Duplicate Records in Query

  • Thread starter Thread starter Bookmdano
  • Start date Start date
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.
 
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?
 
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
 
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));
 
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...
 
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...
 
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...
 
Back
Top