Qry returning too many records - join question

  • Thread starter Thread starter jubu
  • Start date Start date
J

jubu

I have 3 tables in my database (With sample info. Actually there are more,
but these are the ones giving me grief.). tblClientcountyInfo keeps track of
county info and when it changes. tblEmploy keeps track of when they begin
employment and where. tblEmpRetention stores information on the employment
retention information.

tblClientCountyInfo
RecordID StaffNo County DateRevised
1 471591 Ottawa 3/26/08
2 534341 Muskegon 9/25/07
3 534341 Ottawa 4/9/08
4 471591 Muskegon 7/14/08

tblEmploy
RecordID StaffNo EmployerName EmployStartDate
70 471591 Tri-State 4/1/08
90 534341 ABC Company 4/23/08

tblEmpRetention
id EmployRecordID StaffNo Type AsOfDate
67 70 471591 Placement 4/1/08
87 90 534341 Placement 4/23/08
154 70 471591 30-Day 5/1/08
172 90 534341 30-Day 5/23/08
231 70 471591 60-Day 5/31/08
249 90 534341 60-Day 6/22/08
293 70 471591 90-Day 6/30/08
311 90 534341 90-Day 7/22/08

This is the SQL of the query and the results are shown below in the table.
Here’s my problem: I do NOT want any records to appear, if they do not fall
between the dates correctly. Please look at the records for Staff No 534341.
I do not want to see the records where Date Revised are 9/25/07, because
their EmployStartDate was on or after the second record for this person
4/9/08.

SELECT tblClientCountyInfo.StaffNo, tblClientCountyInfo.County,
tblClientCountyInfo.DateRevised, tblEmploy.EmployerName,
tblEmploy.EmployStartDate, tblEmpRetention.Type, tblEmpRetention.AsOfDate,
IIf([asofdate]<[DateRevised],"",[Asofdate]) AS ValidDates
FROM (tblClientCountyInfo INNER JOIN tblEmploy ON
tblClientCountyInfo.StaffNo = tblEmploy.StaffNo) INNER JOIN tblEmpRetention
ON tblEmploy.RecordID = tblEmpRetention.EmployRecordID
ORDER BY tblClientCountyInfo.StaffNo, tblClientCountyInfo.DateRevised,
tblEmpRetention.AsOfDate;


qryPlacementRetentionInfo-
StaffNo County DateRevised EmployerName EmployStartDate Type AsOfDate ValidDates
471591 Ottawa 3/26/08 Tri-State 4/1/08 Placement 4/1/08 4/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 30-Day 5/1/08 5/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 60-Day 5/31/08 5/31/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 90-Day 6/30/08 6/30/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 Placement 4/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 30-Day 5/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 60-Day 5/31/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 90-Day 6/30/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 90-Day 7/22/08 7/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 90-Day 7/22/08 7/22/08

thanks in advance for any suggestions.
 
It seems you may make a query on ClientCountyInfo in order to only keep the
latest information (in this case, essentially removing the second record)
and then, use that query, rather than the table, in your query. To get the
only the latest records, see http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP


jubu said:
I have 3 tables in my database (With sample info. Actually there are more,
but these are the ones giving me grief.). tblClientcountyInfo keeps track
of
county info and when it changes. tblEmploy keeps track of when they begin
employment and where. tblEmpRetention stores information on the
employment
retention information.

tblClientCountyInfo
RecordID StaffNo County DateRevised
1 471591 Ottawa 3/26/08
2 534341 Muskegon 9/25/07
3 534341 Ottawa 4/9/08
4 471591 Muskegon 7/14/08

tblEmploy
RecordID StaffNo EmployerName EmployStartDate
70 471591 Tri-State 4/1/08
90 534341 ABC Company 4/23/08

tblEmpRetention
id EmployRecordID StaffNo Type AsOfDate
67 70 471591 Placement 4/1/08
87 90 534341 Placement 4/23/08
154 70 471591 30-Day 5/1/08
172 90 534341 30-Day 5/23/08
231 70 471591 60-Day 5/31/08
249 90 534341 60-Day 6/22/08
293 70 471591 90-Day 6/30/08
311 90 534341 90-Day 7/22/08

