Results 1 to 5 of 5
  1. #1
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48

    data comparison within same table

    Hi,


    I have a table that contains records with following structure:
    ID, VendorId, ProjectID, ProductID, creationDate, Amount.

    I would like to create a query that lists all of the combinations of Vendor, Project and ProductID that created in the previous month, and a field that shows whether the same combination exist in the current month.
    Eg.
    I have a record like this
    1, Vendor1, Project1, Product1, 2019-06-12, 100

    Here I would like to have a field in the same row that shows, if a record with Vendor1, Project1, Product1 exist in July.

    I could create two separate tables for the 2 months, but i assume, there other solution.
    Thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you should not need to store the value in the table

    your query might look something like

    SELECT *, dcount("*","myTable","VendorId='" & VendorID & "' AND ProjectID='" & ProjectID & "' AND ProductID ='" & ProductID & "'") AS ExistsPriorMonth
    FROM myTable
    WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" )
    Your use of text ID's concerns me - ID's are normally numeric, so wondering if you are using lookups in your table design - in which case remove the single quotes from the dcount criteria

  3. #3
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Looks very promising, thanks a lot.
    I never used Dcount, I just made some search on it, but it is not absolutely clear what is the difference count and dcount.
    Can you help?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    count is a sql term and used to count rows in a query

    dcount is a vba function which access sql can use

    there are other ways to do it using just sql

    However just realised the dcount criteria is incomplete (did not include a date element) so this is the corrected one

    Code:
    SELECT *, dcount("*","myTable","VendorId='" & VendorID & "' AND ProjectID='" & ProjectID & "' AND ProductID ='" & ProductID & "' AND format(creationdate,'yyyymm")='" & format(dateadd('m',-1,date),'yyyymm') & "'") AS ExistsPriorMonth
    FROM myTable
    WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" )
    and this is one just using sql

    Code:
    SELECT *, Exists(SELECT * FROM myTable X WHERE VendorId='" & VendorID & "' AND ProjectID='" & ProjectID & "' AND ProductID ='" & ProductID & "' AND format(creationdate,'yyyymm")='" & format(dateadd('m',-1,date),'yyyymm') & "'" ) AS ExistsPriorMonth
    FROM myTable
    WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" )
    or another way
    Code:
    SELECT DISTINCT A.*, iif(isnull B.ID,True,False) AS ExistsPriorMonth
    FROM myTable A LEFT JOIN myTable B ON A.VendorId=B.VendorID  AND A.ProjectID=B.ProjectID AND A.ProductID = B.ProductID
    WHERE format(creationdate,"yyyymm")=format(date,"yyyymm" ) AND format(B.creationdate,'yyyymm")= format(dateadd('m',-1,date),'yyyymm')
    note these are all 'air code', you will need to correct for table and field names and perhaps typo's on my part


  5. #5
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    thanks a lot, you made my day.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Chart for annual comparison of data by months
    By shindb81 in forum Access
    Replies: 4
    Last Post: 05-21-2014, 12:48 AM
  2. data comparison query help
    By aselm01 in forum Queries
    Replies: 10
    Last Post: 09-12-2013, 03:29 PM
  3. Table creation for comparison
    By Zealotwraith in forum Access
    Replies: 11
    Last Post: 09-03-2013, 06:41 AM
  4. Table field comparison
    By shabar in forum Queries
    Replies: 3
    Last Post: 01-31-2013, 02:09 PM
  5. Month wise two years data comparison
    By waqas in forum Reports
    Replies: 2
    Last Post: 07-06-2012, 08:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums