|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 5th, 2009, 07:20 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Qry returning too many records - join question
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. -- jubu 
|
|
#2
January 5th, 2009, 08:16 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Qry returning too many records - join question
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" wrote in message ... 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. -- jubu
|
|
#3
January 5th, 2009, 08:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Qry returning too many records - join question
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" wrote: 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" wrote in message ... 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. -- jubu
|
|
#4
January 5th, 2009, 09:16 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Qry returning too many records - join question
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" wrote in message news  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" wrote: 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" wrote in message ... 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. -- jubu
|
|
#5
February 6th, 2009, 04:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Qry returning too many records - join question
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" wrote: 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" wrote in message news 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" wrote: 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" wrote in message ... 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. -- jubu
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
