LIMIT record

faca

New member
Local time
Today, 14:24
Joined
May 20, 2018
Messages
7
Hello!


I have 2 tables called Parts and DeliveryDocuments connected via third table DeliveryDocumentsParts.


One part can have more DeliveryDocuments and one DeliveryDocument can have more parts


I have a form where i have listed all parts but the problem is that each Part is multiplied with different DeliveryDocument

Example:

Part is:
ATE BRAKE DISCS

DeliveryDocumentNumbers are:
6534
43SA
54FF
00DD

Now i have like this:

ATE BRAKE DISCS - 6534
ATE BRAKE DISCS - 43SA
ATE BRAKE DISCS - 54FF
ATE BRAKE DISCS - 00DD

What i want is the part to be listed with last DeliveryDocument in this case ->



ATE BRAKE DISCS - 00DD


Code:
Code:
SELECT Parts.PartID, Parts.PartNumber, Parts.PartName, DeliveryDocuments.DeliveryNumber

FROM Parts INNER JOIN (DeliveryDocuments INNER JOIN DeliveryDocumentsParts ON DeliveryDocuments.DeliveryDocumentsID = DeliveryDocumentsParts.DeliveryDocumentsID) ON Parts.PartID =  DeliveryDocumentsParts.PartID
How can i fix this?



Thanks
 
first create a query (Total query) that will group the partid and max(deliveryid).
from this query build another query that will show you the result you want.
see example.
 

Attachments

You fix it with a field that determines order. There is no inherent order in a table. The only way to guarantee a first record, a second record, a 47th record and a last record is for you to explicitly tell the computer how to order your data.

Do you have such a field in your tables? Because what you have shown so far you do not.
 
first create a query (Total query) that will group the partid and max(deliveryid).
from this query build another query that will show you the result you want.
see example.


Thanks working fine ! :D
 
you're welcome.
 

Users who are viewing this thread

Back
Top Bottom