Dups created in queries

[ekoschik]

I have imported Excel files into tables. I then run a query on the table and export back into Excel. When this happens, the values of qty and dollars and duplicated. More specifically, the prod codes of 113100 have different amounts under the same code but when exported back into Excel, the values are just duplicated rather than the actual numbers coming over. How can I get the right numbers exported? Hope this makes sense. Thanks! Elaine

113100	Sausage	$1.00	7	$7.00
113100	Sausage	$1.00	7	$7.00
113100	Sausage	$2.99	40	$119.60
113100	Sausage	$2.99	40	$119.60

[JoeM]

If you view the query you are exporting, are the value duplicated there?
If so, the problem is your query. You probably have a "one-to-many" relationship going on.
Can you post the SQL code of that query here (switch your query to SQL View and Copy and Paste code here)?

[ekoschik]

If you view the query you are exporting, are the value duplicated there?
If so, the problem is your query. You probably have a "one-to-many" relationship going on.
Can you post the SQL code of that query here (switch your query to SQL View and Copy and Paste code here)?

Here you go - thanks!

SELECT Barnes_Dec_2012.F1, Barnes_Dec_2012.F2, Barnes_Dec_2012.F3, Barnes_Dec_2012.F4, Barnes_Dec_2012.[Total $ Sold], Item_Descr.Categories, Item_Descr.[Entree Type]
FROM Barnes_Dec_2012 INNER JOIN Item_Descr ON Barnes_Dec_2012.F1 = Item_Descr.F1
GROUP BY Barnes_Dec_2012.F1, Barnes_Dec_2012.F2, Barnes_Dec_2012.F3, Barnes_Dec_2012.F4, Barnes_Dec_2012.[Total $ Sold], Item_Descr.Categories, Item_Descr.[Entree Type]
HAVING (((Barnes_Dec_2012.[Total $ Sold])=True));

[JoeM]

Do you see the duplicates when you view the query, or only in the exported file?
There is no way you should have "true" duplicate records, as you are grouping on all the fields you are returning.
You should only see multiple records in your query if at least one of the seven fields is different.

[ekoschik]

Do you see the duplicates when you view the query, or only in the exported file?
There is no way you should have "true" duplicate records, as you are grouping on all the fields you are returning.
You should only see multiple records in your query if at least one of the seven fields is different.

Yes, I see the dups in the query.

[JoeM]

That seems to imply that the records may look the same, but really are not. You may have a "floating decimal" issue going on (i.e. where if you expanded out one of your numeric fields, you would say that maybe it is stored as something like $7.000000000000000000001.

If that is the case, round those values off to two decimals places before joining in the query may solve it.

However, I am kind of curious as to why you have a "one-to-many" relationship in the first place. Which underlying table has the multiple "F1" values (or do both)?
Could you post a small sample of each table? Maybe you need another field in your join.

[ekoschik]

I have to be honest, I don't know why I have one to many. I'm don't understand relationships very well, obviously. Below is the data from excel that imports into a table, there are 5 fields. I'll check on the floating decimal.

1700	Pumpkin Waffle Eggspress	146	9.49	1385.54
1720	Pumpkin Waffle	34	6.49	220.66
1740	Pumpkin Waffle w Bacon	5	8.19	40.95
1760	Pumpkin Waffle w Saus	3	8.19	24.57

[JoeM]

What does your second table look like?

If you are able to upload a copy of your database, I may be able to take a look at it tonight when I am home.

[ekoschik]

I just imported a table again letting Access define its primary key. I also have a primary key for my items/desc. table. I linked those 2 fields in a new query and did not get the duplicates this time. This is a new dbase in a new job and it's a bit complicated getting the data they want as I'm a basic user/designer. I can upload the dbase, how do I do this?

[JoeM]

I can upload the dbase, how do I do this?

See: https://www.accessforums.net/forum-s...tml#post164511

[ekoschik]

I tried to upload the dbase and it failed.

[June7]

Why did upload fail? See instructions at bottom of my post.