query to return the records with same data in two fields

Posted on 2006-05-04
Last Modified: 2010-05-01
I want to create a query in access database that will return all the records with the same values in Date and prod


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.
Question by:Aiysha
    LVL 19

    Expert Comment

    "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.

    LVL 32

    Accepted Solution

    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
     T1.MyId <> T2.MyId


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now