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

    Mail-merge Problem (Access 2000)

    I have two tables, one contains original data and the other one contains the updated version of the original data, if there is any. Both of these tables have similar fields. Amendnum is the primary key for the amendment table. Tnum links both tables, which is the primary key for the original table. I have extracted these records using a union query and am linking it to a mail-merged document using a select query.

    What I want to do is when I have amendment records, I want to display only the updated version of the record on the merged document (amendment record is the updated version of the original record). I might have more than 1 amended record for one original record. I need to find a way to find the most recent record.

    Any one have any suggestions?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail-merge Problem (Access 2000)

    Maybe I am not understanding this correctly, but wouldn't it be easier to have the object that you use for the Mail Merge contain only the fields that you want to merge, rather than trying to make that determination later? Maybe a temporary table ... Write the amended fields or the original fields (whichever is appropriate for each record) into the table with an append query and just use that table for the mail merge. You can clean it out after each merge.
    Do you have a date that the amended record was written, so that you can find the most recent one?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail-merge Problem (Access 2000)

    First you need a group by query for the amendments. Group by the foreign key that links the amendments to the other table and use Max in the totals line to return the maximum date for amendments to that record. Here's a sample using the NorthWinds Orders table:

    SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
    FROM Orders
    GROUP BY Orders.CustomerID;


    The create a query on the main table to select all the records that don't have a matching record in the other amendments table. Here's a sample that selects all Customers from Northwind who don't have an order in the Orders table.

    SELECT Customers.CustomerID, Customers.CompanyName
    FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    WHERE (((Orders.CustomerID) Is Null));


    Then build your union query on the other two queries and you should have only the primary records that haven't been amended, plus all the latest amendments.
    Charlotte

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

    Re: Mail-merge Problem (Access 2000)

    Thank you for the reply Charlotte,
    I've done it the way you told me to do, part of it is working. Its not displaying the original record when there is an amendment record, but when there are two of amendment for the amendment, it's displaying both of these records in the end query. Why is this?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail-merge Problem (Access 2000)

    Are you talking about an amendment to an amendment or just two amendments to the same primary record? If the former, the structure you described didn't allow for that, and if the latter, it's something in the way you built the query. Without more information, it's impossible for me to tell, but I would suspect the query. Post the SQL of the query so we can see exactly how you're grouping the values.
    Charlotte

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

    Re: Mail-merge Problem (Access 2000)

    Amendment made to the amendment which will be stored in the same amendment table.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail-merge Problem (Access 2000)

    Princess, the keys are the important thing. You didn't say anything about amendments to amendments. How are you keying them? You have to group by the primary record key to get the latest amendment, but if you have stray records in there amending the amendment, then what are you using as the key for that record? If it is keyed on the amendment it modified, then yes, you will get more than one amendment for the same primary key and you'll have to change your query. No one can guess at what you've created based on minimal description alone. If you want help, you'll have to post the SQL.
    Charlotte

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

    Re: Mail-merge Problem (Access 2000)

    Original Data table uses Tnum as its primary key which both the tables share and the amendments table has its own primary key which is called Amendnum, I have more than 1 query.

    QRY1: Select ORIGINAL records
    SELECT Null AS amendnum, Trade.tnum, Trade.id, Trade.cpty, Trade.clr2, Trade.tt, Trade.td, Trade.settles, Trade.tccy, Trade.q, Trade.tc, Trade.tp, Trade.tai, Trade.tax, Trade.moneyspot, Trade.ctc, Trade.notes, Trade.cancel, Trade.fund
    FROM Trade LEFT JOIN TradeAmendments ON Trade.tnum = TradeAmendments.tnum
    WHERE (((TradeAmendments.tnum) Is Null));

    QRY2: Select Amendment records
    SELECT TradeAmendments.amendnum, TradeAmendments.tnum, Max(TradeAmendments.amended) AS MaxOfamended, TradeAmendments.id, TradeAmendments.cpty, TradeAmendments.clr2, TradeAmendments.tt, TradeAmendments.td, TradeAmendments.settles, TradeAmendments.tccy, TradeAmendments.q, TradeAmendments.tc, TradeAmendments.tp, TradeAmendments.tai, TradeAmendments.tax, TradeAmendments.moneyspot, TradeAmendments.ctc, TradeAmendments.notes, TradeAmendments.cancel, TradeAmendments.fund
    FROM TradeAmendments
    GROUP BY TradeAmendments.amendnum, TradeAmendments.tnum, TradeAmendments.id, TradeAmendments.cpty, TradeAmendments.clr2, TradeAmendments.tt, TradeAmendments.td, TradeAmendments.settles, TradeAmendments.tccy, TradeAmendments.q, TradeAmendments.tc, TradeAmendments.tp, TradeAmendments.tai, TradeAmendments.tax, TradeAmendments.moneyspot, TradeAmendments.ctc, TradeAmendments.notes, TradeAmendments.cancel, TradeAmendments.fund;

    UNION QRY:
    SELECT amendnum, tnum, id, cpty, clr2, tt, td, settles, tccy, q, tc, tp, tai, tax, moneyspot, ctc, notes, cancel, fund
    FROM XClassicTrade UNION SELECT amendnum, tnum, id, cpty, clr2, tt, td, settles, tccy, q, tc, tp, tai, tax, moneyspot, ctc, notes, cancel, fund FROM XClassicAmend;

    Thanks for helping Charlotte, I don't know what I'll do without this forum.
    Im STILL stuck on this one, PLEASE someone help me............. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail-merge Problem (Access 2000)

    Why are you using the amendnum field in your queries? If amendnum is unique for each record, then the groupby in query 2 doesn't do what you want it to do. If you drop amendnum from all your queries, then it should work.
    Charlotte

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

    Re: Mail-merge Problem (Access 2000)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> YAHOOOOOOOOOOOOOO!
    <img src=/S/megashout.gif border=0 alt=megashout width=33 height=17> It works! It works, It works. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Thanx alot Charlotte. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    <img src=/S/salute.gif border=0 alt=salute width=15 height=20> <img src=/S/trophy.gif border=0 alt=trophy 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
  •