Update Query Nightmare

[Philmore]

I have a database that generates customer transactions history but no invoice no is generated for the transaction. I need to generate invoice numbers for each line item to produce invoices for the customers problem being a customer can have multiple items per invoice. How can I update the table with numbers making sure that all customer transactions for each day has the same invoice number.

 

[AncientOne]

This depends on the structure of your db. Are invoices held in a table which is on the many side of a relationship with a Customer table, or are customer details and invoices all together in one table?


And do you want to have numbering as a permanent feature or do you want to update regularly?


Initially, a code routine which steps through your dataset and looks at the rows comparing customer number and date would be the easy solution. Ultimately, having a form and subform for entering invoice items is the answer.

I need furtherdetails about your tables to be more specific.

 

[Philmore]

I have the customer info in one table and the invoice details in another table. THe option I'm looking for is code to look at the data in the table and generate an invoice number for each set of customer transactions. Numbering of the invoice would normally be done in the application the transaction was generated however this application was not designed to do this so I have create invoices for the transactions outside of the source application.

 

[AncientOne]

In the invoice table, what identifies an item as belonging to a specific customer and what links items together in a particular invoice? pehaps if you post the table structure, a better idea of the best approach can be gained.

 

[Philmore]

tblInvoice
TransactionDate
Locationid
Quantity
Price
Location
Customer ID
Sales Representative ID
Contact
Address1
Address2
Address3
Discount Price
Sale
Gct
Extended Price
Card Type
ItemCode
TransNo
InvoiceDate
Number of Distributions


tblCustomer
DigiID
Sales Representative ID
Customer ID
CWID
Customer Name
address1
address2
address3
fax number
merchantid
telephone
mobile number
email
fname
lname
Customer Since Date

There is no relationship between the Customer table and the invoice table. There is a reason for this as I said the data used to generate the invoice is collected from a remote SQL Server. I now need to generate the invoice based on the number of transactions I get in a batch for each customer. Currently I group all the transaction by customer export it to excel increment based on the last invoice number generated import this back to access and use a update query to add the value to each transaction with a matching customer id. I want to be able to do the excel process in access.