Distinct join on query (1 Viewer)

[ukaquanaut]

Hi Guys

I would appreciate some help with a query I'm trying to construct.

I have 4 basic tables.
TblIncomingItems which has supplier, description, qty, batch etc
TblSuppliers suppliername, address arrival date PONumber etc

and

TblDeliverNote has Customer, Name, Date and PONumber etc
TblProductList list the CustomerID, stock code, description, qty, batch etc

When I create a query with a join in to display the list if items with batch codes against the supplied items, I get duplicates, for example

There are specific item with the same batch that maybe delivered to us from different suppliers.

The query then adds the additional shipments of the same batch with different suppliers to the result, the customer is not unique now.

How do i make the customer display unique but still get all the supplied items listed with the same batch.

UPDATE: When I create a UNION query to do the same job, I actually get distinct customers and suppliers but I cant get my head round the output format of the query and how I would then link the batches - I'm thinking I create a temp table maybe and requiry.. your advice appreciated


Many Thanks
Jerry

 

[plog]

My guess is your are linking the tables on a non-unique field. Could you provide a screenshot of your design-view query?

 

[ukaquanaut]

Hi

Many Thanks for having a look - here is the screenshot you suggested.

one of the design and the other of the output

The delivery ship has duplicates MT Saga and MV Tombarra are duplicates, It doesnt matter which is displayed because its the batch that relates it to us and the also to the supplier.

many Thanks
Jerry

 

[plog]

Every row of data in the image you provided is unique. I see that MT Saga and MV Tombarra appear multiple times, however the supplier field is different between the rows, thus making them unique data.

What do you want the returned data to look like?

 

[ukaquanaut]

Every row of data in the image you provided is unique. I see that MT Saga and MV Tombarra appear multiple times, however the supplier field is different between the rows, thus making them unique data.

What do you want the returned data to look like?

Hi

Thanks for looking at this, yes your right.

However, my objective is to see that a ship was supplied with specific batch, the supplier who supplied that batch while important is not the key really

If for example I use the query as as a basis to count what products were supplied to each ship, the value would be wrong because it would count it multiple times from each supplier, where in fact the ship was only supplied once in reality.

I hope that makes it a bit clearer. thank you.

Jerry

 

[plog]

I don't think you should use that query as the count basis. I think your using more tables than you need for that. To get the count I'd use only the table(s) necessary and no more. Could you not get the total cout by just using the supplied table?

 

[ukaquanaut]

Hi

Many Thanks for your thoughts on that

Jerry