Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    3

    Question Unanswered: Query to show new or deleted records between tables

    Hi Guys,

    I am a complete n00b so please bear with me

    I have an access database that is updated on a weekly basis

    Each Week has a new table

    (I have 4 periods of data)

    Week1 thru Week4

    In each one I have a list of faults with a common unique identification string (Alphanumeric)

    What I need to be able to do is to compare Week1 and Week2 for example

    I have 3 scenarios

    A fault doesn't get fixed and moves from Week1 to Week2

    A fault does get fixed in Week1 so doesn't show in Week2

    A new fault appears in Week2 that wasn't in Week1

    I have a basic knowledge of WQL and SQL, (Copy paste and modify a query is about my limit)

    Is this possible?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use queries with a JOIN operator:
    Quote Originally Posted by Iserlohner View Post
    A fault doesn't get fixed and moves from Week1 to Week2
    Same common unique identification string (Cuis) appears in both tables:
    Code:
    SELECT ...
    FROM Week1 INNER JOIN
         Week2 ON Week1.Cuis = Week2.Cuis
    Quote Originally Posted by Iserlohner View Post
    A fault does get fixed in Week1 so doesn't show in Week2;
    Common unique identification string (Cuis) appears in table1 but not in table2:
    Code:
    SELECT ...
    FROM Week1 LEFT JOIN 
         Week2 ON Week1.Cuis = Week2.Cuis
    WHERE Week2.Cuis IS NULL;
    Quote Originally Posted by Iserlohner View Post
    A new fault appears in Week2 that wasn't in Week1
    Common unique identification string (Cuis) appears in table2 but not in table1:
    Code:
    SELECT ...
    FROM Week1 RIGHT JOIN 
         Week2 ON Week1.Cuis = Week2.Cuis
    WHERE Week1.Cuis IS NULL;
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fix your design
    the data belongs in a single table NOT a table per week
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2014
    Posts
    3
    Quote Originally Posted by healdem View Post
    fix your design
    the data belongs in a single table NOT a table per week
    Yep I realised that but as it states at the top I'm a n00b and on a steep learning curve

  5. #5
    Join Date
    Jan 2014
    Posts
    3

    Update

    Well thanks for the advice, the simple queries didn't cut the mustard so I'm going to post what I have come up with so far

    For the New

    Code:
    SELECT ...
    FROM [Query2-Query 1 New Compare]
    WHERE (((IIf(([CUIS]=[Compare]),"old","new"))="new"))
    ORDER BY [Query2-Query 1New Compare].CUIS DESC;
    This relies on a separate query to combine both queries to run the comparison against

    Code:
    SELECT ... [Week 2 CUIS].Owned, [week 1 CUIS].CUIS AS Compare
    FROM [week 2 CUIS] LEFT JOIN [Week 1 CUIS] ON [Week 2 CUIS].CUIS = [Week 1 CUIS].CUIS;
    Still got to work out the missing records from Week 2 in comparison to week 1

    Cheers

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •