query produces duplicates for every record

  • Thread starter Thread starter kath
  • Start date Start date
K

kath

I have two separate groups of tables and queries from one
source.

I am using the retrieval to my Word Merge files since
there are quite a bit of "IF...THEN...ELSE" in my merge
form letters, and not all data fields are available from
the datasource so some inputting is required.

I did the first query successfuly exampled as follows:
1. Built a query to retrieved Info: Last_Name,
First_Name,MI, Addr...
2. I built a table to incorporate the ad hoc data fields
such as Missing1, Missing2, ...
3. Then I built a query to combine all the items in the
query for retrieve data from the source and the empty
table with all the new field names to a final query data.

It worked like magic and my co-workers loved it since they
now don't have to input the names and addresses over and
over when they need to send something to a particular
group from the datasource.

My second attemp are the same except that I also needed
people's ID (NO Duplicates) in the final query. I don't
know what was wrong since the step one query pulled up
names perfectly normal (one record per person), somehow in
the combination process it had to make rows for the same
record/person.

Please help us poor laborers!

Kath
 
Your comment about duplicate records suggests that your query design is not
correct. Likely, you are joining two tables by a single link, but perhaps
you should be joining them by two links?

Post the SQL of the query (open the query in design view, then click on icon
at top left and select SQL, copy the text and paste the text into a
message).
 
Thanks, Ken. Here it is.
SELECT DISTINCT qryCreditAward_Admit_Paid.LAST_NAME,
qryCreditAward_Admit_Paid.FIRST_NAME,
qryCreditAward_Admit_Paid.MIDDLE_INITIAL,
qryCreditAward_Admit_Paid.NAME_SUFFIX,
CreditAward_DataSource.Graduate,
CreditAward_DataSource.ST1,
CreditAward_DataSource.SCHOOL1,
CreditAward_DataSource.BD1, CreditAward_DataSource.ED1,
CreditAward_DataSource.CR1, CreditAward_DataSource.ST2,
CreditAward_DataSource.SCHOOL2,
CreditAward_DataSource.BD2, CreditAward_DataSource.ED2,
CreditAward_DataSource.CR2, CreditAward_DataSource.ST3,
CreditAward_DataSource.SCHOOL3,
CreditAward_DataSource.BD3, CreditAward_DataSource.ED3,
CreditAward_DataSource.CR3, CreditAward_DataSource.ST4,
CreditAward_DataSource.SCHOOL4,
CreditAward_DataSource.BD4, CreditAward_DataSource.ED4,
CreditAward_DataSource.CR4, CreditAward_DataSource.HSTF,
CreditAward_DataSource.College,
CreditAward_DataSource.DeGr, CreditAward_DataSource.NOTES,
CreditAward_DataSource.TTL, CreditAward_DataSource.TESC,
CreditAward_DataSource.TOTALCREDIT,
CreditAward_DataSource.WIP,
qryCreditAward_Admit_Paid.STREET1_LINE1,
qryCreditAward_Admit_Paid.STREET1_LINE2,
qryCreditAward_Admit_Paid.STREET1_LINE3,
qryCreditAward_Admit_Paid.CITY1,
qryCreditAward_Admit_Paid.STATE1,
qryCreditAward_Admit_Paid.ZIP1,
qryCreditAward_Admit_Paid.NATN_DESC1,
qryCreditAward_Admit_Paid.SBGI_DESC_PRIOR_COLLEGE1,
qryCreditAward_Admit_Paid.SBGI_DESC_PRIOR_COLLEGE2,
qryCreditAward_Admit_Paid.SBGI_DESC_PRIOR_COLLEGE3,
qryCreditAward_Admit_Paid.TERM_DESC,
qryCreditAward_Admit_Paid.ID
FROM CreditAward_DataSource, qryCreditAward_Admit_Paid
ORDER BY qryCreditAward_Admit_Paid.LAST_NAME,
qryCreditAward_Admit_Paid.FIRST_NAME,
qryCreditAward_Admit_Paid.MIDDLE_INITIAL,
qryCreditAward_Admit_Paid.NAME_SUFFIX,
CreditAward_DataSource.BD1, CreditAward_DataSource.ED1,
CreditAward_DataSource.BD2, CreditAward_DataSource.ED2,
CreditAward_DataSource.BD3, CreditAward_DataSource.ED3,
CreditAward_DataSource.BD4, CreditAward_DataSource.ED4;
 
