query data based on 2 or more like variables from a single field

[DNRTech]

I have a table that contains Activities that I would like to query data out of. There are two types of Activities. One type happens every time while the other happens occasionally, but at the same time as the first type. I want result records that only have both types of Activities associated with their date/time, not just one or the other.



A typical Activity table looks like this:

Activity Type	Date/Time	Location
1	1/1/1	A
2	1/1/1	A
1	2/1/1	B
1	3/1/1	B
2	3/1/1	A

How do I query only date times where both types 1 and 2 are present? The resulting table would look like this:

Activity Type	Date/Time	Location
1	1/1/1	A
2	1/1/1	A
1	3/1/1	B
2	3/1/1	B

Thanks in advance for the advice!

[June7]

A series of queries can produce this output:

DateTime	Activity1.Activity	Activity2.Activity
1/1/2011	1	2
3/1/2011	1	2

ActivityDates
SELECT DISTINCT DateTime FROM Table1;

Activity1
SELECT * FROM Table1 WHERE Activity="1";

Activity2
SELECT * FROM Table1 WHERE Activity="2";

ActivityBoth
SELECT ActivityDates.DateTime, Activity1.Activity, Activity2.Activity, [Activity2].[Location]) AS LocCode
FROM Activity2 RIGHT JOIN (Activity1 RIGHT JOIN ActivityDates ON Activity1.DateTime = ActivityDates.DateTime) ON Activity2.DateTime = ActivityDates.DateTime
WHERE (((Activity1.Activity)="1") AND ((Activity2.Activity)="2"));

Anything else might involve VBA coding.

[recyan]

Activity Type	Date/Time	Location
1	1/1/1	A
2	1/1/1	A
1	3/1/1	B
2	3/1/1	B

Just a few questions,
1) Shouldn't the last record of your result have Location as "A".

Activity Type	Date/Time	Location
1	1/1/1	A
2	1/1/1	A
1	3/1/1	B
2	3/1/1	B

2) On a particular date, either one & only one of the activities can happen OR only a Max of two different activities (Activities 1 & 2) can happen.

If both above are true, query the Main Table, try Grouping on Date Field & filter only those records that have a Activity Type Count greater than 1. Then use this to join on the Date field to the Main Table & get All the Activity Types for those Dates.

Thanks

[June7]

Fantastic recyan! Correct about the A. I started out with a grouping query but didn't think of the count and couldn't follow through. So the following nested SQL will return the records.

SELECT Table1.ActivityDate, Location, CountOfActivity
FROM (SELECT ActivityDate, Count(Activity) AS CountOfActivity FROM Table1 GROUP BY ActivityDate) As DatesLocCount
INNER JOIN Table1 ON DatesLocCount.ActivityDate = Table1.ActivityDate
WHERE CountOfActivity>1;

[recyan]

Thanks
Yes, though with a little twist, I was thinking of filtering out the count in the DatesLocCount query itself to reduce the load on the final query (Do not know whether technically it works that way or not).
Something like below :

DatesLocCount

SELECT 
    Count(Table1.Activity) AS CountOfActivityType, 
    Table1.ActivityDate
FROM 
    Table1
GROUP BY 
    Table1.ActivityDate
HAVING 
    (((Count(Table1.Activity))>1));

______________________________________
The final query :

SELECT 
    DatesLocCount.ActivityDate, 
    Table1.ActivityDate, 
    Table1.Activity, 
    Table1.Location
FROM 
    DatesLocCount 
    INNER JOIN 
    Table1 
ON 
    DatesLocCount.ActivityDate = Table1.ActivityDate;

Thanks

[June7]

Don't know if it matters either way. Would have to test with a large dataset and evaluate performance of both.

[recyan]

Perhaps,

SELECT 
    Table1.ActivityDate, 
    Table1.ActivityType, 
    Table1.Location
FROM 
    Table1
WHERE 
    (((Table1.ActivityDate) 
    In 
    (
        SELECT 
            Table1.ActivityDate
        FROM 
            Table1
        GROUP BY 
            Table1.ActivityDate
        HAVING 
            (((Count(Table1.ActivityType))>1))
    )))
ORDER BY 
    Table1.ActivityDate, Table1.ActivityType;

There are these times, sometimes, when I have more than one solution for the same question, which give the same answer & I am frustrated at not being able to know & understand ( rather at not having spent time on this ) which one is the best amongst those available ?

Thanks

[DNRTech]

Wow, there is a lot to respond to here!

I was wrong not about the output table but rather the input table. I should have read:

Activity Type	Date/Time	Location
1	1/1/1	A
2	1/1/1	A
1	2/1/1	B
1	3/1/1	B
2	3/1/1	B

Sorry about this, I was trying to show how locations of Activities can be different but grouped activities always happen at the same location.

I had the same idea about using count >1 on the date, but couldn't get it to pan out right.

I do really like June7's first solution. It's given me the results I'm looking for exactly! Thanks everyone!