Tricky SQL Query

Thank you in advance for any help!

I have two tables, TblPNBooking and TblShipping . They are in a one to many relationship on a field uniquePNID. TblPNBooking uniquePNID can have many shipments. When the query is run I need the results to show all shipments for each TblPNBooking.uniquePNID looping as follows. So, for example, if the query results in TblPNBooking.uniquePNID 1 and 2 and TblPNBooking.uniquePNID 1 has 2 shipments and TblPNBooking.uniquePNID 2 has 3 shipments:

TblPNBooking.uniquePNID 1

TblShipping.uniquePNID 1
TblShipping.uniquePNID 1

TblPNBooking.uniquePNID 2

TblShipping.uniquePNID 2
TblShipping.uniquePNID 2
TblShipping.uniquePNID 2

I am working with MS Access and ASP

If I understand you right, should look something like this:


DB

Thanks!! We are getting there, but instead of counting the shipments, I would like each shipment to be written out. Any ideas?

Hi

There is no way to avoid returning related data you will return duplicated id's for TblPNBooking.uniquePNID.
The only way to get around this would be programmatically by populating an array from "select uniquePNIDn from TblPNBooking" and then creating a loop where each id in turn returns all the related data.

DB

Understood. Thank you.