Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Append or Update Query (2000)

    I'm lost. I'm trying to either update/append one table with data from another and nothing I try works. I need help with the set up of this query. Here's the data.

    tblPayments has a field called PaymentID. tblPaymentAllocations has a field call PaymentLink. tblPaymentAllocations.PaymentLink is presently blank (due to reworking an old database and this is a new field). It needs to have the same data that is presently in tblPayments.PaymentID. How do I accomplish this via query vs hand type 1300 +numbers!

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append or Update Query (2000)

    You need some way to match records in tblPaymentAllocations with records in tblPayments. What field or combination of fields can be used for that purpose?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append or Update Query (2000)

    Hi Hans,

    This is the hard part. These two tables are split from a larger table that was in the original database = tblTimesheet. They are linked via tblPaymentAllocations.PaymentLink looking back to tblPayment.sPaymentID, but there is nothing that is the same in the two tables as in data. I'm wondering if I could use tblTimesheet in the query to do this. tblPayments.PayID is joined to tblTimesheet.Autonumber. This number is the same as tblPayments.PayID

    Alicia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append or Update Query (2000)

    Have the data in tblPayments and tblPaymentAllocations been modified since they were split off from tblTimesheet? If not, it would be easier to populate the tables anew (using queries) and set the link field while doing so. If they have been modified, and you don't have a field in tblPaymentAllocations corresponding to AutoNumber / PayID, you have a problem.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append or Update Query (2000)

    No, the data hasn't been modified at all. So, how do I populate the tables using queries at this point? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks,
    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append or Update Query (2000)

    That depends on the structure of tblTimesheet and the way it is related to the other two tables, but it can probably be done as follows. Start by making a backup copy of the database, so that you can experiment freely.
    1. <LI>Delete all existing records from tblPayments and tblPaymentAllocations.
      <LI>Open tblPaymentAllocations in design view and add a field PayID if it doesn't exist yet.
      <LI>Create a new query in design view based on tblTimesheet.
      <LI>Select Query | Append Query...
      <LI>Specify tblPayments as target.
      <LI>Add the fields that should go into tblPayments to the query grid.
      <LI>If necessary, specify which field should be appended to which field. If the field names are equal, Access will propose the match automatically; you will have to match AutoNumber to PayID yourself.
      <LI>Execute the query.
      <LI>Repeat steps 3 through 8, but this time with tblPaymentAllocations as target. Make sure that you have AutoNumber in this query too, with PayID as target.
    You should now have two populated tables, with PayID as common field.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append or Update Query (2000)

    Thanks Hans, I'll give it a shot! And, I've already got a backup tee hee. Learned the hard way a long time ago.

    Leesha

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append or Update Query (2000)

    I'm confused. If I delete all existing records in each of the tables as you directed me to, how do I get the data from the fields other than PayID back?

    Alicia

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append or Update Query (2000)

    I hope you didn't erase the records in tblTimeSheet. (and if you did, you still have the backup <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    In step 6 in my previous reply:
    <hr>Add the fields that should go into tblPayments to the query grid.<hr>
    Not just PayID, but the other fields too! Or have I misunderstood you? You wrote
    <hr>These two tables are split from a larger table that was in the original database = tblTimesheet<hr>
    I took this to mean that the data you need is available in tblTimesheet.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append or Update Query (2000)

    I didn't delete anything till I heard from you. I need to clarify. After tblPayment and tblPaymentAllocations were split from or created from tblTimesheet, I deleted the duplicated fields in tblTimesheet as they weren't needed. I do have the original database so I can go back to that tblTimesheet if need be.

    Leesha

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append or Update Query (2000)

    Yes, you'll need to get tblTimesheet from the original table.

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Append or Update Query (2000)

    Back again,

    I get the idea of what you've advised me to do but before I can do it I need to basically duplicate the data the is in the automumber field in tbltimesheet. What I need is to have the field called Paymentlink have identical info as the info that is in Autonumber. PaymentLink did not originally exist in tbltimesheets. When I add the field it is blank which is what I'd expect. How do I get duplicate info?

    Thanks,
    Leesha

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append or Update Query (2000)

    Hello Leesha,

    There is no need to duplicate information in tblTimesheets. When you create the append query that will add records to tblPaymentAllocations, you can specify that the AutoNumber field in tblTimesheets will be appended to the PaymentLink field in the target tblPaymentAllocations.

Posting Permissions

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