Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

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.
0
Aiysha
Asked:
Aiysha
1 Solution
 
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now