Help required (1 Viewer)

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Relatively new to the forum, but need the benefit of some experience in a problem I cannot resolve.

Simply - I have 3 pre-determined data sources which append to 3 different tables.

Table 1 - Shipment Number

Table 2 - Shipment Number
- Delivery Number

Table 3 -Delivery Number
- Receipt Date Time

Table 1 to Table 2 - joined by Shipment Number
Table 2 to Table 3 - joined by Delivery Number

I want to output the received date/time for each shipment.
Shipment does not exsist in Table 3 and there are multiple deliveries for each shipment.
The received date/time in Table 3 is the same for each delivery on the same shipment.
My query returns duplicate records for the shipment due to having multiple deliveries - how can I filter the results to return just one received date/time for records that have multiple deliveries assigned to a shipment ?

Hope this makes sense - I have tried various options but cannot resolve the issue.

Thanks for your support

Regards
John
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:26
Joined
Sep 21, 2011
Messages
14,038
Group by Shipmemt Number and DateTime ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Hi. Have you tried using the DISTINCT keyword in your SELECT query?
 

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Group by Shipmemt Number and DateTime ?

Thanks for your response -
I did try this previously - the problem being is some Shipments have multiple deliveries and each delivery on the same shipment shares the same delivery date/time.

For example

Table 1 - Shipment 123

Table 2 - Shipment 123 - Delivery A
Shipment 123 -Delivery B
Shipment 123 - Delivery C


Table 3 - Delivery A - Receipt - 1/2/19 20:00
Delivery B - Receipt - 1/2/19 20:00
Delivery C - Receipt - 1/2/19 20:00

Want to return - Shipment 123 (from Table 1) and Receipt -1/2/19 20:00 (Table 3)

Many thanks
 

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Group by Shipmemt Number and DateTime ?

Thanks for your response -
I did try this previously - the problem being is some Shipments have multiple deliveries and each delivery on the same shipment shares the same delivery date/time.

For example

Table 1 - Shipment 123

Table 2 - Shipment 123 - Delivery A
Shipment 123 -Delivery B
Shipment 123 - Delivery C


Table 3 - Delivery A - Receipt - 1/2/19 20:00
Delivery B - Receipt - 1/2/19 20:00
Delivery C - Receipt - 1/2/19 20:00

Want to return - Shipment 123 (from Table 1) and Receipt -1/2/19 20:00 (Table 3)

Many thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Apologies
Could you enlighten me as to how to do this please

Regards

For example,

SELECT DISTINCT ShipmentNumber, DeliveryTime
FROM Shipments
INNER JOIN Deliveries
ON Shipments.ShipmentNumber=Deliveries.ShipmentNumber
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:26
Joined
Sep 21, 2011
Messages
14,038
Well I think the DISTINCT option is better, but the logic remains the same for GROUP BY.
You join by Delivery Number, but do not include in query output, just Shipment and Date.?

Thanks for your response -
I did try this previously - the problem being is some Shipments have multiple deliveries and each delivery on the same shipment shares the same delivery date/time.

For example

Table 1 - Shipment 123

Table 2 - Shipment 123 - Delivery A
Shipment 123 -Delivery B
Shipment 123 - Delivery C


Table 3 - Delivery A - Receipt - 1/2/19 20:00
Delivery B - Receipt - 1/2/19 20:00
Delivery C - Receipt - 1/2/19 20:00

Want to return - Shipment 123 (from Table 1) and Receipt -1/2/19 20:00 (Table 3)

Many thanks
 

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Well I think the DISTINCT option is better, but the logic remains the same for GROUP BY.
You join by Delivery Number, but do not include in query output, just Shipment and Date.?

Using DISTINCT still returns duplicated shipment numbers due to the fact that the Receipt date is associated to the Delivery Number (ie: 1 record per delivery)

Hence the duplication of Shipment Number.

To illustrate

Table 1 - Shipment 123

this has for example 3 matches in Table2

- Shipment 123 - Delivery A
-Shipment 123 - Delivery B
- Shipment 123 - Delivery C

IE: SHIPMENT 123 CONTAINS 3 DELIVERIES, A, B and C

Table 3

Contains Deliveries A, B and C each with identical Receipt Date/Time as they were all on the same Shipment.


All I want to return is

Shipment 123 = Receipt Date/Time as one record (3 are returned ion in the above example one each for delivery A,B and C)


Thanks
G
 

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Using DISTINCT still returns duplicated shipment numbers due to the fact that the Receipt date is associated to the Delivery Number (ie: 1 record per delivery)

Hence the duplication of Shipment Number.

To illustrate

Table 1 - Shipment 123

this has for example 3 matches in Table2

- Shipment 123 - Delivery A
-Shipment 123 - Delivery B
- Shipment 123 - Delivery C

IE: SHIPMENT 123 CONTAINS 3 DELIVERIES, A, B and C

Table 3

Contains Deliveries A, B and C each with identical Receipt Date/Time as they were all on the same Shipment.


All I want to return is

Shipment 123 = Receipt Date/Time as one record (3 are returned ion in the above example one each for delivery A,B and C)


Thanks
G

I have attached an example of tables and query with duplicate shipment numbers I am trying to eliminate.

Regards#
G
 

Attachments

  • Example.accdb
    1.8 MB · Views: 102

Gasman

Enthusiastic Amateur
Local time
Today, 12:26
Joined
Sep 21, 2011
Messages
14,038
I cannot view some of it due my old version. Sorry
However even my version does not appear to have those tables.?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
I have attached an example of tables and query with duplicate shipment numbers I am trying to eliminate.

Regards#
G
Hi. I don't understand, I don't see any duplicates in the query. For example, take a look at this screenshot.




Are you saying the first shipment number is considered as duplicate? If so, which date did you want to see?
 

Attachments

  • dups.PNG
    dups.PNG
    10.7 KB · Views: 159

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Thanks for your reply - what I am trying to eliminate is the duplicate shipment number - you will see the date/times of receipt are different but relate to the same shipment - for the purposes of reporting I just need to ascertain the shipment receipt time which would be the first date/time.

I tried to order by First on this field but didn't work for me.

Regards
G
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Thanks for your reply - what I am trying to eliminate is the duplicate shipment number - you will see the date/times of receipt are different but relate to the same shipment - for the purposes of reporting I just need to ascertain the shipment receipt time which would be the first date/time.

I tried to order by First on this field but didn't work for me.

Regards
G
Okay, that's different than what you asked for initially. In this case, a Totals Query, like Gasman originally suggested, would be a better approach. For example, try the following query:
Code:
SELECT DISTINCT VT11_Master.[Shipment Number], Min(ExportData.asn_hdr__first_rcpt_date_time) AS FirstTime
FROM VT11_Master INNER JOIN (ZIVT61_Master INNER JOIN ExportData ON ZIVT61_Master.Delivery = ExportData.asn_hdr__bol) ON VT11_Master.[Shipment Number] = ZIVT61_Master.Shipment
WHERE (((ExportData.asn_hdr__first_rcpt_date_time) Is Not Null))
GROUP BY VT11_Master.[Shipment Number];
Hope it helps...
 

Garmani

Registered User.
Local time
Today, 05:26
Joined
Sep 23, 2019
Messages
18
Thanks I think I can get what I need by taking this approach, apologies if my explanation confused matters.

Appreciate your help ....I might be back with more questions :)

Regards
GARMANI
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Thanks I think I can get what I need by taking this approach, apologies if my explanation confused matters.

Appreciate your help ....I might be back with more questions :)

Regards
GARMANI
Hi. You're very welcome. Gasman and I were happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom