Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2010 Optimizing a bloated query


(!)

Hello_World's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
07-Aug-2012, 04:08 PM #1
Access 2010 Optimizing a bloated query
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,565 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 08:34 AM #2
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?
__________________
OBP
I do not give up easily
Hello_World's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
08-Aug-2012, 10:23 AM #3
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,565 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 10:46 AM #4
Can you show me the desired output?
I am wondering why it is necessary to run it for each month.
Hello_World's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
08-Aug-2012, 11:01 AM #5
Monthly Average Mechanical Downtime May June July August September October Y-70 41.80% P-71 16.80%
Hello_World's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
08-Aug-2012, 11:03 AM #6
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,565 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 11:37 AM #7
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's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
08-Aug-2012, 11:54 AM #8
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!
Attached Thumbnails
Access 2010 Optimizing a bloated query-sampledata.png  
Attached Files
File Type: docx Monthly Average Unavailability Query.docx (17.1 KB, 36 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,565 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 12:56 PM #9
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's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
08-Aug-2012, 01:19 PM #10
Sure I can
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,565 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 02:03 PM #11
Can you put it in this one.
Attached Files
File Type: zip Updates.zip (20.8 KB, 6 views)
Hello_World's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
08-Aug-2012, 02:03 PM #12
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,565 posts.
 
Join Date: Mar 2005
Location: UK
09-Aug-2012, 06:20 AM #13
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's Avatar
Hello_World Hello_World is offline
Member with 29 posts.
THREAD STARTER
 
Join Date: Jul 2012
09-Aug-2012, 10:16 AM #14
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
access2010, query

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