Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare similar feild of two tables (Access2000)

    This is my problem:
    I have a table called Trade and another table called TradeAmendments
    All the new records are saved in tblTrade and the old records (records that have been amended) are saved in tblTradeAmendments.

    What I

  2. #2
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    What I want is an advanced filter where I want to pick up the original record and the amended record using [tnum] which is the same number for both records and then I want add [moneyspot] of the original record to the moneyspot of the duplicate record and if the result is 0 then I wanna pick that record. [moneyspot] is the total.

    How do I compare records????/
    PLEASE Help

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Compare similar feild of two tables (Access2000)

    Hi,
    The easiest way to do that is probably to take your query that retrieves the original and latest amended records, base a report on it, then apply a filter to the report. If you include a field in your query that subtracts one moneyspot value from the other, you can filter your report for records where that field equals 0.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    Isn't there anyway I could do this in a query coz I have to output these records to a spreadsheet to create a report??

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    I would do it with two queries:
    The first finds the latest amendment records
    SELECT TradeAmendments.Tnum, Max(TradeAmendments.Date) AS MaxOfDate
    FROM TradeAmendments
    GROUP BY TradeAmendments.Tnum;

    Then the second query lists those of the latest amendments where the values differ
    ELECT Trade.Tnum, Trade.moneyspot, TradeAmendments.moneyspot
    FROM (Trade INNER JOIN qryLatestAmendments ON Trade.Tnum = qryLatestAmendments.Tnum) INNER JOIN TradeAmendments ON (Trade.Tnum = TradeAmendments.Tnum) AND (qryLatestAmendments.MaxOfDate = TradeAmendments.Date)
    WHERE (((Trade.moneyspot)<>[TradeAmendments]![moneyspot]));

    The first query is called qryLatestAmendments

    I hope this achieves your objective.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    Thanx David,
    Im working on it to see whether it works.
    Thanx
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    Ok, this is the outcome of the above solution:
    I did what you told me to do & then I used the tnum to extract records from both Trade & TradeAmendments table and for some records it gets more than one copy of the Amended record, meaning it extracts more than one of the most recent old record.
    Any suggestion?

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    I am surprised, the first query should limit the rows extracted from the TradeAmendments tables to just one record for each Tnum, using the Max function.
    Is it possible for you to create a database with the two tables, with just a subset of records, and the two queries so I can have a look?

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Compare similar feild of two tables (Access2000)

    Hi,
    Do you ever have more than one amendment per day for a given tnum? David's code will return the latest date for any tnum, but if you can have more than one amendment per day, you may get multiple records returned.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    Yep, I do have more than one amendments some odd days, there must be a way to do this??

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Compare similar feild of two tables (Access2000)

    How would you determine which of the amendments carried out on the same day is actually the latest one?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    it finds the max, which is the most recent using a date & time field so it compares the time as well but only sometimes it returns 2 amended copies of the same record.
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Don't know why.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Compare similar feild of two tables (Access2000)

    To be honest, nor do I unless there are duplicate records in your database. If you're joining on tnum and latest datetime that really ought to be unique. Do you ever have 2 identical tnums in your main table or is that the primary key?
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare similar feild of two tables (Access2000)

    tnum is the primary key for Trade table but I would have the same tnum in the Trade Amendments Table coz the primary key for TradeAmendments table is amendnum.
    Come to think of it, I think I know where am going wrong, I have tnum in the query where Im finding the max of Amended and that might have caused it to return both records with same Tnum.
    <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> THANX Rory <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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