Multiplying information 7 times

[crystal0streaks]

I have a query taking from a couple tables and queries, everything works fine, but the information is duplicated 7 times...how do i fix this???

-crys

 

[Darrylles]

Hi crystal,

Try adding 'DISTINCT' to your SQL query.

E.g. SELECT DISTINCT xyz FROM tblXYZ

If this doesn't work, then DISTINCTROW.

If this doesn't work, then you need to identify another field that uniquely identifies each record, and add it to the relationship between queries / tables.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[crystal0streaks]

That worked to an extent. now it's repeating 3 times, not 7. and 'DISTINCTROW' did nothing.

-crys

 

[Darrylles]

OK crystal,

Now you need to know why it is repeating.

It is in fact returning the same rows more than once, because you need to identify another field that occurs in both records that enables Access to identify 'unique' matches.

E.g.

2 tables - both identical in structure, having 3 fields each:

Forename, MiddleName and Surname.

Both tables have 2 records:

Fred (Forename)
John (Middle)
Bloggs (Surname)

and

Fred (Forename)
(blank) (Middle)
Bloggs (Surname)

When you link only Forename and Surname in the query builder, Access will return 4 records. If you link the Middle name also - it will return only 2.

Does this help? Look at your data.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[crystal0streaks]

i looked at my data and plugged in some differences, but it did not work.

anyother ideas?

thanks for the suggestions!

-crys

 

[dhookom]

Crys,
Reply back with your table structure and other information including your SQL view. You are telling us you have a problem but aren't providing a clue regarding your data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

and maybe the results that are duplicated would be nice to see as well....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[crystal0streaks]

this is the SQL for the query...

SELECT DISTINCT [DISTRIBUTORS_MAILTO_BILLTO].[BILL_TO], [Customers].[STREET], [Customers].[CITY STATE ZIP], [Customers].[PHONE], [Customers].[FAX], [Customers].[CONTACT], [DISTRIBUTORS_MAILTO_BILLTO].[REP], [DISTRIBUTORS_MAILTO_BILLTO].[INVOICE #], [DISTRIBUTORS_MAILTO_BILLTO].[PO #], [DISTRIBUTORS_MAILTO_BILLTO].[TERMS], Format([Current_Date]+NZ([tblTerms].[DaysToAdd]),"mm/dd/yyyy") AS [Due Date], [DISTRIBUTORS_MAILTO_BILLTO].[SHIP DATE], [DISTRIBUTORS_MAILTO_BILLTO].[SHIP VIA], [DISTRIBUTORS_MAILTO_BILLTO].[ACCOUNT #], [DISTRIBUTORS_MAILTO_BILLTO].[SPECIAL INSTRUCTIONS], [DISTRIBUTORS_MAILTO_BILLTO].[UPS TRACKING#], [DISTRIBUTORS_MAILTO_BILLTO].[UPS TRACKING#2], [DISTRIBUTORS_MAILTO_BILLTO].[SHIPPING], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY1], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION1], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION1B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE1], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY2], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION2], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION2B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE2], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY3], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION3], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION3B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE3], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY4], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION4], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION4B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE4], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY5], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION5], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION5B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE5], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY6], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION6], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION6B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE6], [DISTRIBUTORS_MAILTO_BILLTO].[QUANTITY7], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION7], [DISTRIBUTORS_MAILTO_BILLTO].[DESCRIPTION7B], [DISTRIBUTORS_MAILTO_BILLTO].[UNIT PRICE7], [DISTRIBUTORS_MAILTO_BILLTO].[CM #], [DISTRIBUTORS_MAILTO_BILLTO].[PAID], [DISTRIBUTORS_MAILTO_BILLTO].[MAIL_TO], [CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO].[STREET], [CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO].[CITY STATE ZIP], [CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO].[PHONE], [CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO].[FAX], [CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO].[CONTACT]
FROM tblTerms, (DISTRIBUTORS_MAILTO_BILLTO INNER JOIN Customers ON [DISTRIBUTORS_MAILTO_BILLTO].[BILL_TO]=[Customers].[COMPANY]) INNER JOIN CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO ON [DISTRIBUTORS_MAILTO_BILLTO].[MAIL_TO]=[CUSTOMERS_DISTRIBUTOR_MAILTO_BILLTO].[COMPANY];

the information that is repeating:

BRYANT INTERGRATED TECHNOLOGIES
and
CROW SECURITY

with all of their information.

hope this helps

 

[lespaul]

When you look at the query results for these Customers, are the results in EVERY field duplicated? Bill_To is the same, Street is the same, City State Zip, ...Unit Price1, UnitPrice2...EVERY SINGLE FIELD'S RESULT is EXACTLY the SAME! If there is a single difference, that's why the results are duplicated.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[crystal0streaks]

everything is EXACTLY the same. checked it numerous times looking for differences.

there should only be one line of each company.

-crys

 

[dhookom]

It doesn't appear there are any joins with tblTerms. Does this table have more than a single record in it?

Which of the tables use COMPANY as a primary key?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crystal0streaks]

tblTerms is strictly for the date manipulation. no links.

"COMPANY" is the primary key in both "CUSTOMERS" and "CUSTOMERS_DISTRIBUTORS_MAILTO_BILLTO"

i had to not link them so i could have two companies on one line with the addresses (example, i'm shipping out a package to one company, but another paid for it, and i need both company names and addresses).

-crys

 

[dhookom]

You didn't answer my question if tblTerms has more than one record?

Are BRYANT INTERGRATED TECHNOLOGIES and CROW SECURITY repeated multiple times in the BILL_TO field of DISTRIBUTORS_MAILTO_BILLTO?

Are you aware that having multiple copies of similar fields is un-normalized?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crystal0streaks]

tblTerms has more than one record, it has 7.

no, BRYANT INTERGRATED TECHNOLOGIES and CROW SECURITY are not repeated multiple times in the BILL_TO field of DISTRIBUTORS_MAILTO_BILLTO. this is why there should only be one of each company listed in teh query. this is the problem.


-crys

 

[dhookom]

Ok so your thread subject is "Multiplying information 7 times" and you replied "tblTerms has more than one record, it has 7". Do you see the most likely issue?

I would expect to see some kind of join with tblTerms which I don't and you didn't seem to think was important. If there is no join, I would expect a criteria in your WHERE clause that limits tblTerms to a single record.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[crystal0streaks]

i added a join between tblTerms and DISTRIBUTOR_MAILTO_BILLTO and the multiplication is gone. thank you so much!

-crys

 

[Darrylles]

Hi,

Erm, I'm thinking that my second thread id'd this, however, I didn't employ step-wise-refinement - how silly of me.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.