This is the SQL of the query and the results are shown below in the table.
Here's my problem: I do NOT want any records to appear, if they do not
fall
between the dates correctly. Please look at the records for Staff No
534341.
I do not want to see the records where Date Revised are 9/25/07, because
their EmployStartDate was on or after the second record for this person
4/9/08.

SELECT tblClientCountyInfo.StaffNo, tblClientCountyInfo.County,
tblClientCountyInfo.DateRevised, tblEmploy.EmployerName,
tblEmploy.EmployStartDate, tblEmpRetention.Type, tblEmpRetention.AsOfDate,
IIf([asofdate]<[DateRevised],"",[Asofdate]) AS ValidDates
FROM (tblClientCountyInfo INNER JOIN tblEmploy ON
tblClientCountyInfo.StaffNo = tblEmploy.StaffNo) INNER JOIN
tblEmpRetention
ON tblEmploy.RecordID = tblEmpRetention.EmployRecordID
ORDER BY tblClientCountyInfo.StaffNo, tblClientCountyInfo.DateRevised,
tblEmpRetention.AsOfDate;


qryPlacementRetentionInfo-1
StaffNo County DateRevised EmployerName EmployStartDate Type AsOfDate
ValidDates
471591 Ottawa 3/26/08 Tri-State 4/1/08 Placement 4/1/08 4/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 30-Day 5/1/08 5/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 60-Day 5/31/08 5/31/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 90-Day 6/30/08 6/30/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 Placement 4/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 30-Day 5/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 60-Day 5/31/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 90-Day 6/30/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 90-Day 7/22/08 7/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 90-Day 7/22/08 7/22/08

thanks in advance for any suggestions.
Click to expand...
 
I considered that, however, if an individual starts one job, and then gets
another one during the same fiscal year, I still want to be able to see both
of those records.
--
jubu


Michel Walsh said:
It seems you may make a query on ClientCountyInfo in order to only keep the
latest information (in this case, essentially removing the second record)
and then, use that query, rather than the table, in your query. To get the
only the latest records, see http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP


jubu said:
I have 3 tables in my database (With sample info. Actually there are more,
but these are the ones giving me grief.). tblClientcountyInfo keeps track
of
county info and when it changes. tblEmploy keeps track of when they begin
employment and where. tblEmpRetention stores information on the
employment
retention information.

tblClientCountyInfo
RecordID StaffNo County DateRevised
1 471591 Ottawa 3/26/08
2 534341 Muskegon 9/25/07
3 534341 Ottawa 4/9/08
4 471591 Muskegon 7/14/08

tblEmploy
RecordID StaffNo EmployerName EmployStartDate
70 471591 Tri-State 4/1/08
90 534341 ABC Company 4/23/08

tblEmpRetention
id EmployRecordID StaffNo Type AsOfDate
67 70 471591 Placement 4/1/08
87 90 534341 Placement 4/23/08
154 70 471591 30-Day 5/1/08
172 90 534341 30-Day 5/23/08
231 70 471591 60-Day 5/31/08
249 90 534341 60-Day 6/22/08
293 70 471591 90-Day 6/30/08
311 90 534341 90-Day 7/22/08

This is the SQL of the query and the results are shown below in the table.
Here's my problem: I do NOT want any records to appear, if they do not
fall
between the dates correctly. Please look at the records for Staff No
534341.
I do not want to see the records where Date Revised are 9/25/07, because
their EmployStartDate was on or after the second record for this person
4/9/08.

SELECT tblClientCountyInfo.StaffNo, tblClientCountyInfo.County,
tblClientCountyInfo.DateRevised, tblEmploy.EmployerName,
tblEmploy.EmployStartDate, tblEmpRetention.Type, tblEmpRetention.AsOfDate,
IIf([asofdate]<[DateRevised],"",[Asofdate]) AS ValidDates
FROM (tblClientCountyInfo INNER JOIN tblEmploy ON
tblClientCountyInfo.StaffNo = tblEmploy.StaffNo) INNER JOIN
tblEmpRetention
ON tblEmploy.RecordID = tblEmpRetention.EmployRecordID
ORDER BY tblClientCountyInfo.StaffNo, tblClientCountyInfo.DateRevised,
tblEmpRetention.AsOfDate;


