1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2010 Optimizing a bloated query

Discussion in 'Business Applications' started by Hello_World, Aug 7, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    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!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  3. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    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
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you show me the desired output?
    I am wondering why it is necessary to run it for each month.
     
  5. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    Monthly Average Mechanical Downtime May June July August September October Y-70 41.80% P-71 16.80%
     
  6. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    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
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  8. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    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 Files:

  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  10. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    Sure I can
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you put it in this one.
     

    Attached Files:

  12. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    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.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You can export to a range or preferably a "Named" range, or you can "Link" the range in Excel to the query in Access.
     
  14. Hello_World

    Hello_World Thread Starter

    Joined:
    Jul 9, 2012
    Messages:
    29
    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.
     
  15. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064219