|  | Duplicate records or values in my query | | |
| | | JeffH |  |
|  Posted: Tue Sep 02, 2008 7:32 pm Post subject: Duplicate records or values in my query |  |
I have a query where by one table is joined (an outer join) to another table.
The 1st table is a claims table which includes an employee ID. The send
table is a table of the employees. I linked them by employee ID but I get
multiple records for each claim. I have adjused the properties of the query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it. |
| |
| | | Jeff Boyce | |
| | Posted: Tue Sep 02, 2008 8:53 pm Post subject: Re: Duplicate records or values in my query | |
Just a wild guess, but is one of those tables in a "many" relationship to
the other? If so, and if you are displaying fields from both tables, each
record on the "one" side could have many "matching" records on the "many"
side.
Is that what's happening?
Regards
Jeff Boyce
Microsoft Office/Access MVP
"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:27BB89B4-3F1D-44D8-B3D2-6F1C963295C5@microsoft.com...
| Quote: | I have a query where by one table is joined (an outer join) to another
table.
The 1st table is a claims table which includes an employee ID. The send
table is a table of the employees. I linked them by employee ID but I get
multiple records for each claim. I have adjused the properties of the
query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it. |
|
| |
| | | John W. Vinson | |
| | Posted: Tue Sep 02, 2008 9:04 pm Post subject: Re: Duplicate records or values in my query | |
On Tue, 2 Sep 2008 14:32:04 -0700, JeffH <JeffH@discussions.microsoft.com>
wrote:
| Quote: | I have a query where by one table is joined (an outer join) to another table.
The 1st table is a claims table which includes an employee ID. The send
table is a table of the employees. I linked them by employee ID but I get
multiple records for each claim. I have adjused the properties of the query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it.
|
Correct the error in your query. If you would like help doing so please open
it in SQL view and post the SQL here... we can't fix problems we can't see!
--
John W. Vinson [MVP] |
| |
| | | JeffH | |
| | Posted: Wed Sep 03, 2008 3:33 pm Post subject: Re: Duplicate records or values in my query | |
| |  | |
This is a different query but I'm getting the same results. It duplicates my
totals. I"m trying to count the number of subscribers in any group and the
total number of members. Total number of members = No. of Subribers + No of
Spouses covered+ No. of Dependents.
The number of subscibers adn Members I get is exactly double of the correct
amount.
SELECT dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName,
Count([dbo_tblCensus]![Name]) AS [Subscriber Count],
Count([dbo_tblCensus]![Name])+Count([dbo_tblCensus]![SpouseDOB])+Sum([dbo_tblCensus]![Dependents]) AS [Member Count]
FROM dbo_tblCensus INNER JOIN [tblCurrent Month Renewals] ON
dbo_tblCensus.EventID = [tblCurrent Month Renewals].Event
GROUP BY dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName;
"John W. Vinson" wrote:
| Quote: | On Tue, 2 Sep 2008 14:32:04 -0700, JeffH <JeffH@discussions.microsoft.com
wrote:
I have a query where by one table is joined (an outer join) to another table.
The 1st table is a claims table which includes an employee ID. The send
table is a table of the employees. I linked them by employee ID but I get
multiple records for each claim. I have adjused the properties of the query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it.
Correct the error in your query. If you would like help doing so please open
it in SQL view and post the SQL here... we can't fix problems we can't see!
--
John W. Vinson [MVP]
|
|
| |
| | | JeffH | |
| | Posted: Wed Sep 03, 2008 3:44 pm Post subject: Re: Duplicate records or values in my query | |
| | | |
Hi, you can disregard my question. I figured out my problem. Thanks for all
your help.
"JeffH" wrote:
| Quote: | This is a different query but I'm getting the same results. It duplicates my
totals. I"m trying to count the number of subscribers in any group and the
total number of members. Total number of members = No. of Subribers + No of
Spouses covered+ No. of Dependents.
The number of subscibers adn Members I get is exactly double of the correct
amount.
SELECT dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName,
Count([dbo_tblCensus]![Name]) AS [Subscriber Count],
Count([dbo_tblCensus]![Name])+Count([dbo_tblCensus]![SpouseDOB])+Sum([dbo_tblCensus]![Dependents]) AS [Member Count]
FROM dbo_tblCensus INNER JOIN [tblCurrent Month Renewals] ON
dbo_tblCensus.EventID = [tblCurrent Month Renewals].Event
GROUP BY dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName;
"John W. Vinson" wrote:
On Tue, 2 Sep 2008 14:32:04 -0700, JeffH <JeffH@discussions.microsoft.com
wrote:
I have a query where by one table is joined (an outer join) to another table.
The 1st table is a claims table which includes an employee ID. The send
table is a table of the employees. I linked them by employee ID but I get
multiple records for each claim. I have adjused the properties of the query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it.
Correct the error in your query. If you would like help doing so please open
it in SQL view and post the SQL here... we can't fix problems we can't see!
--
John W. Vinson [MVP]
|
|
| |
| | | Jeff Boyce | |
| | Posted: Wed Sep 03, 2008 4:46 pm Post subject: Re: Duplicate records or values in my query | |
| | | |
Jeff
Consider posting your solution. Other folks may be trying to figure out the
same problem sometime...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:E70C77B6-133F-4694-80D2-AB47887AF807@microsoft.com...
| Quote: | Hi, you can disregard my question. I figured out my problem. Thanks for
all
your help.
"JeffH" wrote:
This is a different query but I'm getting the same results. It
duplicates my
totals. I"m trying to count the number of subscribers in any group and
the
total number of members. Total number of members = No. of Subribers + No
of
Spouses covered+ No. of Dependents.
The number of subscibers adn Members I get is exactly double of the
correct
amount.
SELECT dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName,
Count([dbo_tblCensus]![Name]) AS [Subscriber Count],
Count([dbo_tblCensus]![Name])+Count([dbo_tblCensus]![SpouseDOB])+Sum([dbo_tblCensus]![Dependents])
AS [Member Count]
FROM dbo_tblCensus INNER JOIN [tblCurrent Month Renewals] ON
dbo_tblCensus.EventID = [tblCurrent Month Renewals].Event
GROUP BY dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName;
"John W. Vinson" wrote:
On Tue, 2 Sep 2008 14:32:04 -0700, JeffH
JeffH@discussions.microsoft.com
wrote:
I have a query where by one table is joined (an outer join) to another
table.
The 1st table is a claims table which includes an employee ID. The
send
table is a table of the employees. I linked them by employee ID but I
get
multiple records for each claim. I have adjused the properties of the
query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it.
Correct the error in your query. If you would like help doing so please
open
it in SQL view and post the SQL here... we can't fix problems we can't
see!
--
John W. Vinson [MVP]
|
|
| |
| | | JeffH | |
| | Posted: Wed Sep 03, 2008 5:49 pm Post subject: Re: Duplicate records or values in my query | |
| | | |
My problem resulted from the fact that one of the tables I was linking to in
my query had multiple (in this case two) like records and thus the query
produced duplicates of everything. I modified my query to add criteria to
eliminate one of the duplicate records in the linked table and it took care
of my issue.
I did not realize one of the tables I was linking to had more than one
record based upon the linked field and will be something I'm now aware of in
case this happens again.
I hope I explained this suffcieintly. I know I get lots of help by reading
others resolutions so I hope this helps someone.
"Jeff Boyce" wrote:
| Quote: | Jeff
Consider posting your solution. Other folks may be trying to figure out the
same problem sometime...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:E70C77B6-133F-4694-80D2-AB47887AF807@microsoft.com...
Hi, you can disregard my question. I figured out my problem. Thanks for
all
your help.
"JeffH" wrote:
This is a different query but I'm getting the same results. It
duplicates my
totals. I"m trying to count the number of subscribers in any group and
the
total number of members. Total number of members = No. of Subribers + No
of
Spouses covered+ No. of Dependents.
The number of subscibers adn Members I get is exactly double of the
correct
amount.
SELECT dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName,
Count([dbo_tblCensus]![Name]) AS [Subscriber Count],
Count([dbo_tblCensus]![Name])+Count([dbo_tblCensus]![SpouseDOB])+Sum([dbo_tblCensus]![Dependents])
AS [Member Count]
FROM dbo_tblCensus INNER JOIN [tblCurrent Month Renewals] ON
dbo_tblCensus.EventID = [tblCurrent Month Renewals].Event
GROUP BY dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName;
"John W. Vinson" wrote:
On Tue, 2 Sep 2008 14:32:04 -0700, JeffH
JeffH@discussions.microsoft.com
wrote:
I have a query where by one table is joined (an outer join) to another
table.
The 1st table is a claims table which includes an employee ID. The
send
table is a table of the employees. I linked them by employee ID but I
get
multiple records for each claim. I have adjused the properties of the
query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it.
Correct the error in your query. If you would like help doing so please
open
it in SQL view and post the SQL here... we can't fix problems we can't
see!
--
John W. Vinson [MVP]
|
|
| |
| | | Jeff Boyce | |
| | Posted: Wed Sep 03, 2008 6:42 pm Post subject: Re: Duplicate records or values in my query | |
| | | |
Thanks for posting your solution.
Your situation is a specific instance of what happens when you join a "one"
(parent) table to a "many" (child) table ... you get one record for each
combination.
Regards
Jeff Boyce
Microsoft Office/Access MVP\
"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:7AB4CE71-01B9-4FCC-996D-5DAC8DCA40CA@microsoft.com...
| Quote: | My problem resulted from the fact that one of the tables I was linking to
in
my query had multiple (in this case two) like records and thus the query
produced duplicates of everything. I modified my query to add criteria to
eliminate one of the duplicate records in the linked table and it took
care
of my issue.
I did not realize one of the tables I was linking to had more than one
record based upon the linked field and will be something I'm now aware of
in
case this happens again.
I hope I explained this suffcieintly. I know I get lots of help by
reading
others resolutions so I hope this helps someone.
"Jeff Boyce" wrote:
Jeff
Consider posting your solution. Other folks may be trying to figure out
the
same problem sometime...
Regards
Jeff Boyce
Microsoft Office/Access MVP
"JeffH" <JeffH@discussions.microsoft.com> wrote in message
news:E70C77B6-133F-4694-80D2-AB47887AF807@microsoft.com...
Hi, you can disregard my question. I figured out my problem. Thanks
for
all
your help.
"JeffH" wrote:
This is a different query but I'm getting the same results. It
duplicates my
totals. I"m trying to count the number of subscribers in any group
and
the
total number of members. Total number of members = No. of Subribers +
No
of
Spouses covered+ No. of Dependents.
The number of subscibers adn Members I get is exactly double of the
correct
amount.
SELECT dbo_tblCensus.EventID, [tblCurrent Month Renewals].CompanyName,
Count([dbo_tblCensus]![Name]) AS [Subscriber Count],
Count([dbo_tblCensus]![Name])+Count([dbo_tblCensus]![SpouseDOB])+Sum([dbo_tblCensus]![Dependents])
AS [Member Count]
FROM dbo_tblCensus INNER JOIN [tblCurrent Month Renewals] ON
dbo_tblCensus.EventID = [tblCurrent Month Renewals].Event
GROUP BY dbo_tblCensus.EventID, [tblCurrent Month
Renewals].CompanyName;
"John W. Vinson" wrote:
On Tue, 2 Sep 2008 14:32:04 -0700, JeffH
JeffH@discussions.microsoft.com
wrote:
I have a query where by one table is joined (an outer join) to
another
table.
The 1st table is a claims table which includes an employee ID.
The
send
table is a table of the employees. I linked them by employee ID
but I
get
multiple records for each claim. I have adjused the properties of
the
query
to eliminate duplicate values/records but this does not elimate the
duplicates.
what am I doing wrong?
This should be easy but I'm missing it.
Correct the error in your query. If you would like help doing so
please
open
it in SQL view and post the SQL here... we can't fix problems we
can't
see!
--
John W. Vinson [MVP]
|
|
| |
|
|
