Need two fields to hold data

[rioredpony]

I have two fields--WeekEnding and DriverName.
What I want to do is store the data entered in these fields until all of the data is entered for a specific driver during a specific week. Can anyone help?
Thanks

 

[David R]

Huh? As in every record will have the same WeekEnding value and same DriverName value until the week is finished?
Multiple records storing the same field values sounds like an unnormalized database. You could probably store all your driver information in one table and all your daily logs in a separate table, connected one:many.

Post back or search the archives if you need more information on 'database normalization'.

 

[rioredpony]

The purpose of the form is so that the user
does not have to repeatedly enter the driver and weekending for each record of that driver and that week. There will be multiple entries for each driver on a particular week. The other fields are state, miles, and gallons. Hope this helps.

 

[David R]

Consider this structure (note: PK stands for Primary Key):

tblDrivers: PK field (DriverID), Driver Name, Vehicle (possibly linked to a tblVehicles), Address, PayGrade, whatever. Everything specific to the driver.

tblUsage: PK field (Autonumber, just to identify records in this table), DriverID (as a Long Integer field, not an Autonumber), StateID (possibly linked to a reference table of States), Miles, and Gallons. (Possibly [DateTraveled], see below).

Now go into Tools>Relationships, add your two tables to the view, and draw a line between the two DriverID fields. You should get prompted to make a One-to-Many relationship. Referential Integrity and Cascade Update/Delete are things you should check. You should end up seeing a "1" over one side of the link and an infinity symbol over the other.

Now make your form over. Put the driver-specific information on the mainform, and leave a spot at the bottom for a continuous subform. Design a subform for the fields in tblUsage, but you don't have to 'show' DriverID or the PK of this table. They do need to be in the record source). In design view for your main form, Insert subform and make sure it says to show all records where DriverID matches. The wizards are very helpful in all of this, use them!

Now you should have a more usable format where when you go into a driver's record, you can go to the subform and enter new days' travel for them without having to duplicate all the data.

As far as [WeekEnding] goes, that depends on how you're storing it. but I would store (in tblUsage) the date of travel if you're entering by days, or the week ending date if each entry represents a workweek. Look at this topic for a possible way to help you manage this: http://www.access-programmers.co.uk/ubb/Forum7/HTML/003321.html

Good luck,
David R

 

[dungstar]

Is there a way to do this (retain the information provided in the fields when adding records) without using subforms?

The database I'm creating. Lab technicians place lab samples (each with its own ID) into multiple locations. Each sample contains a lot of data. Often times when multiple samples is put in the database, a lot or all of the data (with exception of the PKey) of the sample will be the same.

Thanks

 

[rioredpony]

Thanks. I'll try that and let you know. I really appreciate your help!!!