Help with Query

[doggo]

Hi - I am a little rust on my Access and SQL and I need a little advise on the best way to proceed with a specific problem. Here's an example of the data:.

Table Name = Temp
DATA
A ----- P1
A ----- P2
A ----- P3
B ----- P1
B ----- P2
B ----- P3
B ----- P4
C ----- P2
C ----- P3
C ----- P4

What I need to do is query the above data and presents rows that exludes data from Column 2 that does not appear for all records in Column A. this is how I would expect the result to look:

RESULT:
A ----- P2
A ----- P3
B ----- P2
B ----- P3
C ----- P2
C ----- P3

EXCLUDED:
A ----- P1
B ----- P1
B ----- P4
C ----- P4

 

[Andrew Fergus]

Hi doggo
You can do this with 3 queries - it's a bit clunky bit it works. However I have assumed that at least one of the unique values in the 2nd column has a matching record for every unique value in the 1st column. If this is not the case then please advise.

For the purposes of this example, I used the table name [doggo_temp] and I used the field names [field1] and [field2]. You can get into the SQL of your queries by going into the query design and clicking on View -> SQL View and then copy -> paste the SQL I have posted below into your query and change the table and field names to match your database.

The first query counts the number of field1 items for each unique value in field2. The SQL looks like this :

SELECT doggo_temp.field2, Count(doggo_temp.field1) AS Num
FROM doggo_temp
GROUP BY doggo_temp.field2;

I saved the query as 'doggo_1'. The 2nd query works out the maximum count value, using this SQL :

SELECT Max(doggo_1.Num) AS MaxOfNum
FROM doggo_1;

I saved the query as 'doggo_2'. The 3rd query matches the max count with the counts from the first query and then returns the appropriate values from the original table, using this SQL :

SELECT doggo_temp.field1, doggo_temp.field2
FROM (doggo_2 LEFT JOIN doggo_1 ON doggo_2.MaxOfNum = doggo_1.Num) LEFT JOIN doggo_temp ON doggo_1.field2 = doggo_temp.field2
ORDER BY doggo_temp.field1, doggo_temp.field2;

This will give you a list of 'inclusions'.
HTH, Andrew.