Duplicates in Query

[Guest]

I'm designing a query from two tables (Administrators and Budget) where I
need to
generate a combined list of email addresses. I have several people in my
Administrators table who have more than one record/unique ID because they
belong to multiple departments.

When I run the query, I either get results where each email address from the
Budget table duplicates to match each record from the Administrator's table.
This occurs when I've selected Unique Records "Yes" in the Query Properties.

When I select Unique Values "Yes", then the Administrator's email addresses
multiply to match each record from the Budget table.

I end up with 1000s of records rather than just 80.

So, how do I fix the query to prevent the data from multiplying? Also, how
do I query so that I don't get the same name with different unique IDs (Admin
table) from popping up?

 

[DebbieG]

In your query, are the 2 tables linked by the unique ID?

| I'm designing a query from two tables (Administrators and Budget) where I
| need to
| generate a combined list of email addresses. I have several people in my
| Administrators table who have more than one record/unique ID because they
| belong to multiple departments.
|
| When I run the query, I either get results where each email address from the
| Budget table duplicates to match each record from the Administrator's table.
| This occurs when I've selected Unique Records "Yes" in the Query Properties.
|
| When I select Unique Values "Yes", then the Administrator's email addresses
| multiply to match each record from the Budget table.
|
| I end up with 1000s of records rather than just 80.
|
| So, how do I fix the query to prevent the data from multiplying? Also, how
| do I query so that I don't get the same name with different unique IDs (Admin
| table) from popping up?
| --
| Thanks,
| Mel

 

[Guest]

No. They're both linked to a third table, but not to each other.

 

[DebbieG]

If you use tables in a query they must be linked by some common field or you
will get the results you're getting.


| No. They're both linked to a third table, but not to each other.
|
| "DebbieG" wrote:
|
| > In your query, are the 2 tables linked by the unique ID?
| >
| > | > | I'm designing a query from two tables (Administrators and Budget) where I
| > | need to
| > | generate a combined list of email addresses. I have several people in my
| > | Administrators table who have more than one record/unique ID because they
| > | belong to multiple departments.
| > |
| > | When I run the query, I either get results where each email address from
the
| > | Budget table duplicates to match each record from the Administrator's
table.
| > | This occurs when I've selected Unique Records "Yes" in the Query
Properties.
| > |
| > | When I select Unique Values "Yes", then the Administrator's email
addresses
| > | multiply to match each record from the Budget table.
| > |
| > | I end up with 1000s of records rather than just 80.
| > |
| > | So, how do I fix the query to prevent the data from multiplying? Also,
how
| > | do I query so that I don't get the same name with different unique IDs
(Admin
| > | table) from popping up?
| > | --
| > | Thanks,
| > | Mel
| >
| >
| >

 

[Guest]

The only common field is their Unit ID. I have each table linked to a third
table by that Unit ID field. I don't have them linked directly to each
other, though.

 

[DebbieG]

Mel,

I misread your last post.

Can you post the SQL for the query?

Debbie


| The only common field is their Unit ID. I have each table linked to a third
| table by that Unit ID field. I don't have them linked directly to each
| other, though.
|
| "DebbieG" wrote:
|
| > If you use tables in a query they must be linked by some common field or you
| > will get the results you're getting.
| >
| >
| > | > | No. They're both linked to a third table, but not to each other.
| > |
| > | "DebbieG" wrote:
| > |
| > | > In your query, are the 2 tables linked by the unique ID?
| > | >
| > | > | > | > | I'm designing a query from two tables (Administrators and Budget)
where I
| > | > | need to
| > | > | generate a combined list of email addresses. I have several people in
my
| > | > | Administrators table who have more than one record/unique ID because
they
| > | > | belong to multiple departments.
| > | > |
| > | > | When I run the query, I either get results where each email address
from
| > the
| > | > | Budget table duplicates to match each record from the Administrator's
| > table.
| > | > | This occurs when I've selected Unique Records "Yes" in the Query
| > Properties.
| > | > |
| > | > | When I select Unique Values "Yes", then the Administrator's email
| > addresses
| > | > | multiply to match each record from the Budget table.
| > | > |
| > | > | I end up with 1000s of records rather than just 80.
| > | > |
| > | > | So, how do I fix the query to prevent the data from multiplying?
Also,
| > how
| > | > | do I query so that I don't get the same name with different unique IDs
| > (Admin
| > | > | table) from popping up?
| > | > | --
| > | > | Thanks,
| > | > | Mel
| > | >
| > | >
| > | >
| >
| >
| >

 

[Guest]

Hi Debbie,

Is this it?

SELECT AdminContact.LastName, AdminContact.EmailAddress,
BudgetContact.LastName, BudgetContact.EmailAddress
FROM AdminContact, BudgetContact;

 

[DebbieG]

I thought you said there was a third table in the query. I see only two. But
the two tables are not joined. You need to create a relationship between
AdminContact and BudgetContact. It ought to state something like:

FROM AdminContact INNER JOIN BudgetContact ON AdminContact.UnitID =
BudgetContact.UnitID;

Debbie


| Hi Debbie,
|
| Is this it?
|
| SELECT AdminContact.LastName, AdminContact.EmailAddress,
| BudgetContact.LastName, BudgetContact.EmailAddress
| FROM AdminContact, BudgetContact;
|
| "DebbieG" wrote:
|
| > Mel,
| >
| > I misread your last post.
| >
| > Can you post the SQL for the query?
| >
| > Debbie
| >
| >
| > | > | The only common field is their Unit ID. I have each table linked to a
third
| > | table by that Unit ID field. I don't have them linked directly to each
| > | other, though.
| > |
| > | "DebbieG" wrote:
| > |
| > | > If you use tables in a query they must be linked by some common field or
you
| > | > will get the results you're getting.
| > | >
| > | >
| > | > | > | > | No. They're both linked to a third table, but not to each other.
| > | > |
| > | > | "DebbieG" wrote:
| > | > |
| > | > | > In your query, are the 2 tables linked by the unique ID?
| > | > | >
| > | > | > | > | > | > | I'm designing a query from two tables (Administrators and Budget)
| > where I
| > | > | > | need to
| > | > | > | generate a combined list of email addresses. I have several
people in
| > my
| > | > | > | Administrators table who have more than one record/unique ID
because
| > they
| > | > | > | belong to multiple departments.
| > | > | > |
| > | > | > | When I run the query, I either get results where each email
address
| > from
| > | > the
| > | > | > | Budget table duplicates to match each record from the
Administrator's
| > | > table.
| > | > | > | This occurs when I've selected Unique Records "Yes" in the Query
| > | > Properties.
| > | > | > |
| > | > | > | When I select Unique Values "Yes", then the Administrator's email
| > | > addresses
| > | > | > | multiply to match each record from the Budget table.
| > | > | > |
| > | > | > | I end up with 1000s of records rather than just 80.
| > | > | > |
| > | > | > | So, how do I fix the query to prevent the data from multiplying?
| > Also,
| > | > how
| > | > | > | do I query so that I don't get the same name with different unique
IDs
| > | > (Admin
| > | > | > | table) from popping up?
| > | > | > | --
| > | > | > | Thanks,
| > | > | > | Mel
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >

 

[Guest]

It worked! I joined the tables and created a new query. The relationship
seems to have eliminated the duplicates. Thanks for your help, Debbie!!