Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Multiple Append querys

    I have an invoicing system currently where by all information is held on one table. In a test area I have no separated this information so that information specific to the invoice is in one table named "invoice entries" and information relating o each line on the invoice is in another table named "invoice lines".



    Previously the user would select the order, then just add quantities on the line they wish to invoice. All of this information would be held together as I said, If there were multiple lines a lot of this information would have been duplicated.

    Now, with two new tables I want the invoice details to be appended in the invoice entries table and the Line information to be in the invoice lines table. These tables are linked by the primary key "inv_ID"

    obviously the ID is generated ON IMPUT, so how would I then use this ID to put in the Invoice lines table?

    I hope this makes sense. Feel free to ask questions if I'm not clear.

    Thanks.

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I'm after any suggestions how to achieve this, or ideas.

    I was thinking that the Invoice entry table would have to be made first. Then the query to append the line information could use the last ID from that table. I don't know if this is the best way.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need to know the value of the primary key of the invoice header table in order to put that value on to the invoice lines table. My first question therefore is - is this an existing field (are you taking it from the existing invoice table) or are you creating new invoice ID's (a new table with a new autonumber PK)? And, is order a synonym for invoice?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    You have currently a table with structure something like
    tblCurrentInvoices: CurrentID, InvoiceNo, InvoiceDate, ..., ArticleNo, ArticleQty, ArticlePrice, Amount

    and you want to have 2 tables with structure like
    tblNewInvoices: NewInvoiceID, InvoiceNo, InvoiceDate, ...
    tblNewInvoiceRows: NewInvoiceRowID, NewInvoiceID, ArticleNo, ArticleQty, ArticlePrice, Amount
    I assume NewInvoiceID and NewInvoiceRowID are autonumeric Primary Keys. CurrentID may be whatever.

    Keep old file intact until you are sure all went well. Create structures for both new files, but add a column for InvoiceNo into tblNewInvoiceRows too.

    Use INSERT query to add invoice info into tblNewInvoices - something like

    Code:
    INSERT INTO tblNewInvoices (InvoiceNo, InvoiceDate, ...)
    SELECT DISTINCT InvoiceNo, InvoiceDate, ...
    FROM tblCurrentInvoices
    (the primary key is filled automatically)

    Use INSERT query to add invoice rows info into tblNewInvoiceRows - something like

    Code:
    INSERT INTO tblNewInvoiceRows (InvoiceNo, ArticleNo, ArticleQty, ArticlePrice, Amount)
    SELECT InvoiceNo, ArticleNo, ArticleQty, ArticlePrice, Amount
    FROM tblCurrentInvoices
    Use Update query to get NewInvoiceID from tblNewInvoices into tblNewInvoiceRows

    Code:
    UPDATE tblNewInvoiceRows INNER JOIN tblNewInvoices ON tblNewInvoices.InvoiceNo = tblNewInvoiceRows.InvoiceNo
    SET tblNewInvoiceRows.NewInvoiceID = tblNewInvoices.NewInvoiceID
    When done, you can remove field InvoiceNo from table tblNewInvoiceRows

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I have converted the data that is already within the database so it works with the new table setup. Its just a concern for new data input when the parent/child are essentially created at the same time.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Avril,

    I have saved the front and back end for the system we have been using in a backup just incase I screw this up. Let me try to explain better.

    for information already in the database I have no issue. Its just new information.

    I add the new invoice details such as the date/notes/whatever. I also add line/row information in the same form. The problem is I have one form with a lot of information that needs to go to two separate tables at the same time and needs to be related.

    typically I have always used auto number, but I could put in a process with querys to increment the number that relates the two when adding the new information. It just seems messy.
    I'm inserting the new invoice

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The title of this thread is append queries - if those are working, what is your next question? Access is a single-threaded process, only one thing can happen at a time.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    They are not working, I wish to append data from one form into two tables and have a parent/child relationship established at the same time. The older data is correct but I did this manually using excel.

    I'm only concerned right now with new data input. Sorry for the confusion.

    I can change the design to suit any suggestion if it seems like I'm doing it wrong.

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Just to verify the sequence of events:
    - user selects order
    - user either selects an existing invoice number or creates a new invoice
    - user enters invoice lines (is this matched up with the original order lines?)

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    How do you insert data. Do you use form-subform with links defined between them, or do you enter new data directly into tables?

    When you use form to enter invoices, and subform to enter invoice rows, then post here subform LinkMasterFields and LinkChildFields properties, and the structure of both tables, or at least linked field names. In case the link to parent form is using form control(s), then also info about this control and how it is linked with parent table.

    When you use tables to enter of data, then I'm afraid you are out of luck!

  11. #11
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Aytee, Yes all the orders exist in the system already. we invoice against existing orders/lines. This screenshot shows the relationships between everything concerned here.

    Click image for larger version. 