You have no join between the two "tables": qryCreditAward_Admit_Paid (which
I assume is a query but it's used as a "table" in the query) and
CreditAward_DataSource. See this line in your SQL statement:

FROM CreditAward_DataSource, qryCreditAward_Admit_Paid

As such, your query is returning a cartesian result, meaning that you are
getting records that result from each record in qryCreditAward being paired
with each record in CreditAward_DataSource-- thus, at a minimum, what may
appear to be duplicate records. (In reality, there not duplicate records per
se, as each record provided by the query is unique with respect to all the
fields in the record, but you'll see multiple "records" from any one of the
tables.)

I don't know what the structure of the qryCreditAward_Admit_Paid query is,
so I can't offer a specific change. However, I'm guessing that there is a
field in the query that should be matched up to a field in the table, such
that your SQL should be changed to something like this:

FROM qryCreditAward_Admit_Paid INNER JOIN CreditAward_DataSource
ON qryCreditAward_Admit_Paid.SomeFieldName =
CreditAward_DataSource.SomeOtherFieldName

Without knowing more about the qryCreditAward_Admit_Paid query's design, I
can't suggest anything more right now.
 
Hey Ken:
That was pretty good - you are helping me to see what SQL
does what.
I solved the problem - In the (supposedly empty)
CreditAward_DataSource table, I had five empty rows and
somehow one of them has a word in a field so, like you
said, for every record the qyr form has, there is another
one with that word in one of the fields! I deleted that
word and now I get the clean result, even without the link!
Thank you so very much again for trying to work with me
and helping me to understand more about Access. I am
learning.

Kath
-----Original Message-----
You have no join between the two "tables":
Click to expand...
qryCreditAward_Admit_Paid (which
I assume is a query but it's used as a "table" in the query) and
CreditAward_DataSource. See this line in your SQL statement:

FROM CreditAward_DataSource, qryCreditAward_Admit_Paid

As such, your query is returning a cartesian result, meaning that you are
getting records that result from each record in qryCreditAward being paired
with each record in CreditAward_DataSource-- thus, at a minimum, what may
appear to be duplicate records. (In reality, there not duplicate records per
se, as each record provided by the query is unique with respect to all the
fields in the record, but you'll see multiple "records" from any one of the
tables.)

I don't know what the structure of the
Click to expand...
qryCreditAward_Admit_Paid query is,
 
Hey Ken:
That was pretty good - you are helping me to see what SQL
does what.
I solved the problem - In the (supposedly empty)
CreditAward_DataSource table, I had five empty rows and
somehow one of them has a word in a field so, like you
said, for every record the qyr form has, there is another
one with that word in one of the fields! I deleted that
word and now I get the clean result, even without the link!
Thank you so very much again for trying to work with me
and helping me to understand more about Access. I am
learning.

Kath
-----Original Message-----
You have no join between the two "tables":
Click to expand...
qryCreditAward_Admit_Paid (which
I assume is a query but it's used as a "table" in the query) and
CreditAward_DataSource. See this line in your SQL statement:

FROM CreditAward_DataSource, qryCreditAward_Admit_Paid

As such, your query is returning a cartesian result, meaning that you are
getting records that result from each record in qryCreditAward being paired
with each record in CreditAward_DataSource-- thus, at a minimum, what may
appear to be duplicate records. (In reality, there not duplicate records per
se, as each record provided by the query is unique with respect to all the
fields in the record, but you'll see multiple "records" from any one of the
tables.)

I don't know what the structure of the
Click to expand...
qryCreditAward_Admit_Paid query is,
 
Back
Top