PDA

View Full Version : mult. record question


ABC123

Islip
08-06-2010, 11:56 AM
I have a question, I have an appliction in which I have form in which displays the vehicles assigned to employees. Now some employees are assigned more than one vehicle. In my form however it displays in the vehicle field it displays only the first vehicle. I want to have a second field as which lists the second vehicle.

DaveM
08-07-2010, 04:30 PM
two ways to do this depending.

you can add the field(s) for a second vehicle to your table and then place the field(s) on your form.

If in a browse ONLY you could just enter the person again and add the second vehicle. Yes the person would show up 2 times in the browse.

Peter.Greulich
08-07-2010, 06:26 PM
In addition to what Dave said, you can have two tables: employees & vehicles. Create a 1:M set w. employees as parent and vehicles as child. Include a emp_id in the child table and link on that. Now an employee can have one or more vehicles.

markmoss
08-08-2010, 12:19 AM
Islip


I would go with Peter's Design, but take it one step further.

1. EmployeeTable - EmployeeID ( Primary Key ) - Parent
2. VehicleTable - VehicleID ( Primary Key ) - Parent
3. EmployeeVehicleTable - ( EmployeeID + VehicleID ) ( Primary key ) - Child

This will allow you to not only have an Employee Assigned to many Vehicles, but Vehicle Assigned to many Employee's.

EmployeeTable

1 John Smith
2 Fred Jones
3 Barney Fifh

VehicleTable

1 Ford Pickup
2 Chevy Pickup
3 IH Backhoe
4 Chevy Dump

EmployeeVehicleTable

1 2 ( JohnSmith - Chevy Pickup )
1 3 ( JohnSmith - IH Backhoe )
2 2 ( FredJones - Chevy Pickup )
2 4 ( FredJones - Chevy Dump )
3 1 ( BarneyFifh - Ford Pickup )
3 3 ( BarneyFifh - IH Backhoe )
3 4 ( BarneyFifh - Chevy Dump )


Hope this helps

Mark Moss
:)

MikeC
08-11-2010, 04:54 AM
Of course these design suggestions, although most likely correct, do not account for the possible context of when the vehicles are what is being considered....then the vehicles would be the parent table with a one-to-many with employees. As the exact context wasn't stated explicitly I just wanted to throw that out there to illustrate how easy it can be to incorrectly assume things! :)