Duplicate Records in Query

B

Bookmdano

I am trying to create a query that retrieves data from 2 tables but I
continue to get duplicate values.
Table 1 = 2006 data
Table 2 = 2007 data
I want to extract Cash data from 2006 and compare to 2007.
I have tried extracting data using account number and/or account
descriptions, which are identical in both tables but continue to have
problems.
 
K

Klatuu

You really should have only one table with a field to tell you what year
the data is for.
You don't give much info for us to help.
continue to get duplicate values - What values are being duplicated?
continue to have problems. - What problems?
 
B

Bookmdano

The table for 06 and 07 looks similar to this format:
Acct No, Description, 06Restricted, 06Unrestricted, 07Restricted, 07
Unrestricted
001 Cash 40000.00 10000.00 30000.00
5000.00
002 Investments 15000.00 25000.00 10000.00
20000.00

I want to pull out Cash (Restricted) and compare 06 data to 07 data. The
results I get are:
Acct No, Description O6Restricted 07Restricted
001 Cash 40000.00 0.00
001 Cash 0.00 0.00
001 Cash 0.00 30000.00
001 Cash 40000.00 30000.00
 
B

Bookmdano

SELECT DISTINCT [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
FROM (Consolidate06 INNER JOIN [Account Descriptions] ON Consolidate06.AAN =
[Account Descriptions].AAN) INNER JOIN Consolidate07 ON [Account
Descriptions].AAN = Consolidate07.AAN
GROUP BY [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
HAVING ((([Account Descriptions].Category)="ca") AND (([Account
Descriptions].AAN)=1));
 
K

Klatuu

Open your query in design view and change 06Unrestricted and 07Unrestricted
to Sum instead of Group by.
--
Dave Hargis, Microsoft Access MVP


Bookmdano said:
SELECT DISTINCT [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
FROM (Consolidate06 INNER JOIN [Account Descriptions] ON Consolidate06.AAN =
[Account Descriptions].AAN) INNER JOIN Consolidate07 ON [Account
Descriptions].AAN = Consolidate07.AAN
GROUP BY [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
HAVING ((([Account Descriptions].Category)="ca") AND (([Account
Descriptions].AAN)=1));


Klatuu said:
Post the SQL of the query that is returning the results.
Click to expand...
Click to expand...
 
B

Bookmdano

I tried that. It works if I only retrieve info from one table at a time. When
I link to both tables the data is four times the original figure. Go figure!

Klatuu said:
Open your query in design view and change 06Unrestricted and 07Unrestricted
to Sum instead of Group by.
--
Dave Hargis, Microsoft Access MVP


Bookmdano said:
SELECT DISTINCT [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
FROM (Consolidate06 INNER JOIN [Account Descriptions] ON Consolidate06.AAN =
[Account Descriptions].AAN) INNER JOIN Consolidate07 ON [Account
Descriptions].AAN = Consolidate07.AAN
GROUP BY [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
HAVING ((([Account Descriptions].Category)="ca") AND (([Account
Descriptions].AAN)=1));


Klatuu said:
Post the SQL of the query that is returning the results.
--
Dave Hargis, Microsoft Access MVP


:

The table for 06 and 07 looks similar to this format:
Acct No, Description, 06Restricted, 06Unrestricted, 07Restricted, 07
Unrestricted
001 Cash 40000.00 10000.00 30000.00
5000.00
002 Investments 15000.00 25000.00 10000.00
20000.00

I want to pull out Cash (Restricted) and compare 06 data to 07 data. The
results I get are:
Acct No, Description O6Restricted 07Restricted
001 Cash 40000.00 0.00
001 Cash 0.00 0.00
001 Cash 0.00 30000.00
001 Cash 40000.00 30000.00

:

You really should have only one table with a field to tell you what year
the data is for.
You don't give much info for us to help.
continue to get duplicate values - What values are being duplicated?
continue to have problems. - What problems?

--
Dave Hargis, Microsoft Access MVP


:

I am trying to create a query that retrieves data from 2 tables but I
continue to get duplicate values.
Table 1 = 2006 data
Table 2 = 2007 data
I want to extract Cash data from 2006 and compare to 2007.
I have tried extracting data using account number and/or account
descriptions, which are identical in both tables but continue to have
problems.
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

It will have something to do with the way you have the joins between the
tables defined, but without having it in front of me, I would have a hard
time getting it right.

This is one of the reasons you really should have only one table. It makes
this sort of thing harder.
--
Dave Hargis, Microsoft Access MVP


Bookmdano said:
I tried that. It works if I only retrieve info from one table at a time. When
I link to both tables the data is four times the original figure. Go figure!

Klatuu said:
Open your query in design view and change 06Unrestricted and 07Unrestricted
to Sum instead of Group by.
--
Dave Hargis, Microsoft Access MVP


Bookmdano said:
SELECT DISTINCT [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
FROM (Consolidate06 INNER JOIN [Account Descriptions] ON Consolidate06.AAN =
[Account Descriptions].AAN) INNER JOIN Consolidate07 ON [Account
Descriptions].AAN = Consolidate07.AAN
GROUP BY [Account Descriptions].Category, [Account Descriptions].AAN,
[Account Descriptions].Description, Consolidate06.Unrestricted,
Consolidate07.Unrestricted
HAVING ((([Account Descriptions].Category)="ca") AND (([Account
Descriptions].AAN)=1));


:

Post the SQL of the query that is returning the results.
--
Dave Hargis, Microsoft Access MVP


:

The table for 06 and 07 looks similar to this format:
Acct No, Description, 06Restricted, 06Unrestricted, 07Restricted, 07
Unrestricted
001 Cash 40000.00 10000.00 30000.00
5000.00
002 Investments 15000.00 25000.00 10000.00
20000.00

I want to pull out Cash (Restricted) and compare 06 data to 07 data. The
results I get are:
Acct No, Description O6Restricted 07Restricted
001 Cash 40000.00 0.00
001 Cash 0.00 0.00
001 Cash 0.00 30000.00
001 Cash 40000.00 30000.00

:

You really should have only one table with a field to tell you what year
the data is for.
You don't give much info for us to help.
continue to get duplicate values - What values are being duplicated?
continue to have problems. - What problems?

--
Dave Hargis, Microsoft Access MVP


:

I am trying to create a query that retrieves data from 2 tables but I
continue to get duplicate values.
Table 1 = 2006 data
Table 2 = 2007 data
I want to extract Cash data from 2006 and compare to 2007.
I have tried extracting data using account number and/or account
descriptions, which are identical in both tables but continue to have
problems.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying data between duplicate records 2
Duplicate Record in Query then Report 3
duplicates in all fields 1
Duplicate records - eliminate from totals 11
How to extract duplicate records from 4 spreadsheets into a separate spreedsheet? 2
Unique Records 6
Access Compare records in same access table 0
How To Delete Duplicate 1

Top