trying to fix a duplication of outputs

[nexshark]

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?

 

[ByteMyzer]

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));

 

[nexshark]

Thank You, Bytemyzer...

i WILL GIVE IT A SHOT AND LET YOU KNOW HOW IT WORKS!!

 

[nexshark]

Worked like a charm.

Thank you so much for the assist!

 

[sweetyo]

Queries with Null Values

You figured this out for my co-worker and it does work like a charm. Now I would like to pose this question for you using the same table. I have Null values in which I do not want to show in the query (meaning, if client John Smith was seen on two different dates and both dates have a Null value, I don't want the query to show that. Can you help me?

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));

 

[ByteMyzer]

Hmm, this MIGHT give you what you're looking for:

SELECT DISTINCT T1.Name
FROM TableA AS T1
WHERE (T1.Date1 Is Not Null) AND (T1.Date2 Is Not Null) AND 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));