Data is duplicating in Access

[E1202413]

While I have run queries, reports and such I have never before had to create my own. I am a true beginner with this.



I have two tables I need to compare but not sure how to do this. I need to compare the same fields in two tables for differences.

My first table has the fields: Doc No, Doc Type and Cost. The second table has: Doc No, Doc Type and Amount. When I try to join them the Cost field doubles the values. How do I stop this?

I am comparing inventory transactions to the the general ledger and looking to find variances.

Any help would be appreciated!

[June7]

Sounds like a 1-to-many or even many-to-many relationship. Records of first table are joining to multiple records of second table. This causes the first table data to be repeated.

What exactly are you trying to compare? If Amount = Cost?

[E1202413]

Our inventory files and general ledger files do not match. I am looking at the document numbers to see where the amounts do not match so I can review the data.

This is what I am trying to get to:

Doc Type	Doc Number	Cost	Amount	Difference
IT	2082601	(2,290.72)	-	(2,290.72)
IT	2085155	2,001.94	2,001.94	-
IT	2090966	(241.36)	-	(241.36)
IT	2102405	(180.64)	-	(180.64)
IT	2102492	(2,546.20)	-	(2,546.20)
IT	2107479	(4,194.08)	-	(4,194.08)
IT	2215810	(174.34)	-	(174.34)
IT	2358420	(246.95)	-	(246.95)
IT	2358448	(1,143.04)	-	(1,143.04)
IT	2379546	(98.42)	-	(98.42)
IT	2425307	(392.80)	-	(392.80)
IT	2461670	(1,670.78)	-	(1,670.78)
IT	2481733	(570.34)	(570.34)	-
IT	2481820	448.12	448.12	-

[June7]

The tables are supposed to be 1-to-1 relationship on DocNumber?

If yes, then apparently the DocNumber is duplicated in second table. Find the duplicate doc number records and fix. An aggregate query can tell you which doc numbers are duplicated.

SELECT DocNumber, Count(*) AS CountDocNum FROM secondtable GROUP BY DocNumber HAVING Count(*) > 1;

[E1202413]

The document number can be repeated multiple times in both tables. So it would be a many to many relationship I believe?

[June7]

Okay, then do an aggregate query for each table to summarize the Cost and Amount fields grouping on DocNumber. This should result in a single record for each DocNumber. Then do a query that joins those queries.

Will each table have the same doc numbers? If not, then need to join the two aggregate queries to a master dataset of unique doc numbers.

[E1202413]

Each table will have some overlapping doc numbers but some will be different in both. So I need to create a table with all the doc numbers as a master list? Then join the two queries with that?

[June7]

Build master table or use a UNION query as the master.

SELECT DocNumber FROM table1
UN ION SELECT DocNumber FROM table2;