Complex query

J

Jeff

Hi

I'm doing a join of many queries into 1 query so that I can use all
this data in one report, and also to sum it up for another.

However, while I just want 1 record per neighborhood, I am getting
duplicate records of a few neighborhoods instead.

Could someone help explain why this is happening?

I've included the SQL to help.

Thanks

Jeff

SELECT [Van Clients query].[Van Neighborhood Served], [Van Clients
query].CountOfDemogID, [Van Clients query].SumFirstTime, [Van Client
Activity Count Query].SumofActiveClients, [Van Immunization
Query].ImmunCount, [Van Immunization Query].NonImmunCount, [Van
Immunization Query].UnknownImmunCount, [Van Delivered
Query].CountOfPregnancyID, [Van Delivered Query].TermNormalBirth, [Van
Delivered Query].PTLTermlBirth, [Van Delivered Query].PTLPTDBirth,
[Van Delivered Query].SumofPTL, [Van Delivered Query].SumofPTD, [Van
Delivered Query].SumofLBW, [Van Delivered Query].SumofVLBW, [Van
Delivered Query].SumofELBW, [Van Deceased Query].SumofMiscarry, [Van
Deceased Query].SumDeceased, [Van Deceased Query].SumLTF, [Van
Delivered Query].[SumOfNumber of Prenatal Visits - Kept], [SumOfNumber
of Prenatal Visits - Kept]/[CountOfPregnancyID] AS [Avg Prenatal
Kept], [Van FYOL Ed Query.CountOfFYOLID] AS CountOfFYOLIDBirth, [Van
FYOL6 Ed Query.CountOfFYOLID] AS CountOfFYOLID6mo, [Van Pregnancy
Query].PregnancyCount, [Van Pregnancy Query].PosCount, [Van Pregnancy
Query].NegCount, [Van Prenatal Ed Query].CountOfVanID, [Van FYOL6 Ed
Query].SSICountFYOL6mo, [Van FYOL Ed Query].SSICountFYOLBirth, [Van
Womenshealth Query].SSICountWH, [Van PN Total Query].PNClients, [Van
FYOL Total Query].FYOLClients, [Van Prenatal Ed Query].SSICountPrenat,
Nz([SSICountFYOL6mo])+Nz([SSICountFYOLBirth])+Nz([SSICountPrenat])
+Nz([SSICountWH]) AS TotalSSICount, [Van VVisit UnionAll Query].
[CountOfEducation Date], [Van VVisit Prenat Query].[CountOfEducation
Date], [Van Clients query].[HMB Report Start Date], [Van Clients
query].[HMB Report Stop Date]
FROM ((((((((((((([Van Clients query] LEFT JOIN [Van Immunization
Query] ON [Van Clients query].[Van Neighborhood Served] = [Van
Immunization Query].[Van Neighborhood Served]) LEFT JOIN [Van
Delivered Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Delivered Query].[Van Neighborhood Served]) LEFT JOIN [Van FYOL
Ed Query] ON [Van Clients query].[Van Neighborhood Served] = [Van FYOL
Ed Query].[Van Neighborhood Served]) LEFT JOIN [Van FYOL6 Ed Query] ON
[Van Clients query].[Van Neighborhood Served] = [Van FYOL6 Ed Query].
[Van Neighborhood Served]) LEFT JOIN [Van Immunization Query] AS [Van
Immunization Query_1] ON [Van Clients query].[Van Neighborhood Served]
= [Van Immunization Query_1].[Van Neighborhood Served]) LEFT JOIN [Van
Pregnancy Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Pregnancy Query].[Van Neighborhood Served]) LEFT JOIN [Van
Prenatal Ed Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Prenatal Ed Query].[Van Neighborhood Served]) LEFT JOIN [Van
Womenshealth Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Womenshealth Query].[Van Neighborhood Served]) LEFT JOIN [Van
VVisit UnionAll Query] ON [Van Clients query].[Van Neighborhood
Served] = [Van VVisit UnionAll Query].[Van Neighborhood Served]) LEFT
JOIN [Van VVisit Prenat Query] ON [Van Clients query].[Van
Neighborhood Served] = [Van VVisit Prenat Query].[Van Neighborhood
Served]) LEFT JOIN [Van Deceased Query] ON [Van Clients query].[Van
Neighborhood Served] = [Van Deceased Query].[Van Neighborhood Served])
LEFT JOIN [Van Client Activity Count Query] ON [Van Clients query].
[Van Neighborhood Served] = [Van Client Activity Count Query].[Van
Neighborhood Served]) LEFT JOIN [Van FYOL Total Query] ON [Van Clients
query].[Van Neighborhood Served] = [Van FYOL Total Query].[Van
Neighborhood Served]) LEFT JOIN [Van PN Total Query] ON [Van Clients
query].[Van Neighborhood Served] = [Van PN Total Query].[Van
Neighborhood Served]
WHERE ((([Van Clients query].[Van Neighborhood Served])<>"City
Heights" And ([Van Clients query].[Van Neighborhood Served])<>"Newport
- Brighton Center" And ([Van Clients query].[Van Neighborhood
Served])<>"Newport - Housing Authority"))
GROUP BY [Van Clients query].[Van Neighborhood Served], [Van Clients
query].CountOfDemogID, [Van Clients query].SumFirstTime, [Van Client
Activity Count Query].SumofActiveClients, [Van Immunization
Query].ImmunCount, [Van Immunization Query].NonImmunCount, [Van
Immunization Query].UnknownImmunCount, [Van Delivered
Query].CountOfPregnancyID, [Van Delivered Query].TermNormalBirth, [Van
Delivered Query].PTLTermlBirth, [Van Delivered Query].PTLPTDBirth,
[Van Delivered Query].SumofPTL, [Van Delivered Query].SumofPTD, [Van
Delivered Query].SumofLBW, [Van Delivered Query].SumofVLBW, [Van
Delivered Query].SumofELBW, [Van Deceased Query].SumofMiscarry, [Van
Deceased Query].SumDeceased, [Van Deceased Query].SumLTF, [Van
Delivered Query].[SumOfNumber of Prenatal Visits - Kept], [Van FYOL Ed
Query.CountOfFYOLID], [Van FYOL6 Ed Query.CountOfFYOLID], [Van
Pregnancy Query].PregnancyCount, [Van Pregnancy Query].PosCount, [Van
Pregnancy Query].NegCount, [Van Prenatal Ed Query].CountOfVanID, [Van
FYOL6 Ed Query].SSICountFYOL6mo, [Van FYOL Ed
Query].SSICountFYOLBirth, [Van Womenshealth Query].SSICountWH, [Van PN
Total Query].PNClients, [Van FYOL Total Query].FYOLClients, [Van
Prenatal Ed Query].SSICountPrenat, Nz([SSICountFYOL6mo])
+Nz([SSICountFYOLBirth])+Nz([SSICountPrenat])+Nz([SSICountWH]), [Van
VVisit UnionAll Query].[CountOfEducation Date], [Van VVisit Prenat
Query].[CountOfEducation Date], [Van Clients query].[HMB Report Start
Date], [Van Clients query].[HMB Report Stop Date];
 
