Hello All, I created a database

[warbuckle]

Hello All,

I created a database related to the courier business,
Tables are:
Drivers
Customers
Invoices
Waybills

The whole process starts with a "Waybill" data entry form I created..............everything works well. Heres my problem...

I wish to have an "Invoice" number generated to "correspond" with several "Waybill" numbers depending on a date qury I have created. The waybill numbers are autogenerated. The query works fine but how to assign a "single" invoice number to multiple waybill numbers depending on the start / finish dates?.........

With the least amount of hand-coding please......

TIA
William

 

[Malchik]

Hi,

You will need a new table containing two fields one for the invoice number and another one for the waybill number. Well actually it depends of the structure of your Invoices table. Usualy for invoicing you need two table, one to keep the basic info (invoice number, client info, taxes number, address...) and anotherone for the detail

Table: Invoice_Detail (Invoice_ID, Waybill_ID)

So you can have an insert query like (example)
INSERT INTO Invoice_Detail ( Invoice_ID, Waybill_ID )
SELECT "001" AS InvoiceNumber, Waybill.Waybill_ID
FROM Waybill
WHERE Waybill_Date Between #2003-04-01# And #2003-04-10#))
Mal'chik