qryPlacementRetentionInfo-1
StaffNo County DateRevised EmployerName EmployStartDate Type AsOfDate
ValidDates
471591 Ottawa 3/26/08 Tri-State 4/1/08 Placement 4/1/08 4/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 30-Day 5/1/08 5/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 60-Day 5/31/08 5/31/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 90-Day 6/30/08 6/30/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 Placement 4/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 30-Day 5/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 60-Day 5/31/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 90-Day 6/30/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 90-Day 7/22/08 7/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 90-Day 7/22/08 7/22/08

thanks in advance for any suggestions.
Click to expand...
Click to expand...
 
The strategy will be the same, but the query removing the unwanted records
from ClientCountryInfo will ... simply be more complex...(I am aware it
sounds like an oxymora). Indeed, in this case, the client should appear
twice in the result of the query (to be elaborated). The logic of this
query, to be produced, sounds quite laborious, though, and thus, seems to
legitimate to do the job THERE, in a query specific to that specific
purpose, rather than to carry that extra logic in the final (actual) query,
already complex enough by itself.


Vanderghast, Access MVP


jubu said:
I considered that, however, if an individual starts one job, and then gets
another one during the same fiscal year, I still want to be able to see
both
of those records.
--
jubu


Michel Walsh said:
It seems you may make a query on ClientCountyInfo in order to only keep
the
latest information (in this case, essentially removing the second record)
and then, use that query, rather than the table, in your query. To get
the
only the latest records, see
http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP


jubu said:
I have 3 tables in my database (With sample info. Actually there are
more,
but these are the ones giving me grief.). tblClientcountyInfo keeps
track
of
county info and when it changes. tblEmploy keeps track of when they
begin
employment and where. tblEmpRetention stores information on the
employment
retention information.

tblClientCountyInfo
RecordID StaffNo County DateRevised
1 471591 Ottawa 3/26/08
2 534341 Muskegon 9/25/07
3 534341 Ottawa 4/9/08
4 471591 Muskegon 7/14/08

tblEmploy
RecordID StaffNo EmployerName EmployStartDate
70 471591 Tri-State 4/1/08
90 534341 ABC Company 4/23/08

tblEmpRetention
id EmployRecordID StaffNo Type AsOfDate
67 70 471591 Placement 4/1/08
87 90 534341 Placement 4/23/08
154 70 471591 30-Day 5/1/08
172 90 534341 30-Day 5/23/08
231 70 471591 60-Day 5/31/08
249 90 534341 60-Day 6/22/08
293 70 471591 90-Day 6/30/08
311 90 534341 90-Day 7/22/08

This is the SQL of the query and the results are shown below in the
table.
Here's my problem: I do NOT want any records to appear, if they do not
fall
between the dates correctly. Please look at the records for Staff No
534341.
I do not want to see the records where Date Revised are 9/25/07,
because
their EmployStartDate was on or after the second record for this person
4/9/08.

SELECT tblClientCountyInfo.StaffNo, tblClientCountyInfo.County,
tblClientCountyInfo.DateRevised, tblEmploy.EmployerName,
tblEmploy.EmployStartDate, tblEmpRetention.Type,
tblEmpRetention.AsOfDate,
IIf([asofdate]<[DateRevised],"",[Asofdate]) AS ValidDates
FROM (tblClientCountyInfo INNER JOIN tblEmploy ON
tblClientCountyInfo.StaffNo = tblEmploy.StaffNo) INNER JOIN
tblEmpRetention
ON tblEmploy.RecordID = tblEmpRetention.EmployRecordID
ORDER BY tblClientCountyInfo.StaffNo, tblClientCountyInfo.DateRevised,
tblEmpRetention.AsOfDate;


qryPlacementRetentionInfo-1
StaffNo County DateRevised EmployerName EmployStartDate Type AsOfDate
ValidDates
471591 Ottawa 3/26/08 Tri-State 4/1/08 Placement 4/1/08 4/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 30-Day 5/1/08 5/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 60-Day 5/31/08 5/31/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 90-Day 6/30/08 6/30/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 Placement 4/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 30-Day 5/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 60-Day 5/31/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 90-Day 6/30/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 90-Day 7/22/08 7/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 90-Day 7/22/08 7/22/08

