Solved Consolidate Delivery Information Into 1 Line Query (1 Viewer)

LGDGlen

Member
Local time
Today, 13:57
Joined
Jun 29, 2021
Messages
229
Hi All

I have a situation where I need to consolidate parent/child records into 1 line for a view of the data. The data looks like this:

Code:
Consignment ID    Delivery ID    CMR    SalesOrder
1234                1            Yes    Yes
1234                2            No    Yes
      
2345                3            Yes    No
2345                4            Yes    No
2345                5            No    Yes
      
3456                6            Yes    No
3456                7            Yes    No
3456                8            Yes    Yes
      
4567                9            No    Yes
4567                10           No    Yes
4567                11           Yes    Yes

Where each consignment has an ID and can have 1 or more deliveries. Each delivery has an ID and 2 fields indicating whether we have received/sent documents.

What i need to see is for CMR if any are not Yes then the output will be No and for Sales Orders the same, if any are not Yes then the output will be No, otherwise output Yes

So the results from the above would be:

Code:
Consignment ID    CMR    SalesOrder
1234            No    Yes
2345            No    No
3456            Yes    No
4567            No    Yes

My initial thought was something along the lines of a simple Total Query grouping by Consignment ID and CMR for the first one and Consignment ID and SalesOrder for the second one. But i'm not sure thats correct.

It's probably something really simple and i'd probably figure it out through trial and error but i'm wanting to skip the pain and go straight to the answer so if anyone can give me pointer i'd appreciate it

kind regards

Glen

(apologies for the poor formatting of the example data)
 

plog

Banishment Pending
Local time
Today, 08:57
Joined
May 11, 2011
Messages
11,611
First, you are doing yourself no favors by having spaces in your field names. Second, if CMR can only have 2 values "Yes" and "No", you should use a Yes/No field type.

For what you have posted the below query will give you the CMR value you desire--you can then use that logic to do the same for SalesOrder:

Code:
SELECT [Consignment ID], MIN([CMR]) AS CMR_All 
FROM YourTableName
GROUP BY [Consignment ID]

This method assumes that CMR can only have Yes or No values--not anything else, nor null.
 

LGDGlen

Member
Local time
Today, 13:57
Joined
Jun 29, 2021
Messages
229
@plog thank you for your response.

to answer in turn:

- no fields have spaces in the names this was just in my example above so apologies for that it was to make them more readable/descriptive
- CMR and SalesOrder are yes/no fields indeed
- thank you for the query i'll take a look tomorrow when i'm back in the office and implement it

thank you once again
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:57
Joined
May 7, 2009
Messages
19,169
i created Query1 that will Count the Deliveries, Total the CMR (Yes, only) and Total SalesOrder (Yes, only).
next i created the final query, Query2 from Query1.
 

Attachments

  • testConsignment.accdb
    456 KB · Views: 123

Users who are viewing this thread

Top Bottom