Query and Duplicate Rows

G

Guest

Hello-
I created a simple query that for some reason is automatically hiding all
duplicate rows from the main table it is pulling data from [DTBsFY07]. My
objective for the query is just to add descriptions to that main table and I
do not want it to hide any duplicate rows. What do I need to do to set it to
include the duplicate values?
The SQL code is as follows, and, in case it helps, I did not set a primary
key in any of the tables:

SELECT DTBsFY07.PERIOD, DTBsFY07.[ACCT-UNIT],
[ACCT-UNIT-DESCR-TABLE].[AU-DESCR], DTBsFY07.ACCOUNT, DTBsFY07.SYSTEM,
DTBsFY07.ENTRY, DTBsFY07.DESCRIPTION, DTBsFY07.ACTUALS
FROM [ACCT-UNIT-DESCR-TABLE] RIGHT JOIN DTBsFY07 ON
[ACCT-UNIT-DESCR-TABLE].[ACCT-UNIT] = DTBsFY07.[ACCT-UNIT]
GROUP BY DTBsFY07.PERIOD, DTBsFY07.[ACCT-UNIT],
[ACCT-UNIT-DESCR-TABLE].[AU-DESCR], DTBsFY07.ACCOUNT, DTBsFY07.SYSTEM,
DTBsFY07.ENTRY, DTBsFY07.DESCRIPTION, DTBsFY07.ACTUALS;


Thank you.
Filo
 
G

Guest

You have created a Totals query that does a Group By off all the return
fields. This will eliminate duplicates. Run this instead to see all returned
records:

SELECT DTBsFY07.PERIOD,
DTBsFY07.[ACCT-UNIT],
[ACCT-UNIT-DESCR-TABLE].[AU-DESCR],
DTBsFY07.ACCOUNT,
DTBsFY07.SYSTEM,
DTBsFY07.ENTRY,
DTBsFY07.DESCRIPTION,
DTBsFY07.ACTUALS
FROM [ACCT-UNIT-DESCR-TABLE] RIGHT JOIN DTBsFY07 ON
[ACCT-UNIT-DESCR-TABLE].[ACCT-UNIT] = DTBsFY07.[ACCT-UNIT]
 
G

Guest

It worked. Thank you!

Jerry Whittle said:
You have created a Totals query that does a Group By off all the return
fields. This will eliminate duplicates. Run this instead to see all returned
records:

SELECT DTBsFY07.PERIOD,
DTBsFY07.[ACCT-UNIT],
[ACCT-UNIT-DESCR-TABLE].[AU-DESCR],
DTBsFY07.ACCOUNT,
DTBsFY07.SYSTEM,
DTBsFY07.ENTRY,
DTBsFY07.DESCRIPTION,
DTBsFY07.ACTUALS
FROM [ACCT-UNIT-DESCR-TABLE] RIGHT JOIN DTBsFY07 ON
[ACCT-UNIT-DESCR-TABLE].[ACCT-UNIT] = DTBsFY07.[ACCT-UNIT]

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Filo said:
Hello-
I created a simple query that for some reason is automatically hiding all
duplicate rows from the main table it is pulling data from [DTBsFY07]. My
objective for the query is just to add descriptions to that main table and I
do not want it to hide any duplicate rows. What do I need to do to set it to
include the duplicate values?
The SQL code is as follows, and, in case it helps, I did not set a primary
key in any of the tables:

SELECT DTBsFY07.PERIOD, DTBsFY07.[ACCT-UNIT],
[ACCT-UNIT-DESCR-TABLE].[AU-DESCR], DTBsFY07.ACCOUNT, DTBsFY07.SYSTEM,
DTBsFY07.ENTRY, DTBsFY07.DESCRIPTION, DTBsFY07.ACTUALS
FROM [ACCT-UNIT-DESCR-TABLE] RIGHT JOIN DTBsFY07 ON
[ACCT-UNIT-DESCR-TABLE].[ACCT-UNIT] = DTBsFY07.[ACCT-UNIT]
GROUP BY DTBsFY07.PERIOD, DTBsFY07.[ACCT-UNIT],
[ACCT-UNIT-DESCR-TABLE].[AU-DESCR], DTBsFY07.ACCOUNT, DTBsFY07.SYSTEM,
DTBsFY07.ENTRY, DTBsFY07.DESCRIPTION, DTBsFY07.ACTUALS;

Thank you.
Filo
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

unwanted duplicate loan account numbers 1
duplicate records 3
Query fails to eliminate duplicate information 4
Moving duplicate records to a separate table. 2
query from two different tables with no common themes 6
Query to merge data - HELP! 1
Access Sum in Query 11
delete query 1

Top