How do I stop records with same field number showing?

[Blaikie]

Hello everyone!

I'm using Access 2000 and working with an old database but need some help. I have a query displaying information from the database each week which has several columns, date, invoice number, name, address etc. However I only want to show one record from each invoice number meaning that the invoice numbers will be sequential and not have duplicates popping up. How would I go about doing this? Sorry I have no clue when it comes to this stuff.


Thanks
Robert

[jimatqsi]

A lot depends on the table designs in your DB, which we can't know.

Maybe you want to query only the invoice heading table and maybe you are currently querying the heading joined to the line item details? In that case, just cruise the heading table and report what's there.

But if your DB has heading and detail info in one table you don't have that choice. Or maybe you want something that is stored in the detail table and is the same on every line item. In that case you want to add grouping to the query. If you group by Invoice number (and all the other fields that repeat on multiple line items), you'll get only one row returned for each invoice.

Jim

[NeoPa]

As Jim says, you leave us with little pertinent information.

Generally then, there are two options that can restrict your output to a single line per invoice :

1. The DISTINCT predicate of the SELECT clause.
2. The GROUP BY clause.

With a better understanding of your position we may be able to assist further.

Welcome to Bytes!

[Blaikie]

Okay here is an example of my query

Date Invoice No Name ...........
21/06/10 978207 GARROWHILL BOWLING CLUB
21/06/10 978208 ALLFIT JOINERS
22/06/10 978213 CBG/CRL
22/06/10 978213 CBG/CRL

In the date field I have "21/06/2010" And "25/06/2010" to show all records from last week and notice the last two lines are duplicates which are completely different records but have the same customer under the same invoice number. I just want to show one line with invoice 978213 and hide the rest. Thanks for you help.

[NeoPa]

In this particular case you could use the DISTINCT predicate of the SELECT clause :

SELECT DISTINCT
       [Date]
      ,[Invoice No]
      ,[Name]
FROM   [YourTable]