Populate multiple records with same data from another table

[Ksinay]

I have a table that will contain data for a delivery manifest that includes prescription number, employee enter, and delivery site. In tbl 1 I will include all these fields. In tbl 2 I want to have the date, employee enter and delivery site. I want to be able to enter tbl 2 information on a form once and apply those fields to tbl 1. The intent is to streamline the data entry process from having to enter in my form everything in tbl2 each time I enter a prescription number in tbl1 as there could be 30-40 entries of rx # per site-date and employee. Report output should look like this: Auburn North Pharmacy, Bob M. 1/20/2016 rx's: #1234, 1444, 5666, 7777, 1940, 4490. The delivery manifest will have the pharmacy name, date, and employee who entered and a list of the rx#'s. I am only intermediate ACCESS user so a lot of code probably would be over my head.

[ranman256]

run append or update queries will populate both tables.

[June7]

Why duplicating employee and delivery site data between tables? Why not just save table 1 record ID as foreign key in table 2 along with the date?

Use form/subform arrangement for data entry and the only data input will be the date.

EDIT: I might not understand the business process here. Are the prescriptions already entered into table 1 and in a later data entry session you need to enter delivery dates? This data entry is from a manifest that will have multiple deliveries for multiple prescriptions?

[Ksinay]

Thanks for the reply. The way it will work is the prescriptions are already processed. They are bagged and have a barcode that we scan. The employee scans a batch of prescriptions at a time into a tote for a specific site. Right now the person doing the scanning for each record has to enter the date, his name, and the site, then the prescription#. We want to associate that batch of rx's to the person scanning, date it was put in the tote, and the site it is going to. Those three elements will always be the same for each batch so we only want to enter those once. Hope that makes sense. It feels like I can create one table with the employee, site and date and for each batch in the other table, have them pre populate along with the scanned rx's

[Ksinay]

Will it append only to that particular batch? The records in the table each day will have a different delivery date and most likely a different employee scanning. If I did it this way, it wouldn't overwrite the previous days data?

[June7]

What does 'already processed' mean? Prescriptions are packaged but not yet entered into database? There is not yet a db record for that prescription number?

There are different ways to approach this data input - some don't require code some do.

Consider:

tblBatch
BatchID (autonumber primary key)
BatchDate
EmpID
DeliverySite

tblBatchDetails
BatchID (foreign key)
PrescNum

Form/subform arrangement for data entry. Main form bound to tblBatch, subform bound to tblBatchDetails. Input info into tblBatch form then enter PrescNum in subform and the BatchID will automatically save in subform record.

[Ksinay]

That is correct, there is no db record yet for that prescription number. The prescriptions are packaged and ready for the technician to scan the barcode on each prescription and placed into the delivery bin for a particular site. So for each batch the person doing the scanning will be the same, the site the same, and the date scanned will all be the same. I will try this!