Full Version: Eliminating duplicates from query
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
iprint
I have a query that is pulling duplicate data due to the way it's linked to other queries. I have a field that I can use to eliminate the duplicates.
Ocan't do this in the table due to the table is created fresh every time the query is run. I'm thinking it mus be done in the query.
How could I do this?
theDBguy
Are you saying you don't want to actually delete the records from the table but would like no duplicate results in your query? If so, try adding the DISTINCT keyword in the SQL statement of your query. HTH.
iprint
No the data is not being deleted from a table. It's being generated by a query and comes up as duplicates due to the way the query is structured. It pulls data from several tables.
Could I have an example of what the DISTINCT code would look like?
I'm not real code savvy.
Thanks
theDBguy
Go to Design View of your query and then click on SQL View. Insert "DISTINCT" after the first SELECT word:
SELECT DISTINCT ... "
HTH.
iprint
This didn't seem to work.
I have attached a sample of the queries and tables I am working with.
If you run the query "QryAllTextLines" you will notice that I am getting duplicates for some of the records. The way the text files and queries are structured it causes some of the records to duplicate.
I can't just tall it to use the OrderNum or LineNum to sort through records. The order number needs to be duplicated if it has several line numbers attached to it.
I created a field at the end of the query that combines these 2 numbers and called it comp. This can be used if necessary to eliminate duplicates.
The object is to create a text file that can be used in another program to generate pdf's for a customer. I have all that working but I need to get rid of the duplicates.
Thanks for your help.
theDBguy
Hey, I remember this db. Didn't I already work on this before?
nyway, what is the significance of the line number and font colors? If you take them out (together with your combined field), you will get a unique set of records for your report. Will that not work for you?
iprint
Yeah I've been redoing this DB since time began.
The line numbers and colors are significant for the report that they generate once I get them in to Fusion Pro. The line numbers are a way for WFc to order several items using one order number. They use the line numbers to separate the different items that are ordered. If I order 100 Milk Chocolate with Almond Bars and 100 Solid Milk Bars That would be line 000010 and Line 000020.
I am attaching a small sample of what I am generating with Fusion Pro using the data from the text file.
Well I guess not. Apparently 1 meg is too much to send. I keep getting we cannot proceed errors.
theDBguy
I don't know what to tell you then. Although the order numbers are the same, the line numbers make them different, and although the order numbers and line numbers are the same, the font and colors make them different. So, in other words, with the way you are collecting the data, you really do not have duplicate records. Each record in your query is already unique.
Why do you have three different lines for text (text line 1, text line 2, and text line 3)? And also, sometimes you have two Text Line 1, but have different time stamps. I guess I'm not too clear on the overall db structure to help you out better.
The only other suggestion I could make for you is maybe not to combine the three text lines in one row, but somehow, maybe through code, still print it out on the report the way you want them to appear.
HTH.
iprint
Sorry it took so long for me to reply. Got buried yesterday afternoon. Printing...Got to love it.
I am attaching the file that I generate using the text file from Access. It is a PDF file that I use Fusion Pro to generate using a template. This is transfered to the customer via FTP so they can view the order and sample image. Keep in mind that this is variable data so the file could be up to 150 separate orders which would mean 300 pages.
This db has evolved from 1997. 2 of us created it back then at another company. Now it's completely different and keeps evolving. The file that I'm generating in Fusion is a clone of the order that is generated using the db.
All the data needs to be present in order to populate the fields on the order.
theDBguy
Thanks for the attachment, that helps a bit. After seeing it though, I would stay with my last comment. It looks like you print the text lines (or Personlization lines marked Pos in the attachment) as it appears in a normal query. So, maybe you can eliminate the step of combining the three text lines into one record, and thereby eliminating the duplicate records in your query.
I'm not sure how that would affect your process of converting the query into Fusion Pro, but if you can adapt Fusion Pro to the new query structure, then I think, that would solve your problem. Good luck.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.