By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

How do I stop records with same field number showing?

P: 6
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.

Jun 30 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,240
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.

Jun 30 '10 #2

Expert Mod 15k+
P: 31,769
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!
Jun 30 '10 #3

P: 6
Okay here is an example of my query

Date Invoice No Name ...........
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.
Jul 1 '10 #4

Expert Mod 15k+
P: 31,769
In this particular case you could use the DISTINCT predicate of the SELECT clause :
Expand|Select|Wrap|Line Numbers
  2.        [Date]
  3.       ,[Invoice No]
  4.       ,[Name]
  5. FROM   [YourTable]
Jul 1 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.