+ Reply to Thread
Results 1 to 8 of 8

Query run like a report (grouping)?

  1. #1
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    120

    Query run like a report (grouping)?

    I don't think this is possible, but at this point my report has the data formated in the way I need to, I guess, create the report I want.

    The data in the report is grouped by SectionID. In each SectionID, there are numerous documents (docID). Each document has a numeric status code(Code). In my report I have a footer for the SectionID grouping that displays the status code of the last document. If the code is 3, then I have a condition that makes the text red and bold. If it's not code 3, then the font is normal, as I don't really need to look at it.

    Now they are asking for a report of just the code 3 documents, but it's not as simple as that. The reason I did the above grouping and footer flag of the last document is that they only care if the last document of the section has a code 3 status. So basically, I guess I need to simulate what I did in the report in a query, then get a report of the last documents of the sections if the code status is 3... Hope I made that clear enough...

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Query run like a report (grouping)?

    It would be helpful if you could post some samples of the data you have and then what you would like your results to look like. Your explanation while complete is confusing and hard to visualize for me.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    120

    Re: Query run like a report (grouping)?

    Well, I can show you a "mockup" of what the report outputs, but the actual database has some confidetial data. I hope this quick excel representation gives a good idea of what it should look like:
    SectionedGroup.jpg
    The report looks much better that this, but I hope this gives you and idea of what the report output shows.

    Section 1000 has 3 documents, but none are Status Code 3, so the footer would not flag it.
    Section 1010 has 3 documents, and the last one is Status Code 3. This is shown in the footer with the Status Code flagged red.
    Section 1020 has 3 documents, and although the 2nd document is Code 3, the last (latest) document is code 1, so it is not flagged.
    (This is not my system, it is one that they are having me automate, so this is all legacy style document tracking, which as one of the IT guys, I have no idea about)

    So I have this report which they are happy about, but since I did this little bit of magic, they want me to take it one step further. They want another report that will only show the section 1010 situtation in which the last document (only) in the section is Status Code 3. So basically, I need to check the last document in each section and only output the document if it is Status Code 3. I cannot just grab all documents that are Code 3, as if it is not the last document, it will be flagged in error.

    Does this make it easier to understand where I am going with this?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Query run like a report (grouping)?

    What constitutes the last document. What is the criteria that determines that the document is the last one. Date? DocID? etc.

  5. #5
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    120

    Re: Query run like a report (grouping)?

    In each section the DocID with the highest value would be the last. These are already sorted.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Query run like a report (grouping)?

    I think I got this to work with two queries. Assume your data is in Table2. Create an aggregate query (Mine is called Query2) as follows:

    Please Login or Register  to view this content.
    then create another query based upon the first one.

    Please Login or Register  to view this content.
    Use this second query as your recordsource for your report.

    BTW: Sorting has no bearing on how data is pulled from tables. It is representative of only how you show (display) the data in a query or report.

    Alan
    Last edited by alansidman; 05-03-2012 at 04:29 PM. Reason: Sorting comment

  7. #7
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    120

    Re: Query run like a report (grouping)?

    I was getting close. I had the first part done, but I was trying to add more info to show in the first Query. If I added the Key field (autonumber) it would show more of the records.
    In any case, I see where you are going with the second query. I think I am getting close here, but there is something that is throwing a wrench in the works. Some of the documentIDs are similar across the sections (which I didn't notice at first, only looking at a fraction of the data at a time). So in section 1000, there could be a documentID 2033. This number may also appear in sections 2000 and 3150, or even others. In the sample database they gave me to work this out with, the first query (Query2) comes back with 376 records. If I leave out the WHERE (so as not to cull any data) statement at the end, I should have the same number of records, but I have 497. An export to Excel and a Conditional format showing duplicates in the Section field shows that I have a problem.

    First of, Thanks for you help. I am understanding the Query Grouping better, but not to the full extent. I'm still stuck though, wishing for the days when this was all done on paper so they would let me do my real work...
    In any case, can your second SQL statement be modified so that the duplicate DocumentIDs don't cause a problem?

  8. #8
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    120

    Re: Query run like a report (grouping)?

    Do I have egg on my face...
    Seems that the autonumber is unique, which can be used to show which document was created first to last in a section. I instead used this and followed your direction and it is working!

    Thanks again for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1