thanks in advance for any suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Apologies for delaying my response. Your message got me thinking again and I
realized that I had to link my tables through another piece of information,
which referenced the record of their "county" information. After toiling
with this, it worked and continues to work beautifully. Thank you for
helping me to re-process my thinking!!
--
jubu


Michel Walsh said:
The strategy will be the same, but the query removing the unwanted records
from ClientCountryInfo will ... simply be more complex...(I am aware it
sounds like an oxymora). Indeed, in this case, the client should appear
twice in the result of the query (to be elaborated). The logic of this
query, to be produced, sounds quite laborious, though, and thus, seems to
legitimate to do the job THERE, in a query specific to that specific
purpose, rather than to carry that extra logic in the final (actual) query,
already complex enough by itself.


Vanderghast, Access MVP


jubu said:
I considered that, however, if an individual starts one job, and then gets
another one during the same fiscal year, I still want to be able to see
both
of those records.
--
jubu


Michel Walsh said:
It seems you may make a query on ClientCountyInfo in order to only keep
the
latest information (in this case, essentially removing the second record)
and then, use that query, rather than the table, in your query. To get
the
only the latest records, see
http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP


I have 3 tables in my database (With sample info. Actually there are
more,
but these are the ones giving me grief.). tblClientcountyInfo keeps
track
of
county info and when it changes. tblEmploy keeps track of when they
begin
employment and where. tblEmpRetention stores information on the
employment
retention information.

tblClientCountyInfo
RecordID StaffNo County DateRevised
1 471591 Ottawa 3/26/08
2 534341 Muskegon 9/25/07
3 534341 Ottawa 4/9/08
4 471591 Muskegon 7/14/08

tblEmploy
RecordID StaffNo EmployerName EmployStartDate
70 471591 Tri-State 4/1/08
90 534341 ABC Company 4/23/08

tblEmpRetention
id EmployRecordID StaffNo Type AsOfDate
67 70 471591 Placement 4/1/08
87 90 534341 Placement 4/23/08
154 70 471591 30-Day 5/1/08
172 90 534341 30-Day 5/23/08
231 70 471591 60-Day 5/31/08
249 90 534341 60-Day 6/22/08
293 70 471591 90-Day 6/30/08
311 90 534341 90-Day 7/22/08

This is the SQL of the query and the results are shown below in the
table.
Here's my problem: I do NOT want any records to appear, if they do not
fall
between the dates correctly. Please look at the records for Staff No
534341.
I do not want to see the records where Date Revised are 9/25/07,
because
their EmployStartDate was on or after the second record for this person
4/9/08.

SELECT tblClientCountyInfo.StaffNo, tblClientCountyInfo.County,
tblClientCountyInfo.DateRevised, tblEmploy.EmployerName,
tblEmploy.EmployStartDate, tblEmpRetention.Type,
tblEmpRetention.AsOfDate,
IIf([asofdate]<[DateRevised],"",[Asofdate]) AS ValidDates
FROM (tblClientCountyInfo INNER JOIN tblEmploy ON
tblClientCountyInfo.StaffNo = tblEmploy.StaffNo) INNER JOIN
tblEmpRetention
ON tblEmploy.RecordID = tblEmpRetention.EmployRecordID
ORDER BY tblClientCountyInfo.StaffNo, tblClientCountyInfo.DateRevised,
tblEmpRetention.AsOfDate;


qryPlacementRetentionInfo-1
StaffNo County DateRevised EmployerName EmployStartDate Type AsOfDate
ValidDates
471591 Ottawa 3/26/08 Tri-State 4/1/08 Placement 4/1/08 4/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 30-Day 5/1/08 5/1/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 60-Day 5/31/08 5/31/08
471591 Ottawa 3/26/08 Tri-State 4/1/08 90-Day 6/30/08 6/30/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 Placement 4/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 30-Day 5/1/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 60-Day 5/31/08
471591 Muskegon 7/14/08 Tri-State 4/1/08 90-Day 6/30/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Muskegon 9/25/07 ABC Company 4/23/08 90-Day 7/22/08 7/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 Placement 4/23/08 4/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 30-Day 5/23/08 5/23/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 60-Day 6/22/08 6/22/08
534341 Ottawa 4/9/08 ABC Company 4/23/08 90-Day 7/22/08 7/22/08

thanks in advance for any suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top