Conditional Query To Show Count Of Correct Matches In Data (1 Viewer)

[kobesux]

I understand that the topic title is vague as buzzwords can get so let me give some sample data below:

Table "TARGET"

PLATE COLOR UTENSIL
1 RED FORK
1 RED SPOON
1 BLUE FORK
1 BLUE FORK
2 BLUE SPOON
3 BLUE FORK
3 RED FORK
4 BLUE FORK
4 BLUE SPOON
4 BLUE SPOON
4 RED FORK
4 RED SPOON
5 RED FORK
5 BLUE SPOON
5 RED FORK

Table "RESULT"

PLATE MATCHES
1 1
4 2
5 1

From the data I gave above, my aim is to match a fork and a spoon with the opposite color on the same plate no.

So, what I have above is:
- I have 1 match made on plate 1 (blue fork & red spoon)
- I have no matches on plate 2 (a lonely blue spoon)
- I have no matches on plate 3 (both are forks)
- I have 2 matches on plate 4 (a blue fork & red spoon and a red fork & blue spoon)
- and 1 match made on plate 5 (red fork & blue spoon)

What I want to know is how do I implement this kind of conditional query in MS access design view?

Any help would be very deeply appreciated!

 

[Uncle Gizmo]

If this question is an analogy for a particular problem, then I am very impressed with the way you have presented the question.

However I would say that I am having some difficulty with this:
- I have 2 matches on plate 4 (a blue fork & red spoon and a red fork & blue spoon)

Now if it were to read thus:
- I have 2 matches on plate 4 (a red fork & red spoon and a blue fork & blue spoon)

Then the structure of your analogy would make sense to me, however the way you’ve got it, indicates to me there is either a problem with the analogy or there’s some information I don’t understand.

 

[kobesux]

No, my statement still stands correct based on my intentions.

I declare 2 rows in the table as a match when I get:
- a blue fork & a red spoon OR
- a red fork & a blue spoon

1 match means having 2 records with opposite utensils AND opposite colors

 

[vbaInet]

It's not quite a match if you're looking for completely different keywords which aren't really the opposite because we can't infer that Blue is the opposite of Red neither can we say that Fork is the opposite of Spoon.

I think what you need is a Subquery in the SELECT clause Where the Subquery fields is:

Not Red And Not (Red and Fork)

 

[kobesux]

I understand what you're trying to point out. Maybe it would be clearer if I changed the columns and its corresponding values. Let's change the "COLORS"and "ÜTENSIL" column into "TIMELINE" column having ('beginning' & 'end') as values AND "FORM" column having ('chicken' & 'egg') as values.

So from the change above, to have a "match", I want to have two rows of having a beginning-chicken AND a end-egg OR a beginning-egg AND a end-chicken.

 

[vbaInet]

Hard to understand without showing it in the format in which you presented your first post. Use that same format and substitute the values and column names in. Then show your desired result.

To preserve the indents or spaces, wrap your table in code tags:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[philben]

Unless I'm wrong, this query should help you :

SELECT  T3.PLATE,
  Count(*)/2 AS MATCHES
FROM (
  SELECT T1.PLATE
  FROM Target AS T1 
    INNER JOIN Target AS T2 
    ON T1.PLATE = T2.PLATE 
  WHERE T1.COLOR<>T2.COLOR AND T1.UTENSIL<>T2.UTENSIL 
  GROUP BY T1.PLATE, T1.COLOR, T1.UTENSIL
  )  AS T3
GROUP BY T3.PLATE;

Best Regards,

Philippe

 

[spikepl]

#8

You are comitting thread-hijacking, which is not recommended practice in any forum. I have therefore reported your numerous hijack attempts.

If you have an issue start your own thread.

Besides - you need to make clear whether are you looking for volunteer help or paid support.