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:
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:
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)
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)