M

Michel Walsh

It happens if the ON expression is not based on at least ONE field without
duplicated values.

Given:

SELECT ... FROM a INNER JOIN b ON a.f1=b.f2

if neither a.f1 has no dup, neither b.f2 has no dup, you will get what you
described. If either a.f1 has no dup, either b.f2 has no dup, you won't get
dup in the end result, as long as you consider a dup the pair (couple) of
values {a.f1, b.f2}.

With:

a.f1, a.g1
-----------------
1 aa
1 bb
2 cc



and

b.f2, b.g2
--------------
1 dd
2 ee
2 ff


then

SELECT a.f1, a.g1, b.f2, b.g2 FROM a INNER JOIN b ON a.f1=b.f2

produces 4 rows (while we have only 3, initially):

a.f1 a.f2, b.f2, b.g2
-----------------------------
1 aa 1 dd
1 bb 1 dd
2 cc 2 ee
2 cc 2 ff


the first two rows in the result show (1, dd), from table 'b' doubled, and
the last two rows show (2, cc), from table 'a', doubled. So BOTH tables get
a 'duplicated' number of records, which is generally something we don't
want. On the other hand, having just one table seeing its rows duplicated is
not a problem, in general. But to get such a result, well, I repeat myself,
your data should not have dup, in both tables, involved in the fields of ON
clause.


