query to return the records with same data in two fields

I want to create a query in access database that will return all the records with the same values in Date and prod
E.g

MYID

W1   Date1   22
W1   Date1   22  
W2   Date2   23
W2   Date2   23
W3   Date3   100

My query should read W1 Matches W2 at Date1,22 and Date2,23

Thank you.
AiyshaAsked:
Who is Participating?
 
Robberbaron (robr)Commented:
need more info and possible some better data. do you have an index field ?
Which is the Product field ?   is this the table.?  A few records of real data will go a long way.....

MyID    Product    Date    Count
------+----------+-------+--------
 1          W1        Date1     22
 2          W1        Date1     22
 3          W2        Date2    23
 4          W2        Date2   23
5           W3        Date3   100


SELECT T1.Product FROM yourtable T1, yourtable T2
WHERE
  T1.Date=T2.Date
 AND
 T1.Product=T2.Product
  AND
 T1.MyId <> T2.MyId

0
 
ShauliCommented:
"SELECT * FROM yourtable WHERE fielddate=#" & yourDateVar & "# AND fieldprod=" & yourProdVar & ";"

if fieldprod is not numeric then the sql string should read:

"SELECT * FROM yourtable WHERE fielddate=#" & yourDateVar & "# AND fieldprod='" & yourProdVar & "';"

Replace yourtable with the real table name, and also fielddate and fieldprod with the corresponding field names.

S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.