[Hello_World]

I have to have this query produce a monthly average availability for a selection of equipment. The query looks pretty ugly and rather bloated and I'm wondering if there's something I can do to make it better.




Right now I have an Expression column representing each month. In each month, take January for example, I need to check if the maintenance period occurred within the month, started before the month but ended during, started during the month but ended outside, completely encapsulates the month (if Jan 2012, then started maintenance Dec 2011 ended Feb 2012), or not count it because it was fully operational throughout the month.





So as you can see checking these 4 cases and doing calculations afterwards really bloats up one month, let alone 12! So I'm just looking for a suggestion if there's something more I can be doing. I'm not afraid of some RnD if someone has articles they can point me towards. If this approach is not overly bad I don't mind, I would just like to do better if its possible.





Thanks in advanced!

[OBP]

Well a couple of options come to mind, as the work has to be done by the query, but necessarily shown, you could use a second query to display the data you want to see.
Or use a form or Report to show the data.
Are you doing all 4 conditions in one Column using nested IIF() functions?

[Hello_World]

Hey OBP, thanks for the response. The query currently looks like this:

Format(
Sum(
Switch(

DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1) > Date(), NULL,

DownDate BETWEEN DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1) AND DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1) AND
UpDate BETWEEN DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1) AND DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1),
(DateDiff('s',[DownDate],[UpDate]))/(DateDiff('s',DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1), DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1))),

DownDate BETWEEN DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1) AND DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1) AND
(IsNull([UpDate]) Or UpDate > DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1)),
(DateDiff('s',[DownDate],DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1))) /(DateDiff('s',DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1), DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1))),

DownDate < DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1) AND
UpDate BETWEEN DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1) AND DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1) ,
(DateDiff('s',DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1),[UpDate])) /(DateDiff('s',DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1), DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1))),

DownDate < DateSerial(Forms!frmExportReport!txtYearChosen, 1, 1) AND
UpDate > DateSerial(Forms!frmExportReport!txtYearChosen, 2, 1),
1,

True, 0
)
), "Percent"
) As Janurary,

copy pasted about 12 times with the months in the DateSerials changing obviously. I was think about it last night and I think I was getting to somewhere that I see you might be suggesting.

I was wondering if it were possible to either create a temp table with VBA and run the query 12 times but not with parameters for each column. Or is using a report like you suggested the better approach. The end result is that I need this data for about 40 machines exported to excel. The reason for the excel export is it is where the customer has other data the wish to combine into their own reports.

Thanks again, you are always a big help

[OBP]

Can you show me the desired output?
I am wondering why it is necessary to run it for each month.

[Hello_World]

Monthly Average Mechanical Downtime May June July August September October Y-70 41.80% P-71 16.80%

[Hello_World]

Well that didn't come out as expected...
So more formatted like this:

Monthly Average Jan Feb Mar Apr May
Truck1 18% 48% 22%
Truck7 0% 15% 0%

Where that date was say April 2012 so because we have not come to the end of that month its column and all later dates are null

[OBP]

What that looks like is a Crosstab Query.
Have you looked at a crosstab query for the output?
Could you provide me with some dummy data and your current query?

[Hello_World]

Here they are. I hadn't thought about a crosstab query. I was looking into creating a new table that could have the query as a source but I'm not sure if that was a good/bad practice. This is my first database so much has been trial and error, along with many many google searches. I am very grateful for people like you who take the time to educate the newbies like moi!

[OBP]

If I provide a blank database can you put the table and query in it for me to "play" with?
I am not good with looking at SQL, I am better just trying the query in design mode.

[Hello_World]

Sure I can

[OBP]

Can you put it in this one.

[Hello_World]

I have an idea that I'm working with, do you know of any way to Export to a specific range in excel from Access 2010? because then I could cut the query down and just use parameters.

[OBP]

You can export to a range or preferably a "Named" range, or you can "Link" the range in Excel to the query in Access.

[Hello_World]

I ended up modifying some other code I found so that now I export to an excel sheet while looping through a very condensed version of the query I sent you.

Thanks for your help, I may have one last question later while I'm wrapping this database up.