Name:	Order and Invoice tables.png 
Views:	18 
Size:	33.5 KB 
ID:	30922

    Avril, The data displayed on the form is from table "order lines" and "order table". I am using a the "temp_quantity" in "order Lines" to populate which lines we want to invoice. (this is cleared on form open and close). Once that data is entered on the form. I eed a new entry in the "invoice Entries" as a record for this specific invoice I am creating. Then I also need each line to have a related entry in the "invoice lines" table.

    I'm comfortable with all of this, I just don't know how to make the relationship between the NEW invoice entries and NEW line entries. Because they are both created at the same time. I do struggle with being clear, hopefully we are getting somewhere.

    thanks for the replies.

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Main form, record source Invoice_Entries:
    - user selects order number, combobox
    - user selects existing invoice number from a combobox of existing invoices for that order number
    OR
    - user says New Invoice, create new record

    Subform (tabular), record source Invoice_Lines:
    - this is linked to the main form with Inv_ID
    - first field is a combobox of lines from Order_Lines table, user selects which line to invoice and enters rest of info

    Remove temp quantity.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    OK. In our ERP system orders and invoices aren't linked at row level at all, as the order may be p.e. for long preiod, and the production is sent out on weekly shedule - i.e. there may be a lot of invoices for same order. Of course there is a field in invoices table with order number this invoice belongs to, but this is all of it. Invoice rows are entered for every invoice manually, and the number of rows and the list of invoiced items can be different for every invoice linked to same order.

    For your case maybe this will work:
    Add Order_ID into table Invoice_Entries, and add a boolean field to.

    User creates a new invoice in Invoices form (based on table Invoice_Entries), and fills it. Then the invoice is linked with order (Order_ID is selected in combo). When the invoice is linked, a button will be made visible to import order rows. Clicking on button starts a procedure, which inserts data from Order_Lines table into Invoice_lines table. After data are imported, the boolean field is set to TRUE (the default is FALSE) and the button will be hidden.

    In OnCurrent event of invoices form, you have to check for status of boolean field and Order_ID. When boolean field is FALSE and Order_ID is not empty, the button for import will be visible, otherwise not visible. And probably you want to set the visibility of Invoice rows subform too.

  14. #14
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hi Both,

    I appreciate your comments and ideas. I think I understand, Ill have a crack this morning and find out!

  15. #15
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Just wanted to thank you both. I may have a solution slightly different to the recommended, but Id have nothing without your advice and suggestions. Thanks a lot.

    I have the new invoice open on the invoice form. that form has a load event to go to last record which is created on the same button that opens the form. Under that I have the order lines table in a subform to show any potential lines for the invoice. I am still using the temp quantity on each of these lines which clears when this form is opened. this info is then appended to the invoice lines table using the invoice ID in the main form to link them.

    It also obviously has the line ID. this isn't actually linked in the relationships but it can be via a query to show information.

    Its working anyway and I'm happy with it, but if you want to add anything then feel free. Right now I'm just tidying up the process and the forms.

    Thanks again.

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

Similar Threads

  1. Replies: 19
    Last Post: 10-03-2017, 09:10 AM
  2. Mail Merge multiple querys
    By Homegrownandy in forum Queries
    Replies: 1
    Last Post: 10-16-2015, 04:41 AM
  3. Join 2 Querys
    By afslaughter in forum Queries
    Replies: 4
    Last Post: 12-07-2011, 02:59 PM
  4. Where are my Querys?
    By tamu46 in forum Access
    Replies: 7
    Last Post: 12-04-2010, 03:27 AM
  5. Understanding SQL Querys
    By jacobbiljo in forum Access
    Replies: 8
    Last Post: 11-17-2009, 05:17 PM

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