Results 1 to 11 of 11
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    Add invoice id when appending records

    I have a database I am creating where I create an invoice header in tbl_header that creates one invoice for each customer that has the billing period, accounting period, and a few others. Then i run a query that appends multiple records from a detail table to a working table based on the customer id.



    Then I have a form that shows the header info with the detail info in a subform that is matched up based on customer ID and billing period. However, I want to be able to add random invoices and using customer id and billing period wont work for that so I want to create an Invoice id to match them up.

    However, when i try to run an append query that adds in the invoice id from the header table to the detail records it is adding to the working table it ends up adding 6 times the number of records. I should be adding around 5,000 and it adds around 30,000.

    How can I create a process that will create a header record with an invoice ID for each customer and then create the detail records and assign the single invoice Id that the customer has to all the detail line items.

    Example
    Header creation
    Customer InvoiceID Billing Period Accounting period
    Customer 1 2223 2 1

    Detail creation
    CustomerID Invoice ID Car Price
    Customer 1 2223 25 $400
    Customer 1 2223 27 $300
    Customer 1 2223 30 $500
    Customer 1 2223 50 $450

    Any help is appreciated.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    We're going to need to see your code and queries.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Would be much better if you clicked on Go Advanced when posting and use a table, or create in Excel and dump here to automatically create a table.
    Wouldn't your InvoiceID be linked/related to your OrderID because it's not related to the Customer as you suggested?

    If you've added InvoiceID field to an existing table, you don't append, you update. Only reason I can think of for adding that many records is that there's more than one table in the query and there's no actual join (a Cartesian query) - that and the possibility you're using the wrong query type. Not sure I get the idea of adding just any invoice to such a header. I guess that the detail records are not for invoice payments and maybe not related to orders either.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Click image for larger version. 

Name:	HeaderCreation.PNG 
Views:	27 
Size:	50.6 KB 
ID:	46399

    Click image for larger version. 

Name:	DetailCreation.PNG 
Views:	27 
Size:	44.3 KB 
ID:	46400


    Here are the queries I have. The first query uses the form to set some values and creates one record per customer. The second query combines several tables to create the detail records. As you can see I have added the header table to try to match the Invoice Id by using customer ID but this is what is causing the duplicate records.

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    This query appears to properly match the InvoiceId in both tables. So Micron, you think the best thing to do is run a query to create the header records, then run a query to create the detail records, then run a third query to update the InvoiceID instead of trying to add the invoice ID to the append?

    Click image for larger version. 

Name:	MatchInvoiceID.PNG 
Views:	27 
Size:	35.9 KB 
ID:	46401

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    [delete... disregard]

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you post a zipped copy of your database for review? feel free to delete sensitive information

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To answer your question to me - I don't know what type of query you're referring to when you say to "run" it. I base forms and reports on select queries, not tables, but I don't think that's what you're referring to. I'm saying that if I interpreted your 1st post correctly, you added a field to a table so that you could enter linking data, and to fill those fields would require an update query, not an append query.

    I don't even think the CustomerID needs to be in the invoice table. If one goes up the chain to see what is linkd to what,
    - the invoice details table would contain the linking field to invoice ID
    - the invoice table would contain the linking field to the PO header table
    - the PO header would contain the linking field to the customer table.

    Then again, I'm no accountant. If this thread is not about how to fill in data to newly added fields (which as I said is an update query) then I'm missing the point.
    FWIW, I also think it's not a great idea to link a foreign key to a foreign key (AllRailCarData.CustomerID to InvoicingHeader.CustomerID) - makes me think you're using data values for primary (and maybe secondary) keys.

    Might be a good idea to post a zipped db copy as suggested.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    ok, I am going camping for the weekend. I will post a simplified database with the tables and queries when I get back.

    Thanks for the help so far.

  10. #10
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Hey guys, I did some work on it with and used Micron suggestion to do the update query. I run the append query and then the update query right after it and it appears to be working fine.
    Thanks for the info

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you got it solved.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Appending only records which have changed.
    By Miquel1 in forum Queries
    Replies: 3
    Last Post: 01-10-2014, 08:25 AM
  2. Appending records to a table
    By crowegreg in forum Programming
    Replies: 6
    Last Post: 08-13-2013, 09:54 AM
  3. Message before Appending Records
    By Lorlai in forum Programming
    Replies: 3
    Last Post: 03-19-2013, 04:51 PM
  4. Appending inventory records with current price records
    By sberti in forum Database Design
    Replies: 8
    Last Post: 11-29-2012, 10:24 PM
  5. Key violation when appending records
    By slaterino in forum Programming
    Replies: 10
    Last Post: 08-26-2010, 08:25 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