Results 1 to 12 of 12
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    best way to create a complex query

    I am trying to get a count of all the tests due in certain years.
    I have 4 tests:
    Service Due
    Lining Due
    Qualification Due
    Valve due

    I have a bunch of cars that need these tests done at different times. I want to be able to see a count of how many cars need each test done in the next 5 years.

    So kind of like this


    Code:
                                2023           2024     2026     2027     2028
    Service Due                   23           15       34        21          12
    Lining Due                     15           34       18        55          17
    Qualification Due               45           74       34        23         33
    Valve due                       34          23        12       23          55
    I have checked out a crosstab query but that does not seem to work for me. I have tried a basic query using dates and or but that doesn't work either. It seems like I am going to have to make a separate query for each test AND date rage and find a way to put them together and that would mean 12 queries.

    I am sure there is a more efficient way someone can think of to do this.
    Thanks

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    A Crosstab query is definitely the way to go here.

    Can you post up some sample data or just a stripped down version of your database with the table(s) required?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I dont have sample data but the problem I have found with the crosstab is that it doesnt seem to have a way to set criteria for the different years.

    What i have found that works is creating a form with a dcount formula for each one but then the formula has to be hard coded (well i can probably do something to find the beginning and ending of this year and then add one year to each one but I will do that later)

    I make a database with some sample data in it to test because I would love to see if I can get a crosstab to work since that would be better.
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    A bit of a bodge but the best I can do.

    See report1
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay your data isn't normalised correctly which is why you are having difficulty.

    See the attached. And look at how the query is set up on the normalised data.
    Attached Files Attached Files
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I do not see any Lining Due, Qualification Due or Valve due in your data.
    Groeten,

    Peter

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by xps35 View Post
    I do not see any Lining Due, Qualification Due or Valve due in your data.
    That's because they are the field names, hence the un-normalised structure. If the OP changes it to the example I posted, he can

    a) Add a dozen other types of service action without changing anything other than data.
    b) Use a cross tab to get his desired results.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    There is an issue with the normalization. These dates are very important and are used on a form and often filtered on the so I have a table that has only one slot for each test so that it can be filtered and to reduce the risk of having duplicate dates entered. When a date gets updated it gets appended to a history table just like your normalized one.

    If i use the normalized table the form filter function wont work the way my users need it to work without rewriting the form and making a subquery and adding that in the record source of the form and then I would have to add some code to get the max of each one and assign each one a test type etc etc. All of which could be done but still creates issues where it could pull the wrong entry or something and it makes it not as clear for the end user to validate. I have done this a couple of times with other data sets and the advantages are not always outweighed by the disadvantages.

    In addition, as usually happens, after creating the form using dlookups, which worked well, the user now states that they want the total number of Qualifications due, but if it needs Qualification AND Valve in the same year don't list it in the Valve count bc it will get tested during Qualification. That would make the queries even more difficult with the normalized table I would think.

    I will have to consider this and see if there is a way around the filtering problem with this normalized table. I think I can create a subquery to pull the max of each of the four fields, then add the query linked in to the data source and that may work for the filtering but I need to think it through and see what other problems it may cause. The end user is the main concern and if I can't make it work the way they currently use it they would rather not have the new report.

  10. #10
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Maybe if you attach a sample file with data filter masks too you can help.

  11. #11
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you post up a mock up of the desired results you think you would struggle with using the normalised structure.
    I'm having difficulty "seeing" the issue from your description.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you really want non-normalized structure, get to know UNION query. There is no designer/wizard for UNION, must type or copy/paste in SQLView. Limit of 50 SELECT lines. First SELECT defines field names and types. Be aware, UNION might perform slowly with very large dataset.

    SELECT ID, CarID, QualificationDate AS ActDate, "Qual" AS Activity FROM SampleData
    UNION SELECT ID, CarID, ServiceDate, "Svs" FROM SampleData
    UNION SELECT ID, CarID, LiningDate, "Lng" FROM SampleData;

    Now build CROSSTAB with the UNION as source. More about CROSSTAB http://allenbrowne.com/ser-67.html

    TRANSFORM Count(DataUNION.CarID) AS CountOfCarID
    SELECT DataUNION.Activity
    FROM DataUNION
    GROUP BY DataUNION.Activity
    PIVOT Year([ActDate]);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-28-2019, 08:07 AM
  2. Query is too complex to create report
    By chriswrcg in forum Reports
    Replies: 10
    Last Post: 11-03-2018, 09:30 AM
  3. Replies: 8
    Last Post: 10-17-2018, 11:52 AM
  4. Query is too Complex
    By ihealy1 in forum Queries
    Replies: 2
    Last Post: 11-18-2011, 04:46 PM
  5. Complex Query
    By amotto11 in forum Queries
    Replies: 5
    Last Post: 09-02-2011, 07:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums