How to Hide Duplicates in a Query for Just One Field (1 Viewer)

[HansSteyn]

Hi All

Many offers can be made to sell a specific vehicle (which has only one stock number - let's say filed INVSTOCKNR is 9750) but at the end of the day that stock number can only be sold once. I have to make a monthly Police Acquisition Register report. So if there were 3 offers made on stock number 9750 I need to hide 2 since this stock number was only purchased from one seller/supplier. I need to do this in a query which have multiple fields including field INVSTOCKNR where the duplication takes place. I have, so far, not used SQL queries. Any suggestions out there? Thanks

 

[arnelgp]

how do you determine when it is sold? do you have a selling date?
you try to google Total query:

select invstocknr, First(anotherField), First(yetanotherfield) from yourTable group by invstocknr;

if you have sellingDate, you can use it:

select invstocknr, anotherfield, yetanotherfield from yourTable where Not sellingDate Is Null;

 

[HansSteyn]

There is a status field called InvStatus and in the query the criteria for this field is <>"FINAL INVOICE". I know what you mean by the sellingDate but that is unfortunately not available since the same field is used for the offers, so I need to hide the 2 offers.

 

[arnelgp]

"FINAL INVOICE" means this is the one record you want to retrieve?

select * from yourTable where InvStatus = "FINAL INVOICE"

 

[HansSteyn]

<>"FINAL INVOICE" means not Sold

 

[arnelgp]

the sample query i showed will output only the sold one (1 record per invstocknr).

 

[The_Doc_Man]

Your problem is that you are retrofitting functions into a DB lacking the fields necessary to support your requirements.

If you don't have a field that says "This offer resulted in a sale" (i.e. a Y/N checkbox would suffice) then it sounds like you don't have what you need to make the report you want.

 

[arnelgp]

DB lacking the fields necessary to support your requirements.

but at the end of the day that stock number can only be sold once.

<>"FINAL INVOICE" means not Sold

so when it is sold InvStatus = "FINAL INVOICE".