Question:
I am a newbie to Access, and wonder if someone could give me a hand with a query I am trying to create. If I can beg your indulgence, my problem is as follows:
I have two tables, one with names and date ranges, as follows:
John Smith 4/1/01 6/1/01
John Smith 2/2/98 3/2/98
Irv Jones 3/9/99 3/16/99
Bob Welch 3/1/01 4/4/01
Bob Welch 9/1/02 9/16/02
My second Table is a list of co-workers for each person from table A. I am querying to find overlap of the dates, as would be seen in John Smith's first row and Bob Welch's first row. My query then pulls these two men out and lists coworkers for each, but because they have two date ranges associated with each, it lists the co-workers twice for each. I have tried using SQL Distinct and Distinct Row, as well as Unique Record and Unique Value functions, but think I am using them wrong to achieve my goal. Can anyone give me a hand based upon this rambling dissertation?
Response:
I love brain-teasers, and your case was a good one. Don't be discouraged; the means to your goal of pulling back the distinct names in this case is not an obvious one. I created a table named TableA and added the fields Name, Date1 and Date2, and filled them with the sample data you provided.
Using that table structure, here's the query I came up with that will pull back the distinct-name scenario that you're looking for. It should be easily adaptable for your specific table structure(s). See what you think:
SELECT DISTINCT T1.Name
FROM TableA AS T1
WHERE EXISTS
(SELECT *
FROM TableA AS T2
WHERE T2.Name <> T1.Name
AND ((T2.Date1 BETWEEN T1.Date1 AND T1.Date2)
OR (T2.Date2 BETWEEN T1.Date1 AND T1.Date2))
;
This worked beautifully. Here is another question.
In my table, I have a field with comments (but some do not have comments for particular clients) what I would like to do is if the comment field is empty, I would like that particular record (row) not to show in the query/report. What do I need to do? Thanks for any help.
I am a newbie to Access, and wonder if someone could give me a hand with a query I am trying to create. If I can beg your indulgence, my problem is as follows:
I have two tables, one with names and date ranges, as follows:
John Smith 4/1/01 6/1/01
John Smith 2/2/98 3/2/98
Irv Jones 3/9/99 3/16/99
Bob Welch 3/1/01 4/4/01
Bob Welch 9/1/02 9/16/02
My second Table is a list of co-workers for each person from table A. I am querying to find overlap of the dates, as would be seen in John Smith's first row and Bob Welch's first row. My query then pulls these two men out and lists coworkers for each, but because they have two date ranges associated with each, it lists the co-workers twice for each. I have tried using SQL Distinct and Distinct Row, as well as Unique Record and Unique Value functions, but think I am using them wrong to achieve my goal. Can anyone give me a hand based upon this rambling dissertation?
Response:
I love brain-teasers, and your case was a good one. Don't be discouraged; the means to your goal of pulling back the distinct names in this case is not an obvious one. I created a table named TableA and added the fields Name, Date1 and Date2, and filled them with the sample data you provided.
Using that table structure, here's the query I came up with that will pull back the distinct-name scenario that you're looking for. It should be easily adaptable for your specific table structure(s). See what you think:
SELECT DISTINCT T1.Name
FROM TableA AS T1
WHERE EXISTS
(SELECT *
FROM TableA AS T2
WHERE T2.Name <> T1.Name
AND ((T2.Date1 BETWEEN T1.Date1 AND T1.Date2)
OR (T2.Date2 BETWEEN T1.Date1 AND T1.Date2))
;This worked beautifully. Here is another question.
In my table, I have a field with comments (but some do not have comments for particular clients) what I would like to do is if the comment field is empty, I would like that particular record (row) not to show in the query/report. What do I need to do? Thanks for any help.
