How to avoid dupliate records???? Help!

[nicky37]

Hi

I am trying to create a query that will show invoice information. It is being drawn from 3 tables - a customer deal table, a sales invoice table and a supplier invoice table. I want to be able to show the invoice information for each deal, I have created a query for this and put into a report.

However one of my deals has 3 sales invoices and 2 supplier invoices and the report is showing the same customer 6 times and duplicating the invoices.

What I would ideally liek to show is the customer name once with the invoices listed underneath or to the side....

Can anyone help!!!!

Thanks

 

[ColinEssex]

In the report design view click on the sorting and grouping button and create a group for the customer. Put the customers name in the customers header, then you'll get the customer name once and the details in the detail part.

Col

 

[nicky37]

Thank you but that still doesn't work, that is the format I want however I am still getting duplicates...

for example, one deal has the 3 sales invoice numbers and 2 supplier invoice numbers and it is duplicating records from both the sales and supplier invoice tables...

So for example:

Jo Bloggs
Ref:
Sales invoice number 1111
Sales invoice number 1112
Sales Invoice Number 1113
Supplier invoice number 33
Supplier invoice number 34

I would like to see it as above, however what I am seeing is each invoice number twice so if I put grouping and sum the totals the total is incorrect as it is showing 1111 twice, 1112 twice and so on.....am at a loss!!

I think it is doing this because there is more than one of both supplier and sales invoices, if there is only one sales and supplier invoice it looks good and is fine......

Any ideas are appreciated....

 

[Pat Hartman]

You have two separate 1-many relationships. Just because tables have a common field doesn't mean that it makes sense to join them. Change your report to a main report for the 1-side data and two reports each showing one of the many-side sets of data.

 

[Dona]

How to avoid duplicate records??????

Try this

In SQL view after select type "DISTINCT"


Good Luck.

 

[Pat Hartman]

That won't help, Dona.

 

[nicky37]

okay Pat, thanks, how would I then join them together to see all the data in one report? I am bit of a novice!! Thanks for all your help!

 

[Pat Hartman]

Change your report to a main report for the 1-side data and two subreports each showing one of the many-side sets of data. Sorry, that was apparently an important word that I left out.

 

[nicky37]

Hi Pat

I am obviously missing something here! (which doesn't surprise me!)

I have tried this however I am still getting duplicates, I am presuming this is something to do with my relationships.

What I have done is include an identifying number in both the deal table and the invoice's table that links them alltogether, however as you have said in a previous post, just becuase they contain common data doesn't mean I should join them.....I can't get my head around how else I can join them as I obvously only want to see invoice information for each particular deal......

Can you tell that I have tought myself access and keep learing as I go!!

Thanks again you are an angel

 

[sportsguy]

Can you paste a picture of the query and table design as below

That would help quickly in being able to provide a detailed answer to a vague question.



sportsguy

 

[Tezdread]

I'm probably missing the point here also but in the first post you said

What I would ideally liek to show is the customer name once with the invoices listed underneath or to the side....

and for this, doesn't going into properties of the customer name and setting hide duplicates to 'yes' work?

As for the relationships issue, if the totals are correct although duplicated can't you do the same thing (hide dup's)?

Guess that if you do have a problem with your relationships you need to sort that anyway...

 

[Pat Hartman]

If you are getting duplicates, your forms are not drawing their data from the correct table/query.

The main report should be based on a query of the customer deal table.
One subreport should be based on a query of the sales invoice table and and the second subreport should be based on a query of the supplier invoice table. Make sure that all the master/child links are properly set so that Access will sync the subreports with the main report.