What would be the best way to....

[KiwiLee]

Hi All from Downunder, This is my first post.....

I have an Access program we use to record batch numbers of the product we produce. We now want to relate the batch numbers to the Clients invoice number.

Preferably the invoice column would Auto number the rows. We could enter the batch number or several batch numbers in the fields/columns across the row. It could be up to several batch numbers per invoice.

If we needed a report to relate a batch number to an invoice, we would like the several columns of possible batch numbers to relate to the invoice number. i.e.

Invoice No: Batch 1 Batch 2 Batch 3 Batch 4 Batch 5 etc.
15556 110113 110213 130125 121213 130731
15557 130731 130917
15558 130125 120430 110113

I would like to see a report as a result say;
REPORT RESULTS:
Batch No's INVOICES
110113 15556
15558
130731 15556
15557

I guess it is easy, but this amateur just can't figure it out.... Many thanks in advance.

 

[Galaxiom]

Do not use multiple fields to store the batch numbers. Use a separate record for each Invoice/Batch combination.

The junction table will have two fields. InvoiceNumber and BatchNumber. The relationship type between Invoice and Batch is Many-to-many.

 

[KiwiLee]

Thank you,

I was trying to avoid keying in the invoice number every time, hence using autonumber.... Did you mean enter the invoice number in every time.

 

[Galaxiom]

You wouldn't be able to use autonumber because the invoice number will be used multiple times.

How is the data currently stored? Maybe it can be imported.

 

[KiwiLee]

This is a new table I am trying to sort out. There is no data to import as we are creating the tracking record.

I had wondered about inserting rows in the autonumber fields, but that does not appear to be a good option. I guess at the end of the day we can key it in, but just trying to save some work.

Many thanks,,

 

[Galaxiom]

The InvoiceBatch junction table would be the basis of the RecordSource for a subform in Datasheet or Continuous Forms mode on your Invoice form.

Only the control bound to the Batch field would need to be showing on this subform.

The Invoice field in the subform records would be automatically entered by the Master/Child Link Fields property of the subformcontrol on the Invoice forrm so it won't need to be typed.

Ideally the Batch would be shown as a combobox so the operator could select the batch rather than type it. With some fancy querying the rowsource of the combo would only show batches that are available for selection.

 

[KiwiLee]

Hey, Many thanks, I will give that a go....

Really appreciate your time in answering the question..