Hoping it may help,
Vanderghast, Access MVP

Jeff said:
Hi

I'm doing a join of many queries into 1 query so that I can use all
this data in one report, and also to sum it up for another.

However, while I just want 1 record per neighborhood, I am getting
duplicate records of a few neighborhoods instead.

Could someone help explain why this is happening?

I've included the SQL to help.

Thanks

Jeff

SELECT [Van Clients query].[Van Neighborhood Served], [Van Clients
query].CountOfDemogID, [Van Clients query].SumFirstTime, [Van Client
Activity Count Query].SumofActiveClients, [Van Immunization
Query].ImmunCount, [Van Immunization Query].NonImmunCount, [Van
Immunization Query].UnknownImmunCount, [Van Delivered
Query].CountOfPregnancyID, [Van Delivered Query].TermNormalBirth, [Van
Delivered Query].PTLTermlBirth, [Van Delivered Query].PTLPTDBirth,
[Van Delivered Query].SumofPTL, [Van Delivered Query].SumofPTD, [Van
Delivered Query].SumofLBW, [Van Delivered Query].SumofVLBW, [Van
Delivered Query].SumofELBW, [Van Deceased Query].SumofMiscarry, [Van
Deceased Query].SumDeceased, [Van Deceased Query].SumLTF, [Van
Delivered Query].[SumOfNumber of Prenatal Visits - Kept], [SumOfNumber
of Prenatal Visits - Kept]/[CountOfPregnancyID] AS [Avg Prenatal
Kept], [Van FYOL Ed Query.CountOfFYOLID] AS CountOfFYOLIDBirth, [Van
FYOL6 Ed Query.CountOfFYOLID] AS CountOfFYOLID6mo, [Van Pregnancy
Query].PregnancyCount, [Van Pregnancy Query].PosCount, [Van Pregnancy
Query].NegCount, [Van Prenatal Ed Query].CountOfVanID, [Van FYOL6 Ed
Query].SSICountFYOL6mo, [Van FYOL Ed Query].SSICountFYOLBirth, [Van
Womenshealth Query].SSICountWH, [Van PN Total Query].PNClients, [Van
FYOL Total Query].FYOLClients, [Van Prenatal Ed Query].SSICountPrenat,
Nz([SSICountFYOL6mo])+Nz([SSICountFYOLBirth])+Nz([SSICountPrenat])
+Nz([SSICountWH]) AS TotalSSICount, [Van VVisit UnionAll Query].
[CountOfEducation Date], [Van VVisit Prenat Query].[CountOfEducation
Date], [Van Clients query].[HMB Report Start Date], [Van Clients
query].[HMB Report Stop Date]
FROM ((((((((((((([Van Clients query] LEFT JOIN [Van Immunization
Query] ON [Van Clients query].[Van Neighborhood Served] = [Van
Immunization Query].[Van Neighborhood Served]) LEFT JOIN [Van
Delivered Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Delivered Query].[Van Neighborhood Served]) LEFT JOIN [Van FYOL
Ed Query] ON [Van Clients query].[Van Neighborhood Served] = [Van FYOL
Ed Query].[Van Neighborhood Served]) LEFT JOIN [Van FYOL6 Ed Query] ON
[Van Clients query].[Van Neighborhood Served] = [Van FYOL6 Ed Query].
[Van Neighborhood Served]) LEFT JOIN [Van Immunization Query] AS [Van
Immunization Query_1] ON [Van Clients query].[Van Neighborhood Served]
= [Van Immunization Query_1].[Van Neighborhood Served]) LEFT JOIN [Van
Pregnancy Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Pregnancy Query].[Van Neighborhood Served]) LEFT JOIN [Van
Prenatal Ed Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Prenatal Ed Query].[Van Neighborhood Served]) LEFT JOIN [Van
Womenshealth Query] ON [Van Clients query].[Van Neighborhood Served] =
[Van Womenshealth Query].[Van Neighborhood Served]) LEFT JOIN [Van
VVisit UnionAll Query] ON [Van Clients query].[Van Neighborhood
Served] = [Van VVisit UnionAll Query].[Van Neighborhood Served]) LEFT
JOIN [Van VVisit Prenat Query] ON [Van Clients query].[Van
Neighborhood Served] = [Van VVisit Prenat Query].[Van Neighborhood
Served]) LEFT JOIN [Van Deceased Query] ON [Van Clients query].[Van
Neighborhood Served] = [Van Deceased Query].[Van Neighborhood Served])
LEFT JOIN [Van Client Activity Count Query] ON [Van Clients query].
[Van Neighborhood Served] = [Van Client Activity Count Query].[Van
Neighborhood Served]) LEFT JOIN [Van FYOL Total Query] ON [Van Clients
query].[Van Neighborhood Served] = [Van FYOL Total Query].[Van
Neighborhood Served]) LEFT JOIN [Van PN Total Query] ON [Van Clients
query].[Van Neighborhood Served] = [Van PN Total Query].[Van
Neighborhood Served]
WHERE ((([Van Clients query].[Van Neighborhood Served])<>"City
Heights" And ([Van Clients query].[Van Neighborhood Served])<>"Newport
- Brighton Center" And ([Van Clients query].[Van Neighborhood
Served])<>"Newport - Housing Authority"))
GROUP BY [Van Clients query].[Van Neighborhood Served], [Van Clients
query].CountOfDemogID, [Van Clients query].SumFirstTime, [Van Client
Activity Count Query].SumofActiveClients, [Van Immunization
Query].ImmunCount, [Van Immunization Query].NonImmunCount, [Van
Immunization Query].UnknownImmunCount, [Van Delivered
Query].CountOfPregnancyID, [Van Delivered Query].TermNormalBirth, [Van
Delivered Query].PTLTermlBirth, [Van Delivered Query].PTLPTDBirth,
[Van Delivered Query].SumofPTL, [Van Delivered Query].SumofPTD, [Van
Delivered Query].SumofLBW, [Van Delivered Query].SumofVLBW, [Van
Delivered Query].SumofELBW, [Van Deceased Query].SumofMiscarry, [Van
Deceased Query].SumDeceased, [Van Deceased Query].SumLTF, [Van
Delivered Query].[SumOfNumber of Prenatal Visits - Kept], [Van FYOL Ed
Query.CountOfFYOLID], [Van FYOL6 Ed Query.CountOfFYOLID], [Van
Pregnancy Query].PregnancyCount, [Van Pregnancy Query].PosCount, [Van
Pregnancy Query].NegCount, [Van Prenatal Ed Query].CountOfVanID, [Van
FYOL6 Ed Query].SSICountFYOL6mo, [Van FYOL Ed
Query].SSICountFYOLBirth, [Van Womenshealth Query].SSICountWH, [Van PN
Total Query].PNClients, [Van FYOL Total Query].FYOLClients, [Van
Prenatal Ed Query].SSICountPrenat, Nz([SSICountFYOL6mo])
+Nz([SSICountFYOLBirth])+Nz([SSICountPrenat])+Nz([SSICountWH]), [Van
VVisit UnionAll Query].[CountOfEducation Date], [Van VVisit Prenat
Query].[CountOfEducation Date], [Van Clients query].[HMB Report Start
Date], [Van Clients query].[HMB Report Stop Date];
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

Adding data from a new table 0
Another Date Query 2
Invalid use of null in query expression 3
Running total in query 2
Query - Force non-existant "Group by" values 1
Problem - can create an updatable query 2
Null values in a cross tab query 2
problem - data source query not updatable 0

Top