Qry returning too many records - join question

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.
 
M

Michel Walsh

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...
 
J

jubu

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...
 
M

Michel Walsh

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...
 
J

jubu

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...
 

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

Help with calculation flow days 0
Date range and creating a formula to use in a second date range 0
Fiscal year 2
Query for Running Inventory Balance 1
same time range on different days 6
Find Period Date? 5
Please Help with Query to Return a Balance Column 5
Calculating flow days help 1

Top