Adding new records to table based on values in another table

[draftsman]

Hello,

I'm developing a database for the hospital I work in. One purpose of this is to keep track of patients with temporary invasive devices (there are many types such as urinary catheters, ventilators, etc.), specifically how many days each patient has a device for. Each device is associated with one patient only, but one patient may have many devices. Here's how the associated tables are set up:

1. tblPatients - PatientID (PK), LastName, FirstName, DOB,...
2. tblLocations - LocationID (PK), LocationLabel,....
3. tblDevices - DeviceID (PK), DeviceType, Device, DeviceDesc


4. tblDeviceUse - DeviceUseID (PK), DeviceID (FK), PatientID (FK), LocationID (FK) (where in the hospital was device inserted, e.g., operating room, bedside, etc.), DeviceStartDate, DeviceEndDate
5. tblDeviceDailyLog - DeviceLogID (PK), DeviceUseID (FK), DeviceDate, PatientLocID (FK) (area in the hospital that patient is in)

All primary keys except for PatientID & LocationID are Autonumbers; and the tables are linked appropriately.

Whenever an entry is made into tblDeviceUse, I want there to be new records to be automatically generated in tblDeviceDailyLog for each date between the Start and End Dates. For example, patient A123 had a urinary catheter from 1/1/2000 to 1/10/2000 that was inserted while the patient was in the ICU, but the patient was moved to the Medical Ward on 1/7/2000. So tblDeviceDailyLog should have 10 new records associated with this device, one for each calendar day, with the appropriate location for each day.

What method and steps should I take - VBA, macro, or append query? I'm new to all 3 types, so examples of syntax would be helpful. Many thanks.

[orange]

Can you not determine what you need knowing the InsertDate and RemovalDate?

Analogy, you start vacation on Jun 2 and you end vacation Jun 10.
Your status as of Jun 5 is OnVacation after Jun 10 status is NotOnVacation.

Seems to be the same concept. Please clarify if I have misunderstood.

My advice - to a lot of posters - and to you with 1 post:

Get your business facts defined.
Identify some of the information you need to get from the proposed database (questions/reports etc) (it has to be in the database or calculated from info in the database to get it out)
Create a entity relationship diagram/data model
Take some sample representative data and test the model

A good tutorial re tables and relationships. You have to work through(30-60 minutes) it but you will learn a technique that can be used with any database.

You may get some ideas from this also.

I don't use macros. I've been using vba and SQL for a long time.
Understand database before you get too deep into Access.

Good luck.

[draftsman]

Can you not determine what you need knowing the InsertDate and RemovalDate?

Analogy, you start vacation on Jun 2 and you end vacation Jun 10. Your status as of Jun 5 is OnVacation after Jun 10 status is NotOnVacation. Seems to be the same concept. Please clarify if I have misunderstood.

That is part of what I want. But my intention with this is not to just look up patient & device status on any given day, but to be able to see a daily summary of which device, if any, the patient has. More importantly, this is being done as part of a broader daily tracking of patient information, such as vital signs, laboratory tests, etc., that may change from day to day, for analysis. Please see the attachment for context.

That Word table is what I want to replicate in an Access form (with several subforms). The top row of fields in the table is information that is basically one-to-one for each patient visit, but the bottom 5 rows have a lot of one-to-many data relationships with either each patient or each visit. The idea I had is that the form should generate one row for each date of the visit based on the admission date and the discharge date rather than a preset number of rows for each day of the patient's stay, while Access fills in the information about the from "tblDeviceDailyLog". Example: if a patient was admitted 1/1/2015, had a "Vent" from 1/1/2015 to 1/6/2015, and was discharged on 1/8/2015, then the form should have 8 rows, with only the first 6 days having a checkmark in the "Vent" field.

[orange]

I think you are in spreadsheet mode --not database.
You can record StartDate and EndDate and deduce/calculate info.
You do not need a record for every day in order to produce a Daily summary, in my view.