Relational Portals

[agaperrk]

I am creating a database to keep track of three different things, I am not sure how to best set them up.

Trucks

Drivers

Trailers

I have developed a data file for each and after I have created them I feel there would be a better way

So I am going back and creating tables for each.

Now the question, I would like to create portals to assign the truck a driver and trailer.

I need to be able to unasign the driver from the truck and trailer visa versa.

Basically I need to keep track who is driving which truck and trailer it could be different each month.

I do not know which way would be the best to set this up. I am trying to avoid redoing data files.

Thanks in advance for the help.

Edited June 13, 2008 by Guest

[theOx26]

Randy,

You were very right to switch to tables in one file. That is always the best way to do a simple setup like this.

Now, to be able to help with the best method for your system, we'll need a little more information.

1. Roughly how many trucks, drivers and trailers do you have to work with?

2. how often do trucks change trailers and drivers change trucks.

3. Is it safe to assume that the truck is connected to the trailer and the truck to a driver? (obviously from a Database perspective, I understand that a driver needs a truck to pull the trailer)

4. How many different people are going to be using this program, just you or more?

Sorry to respond with questions, but for a best case, they really do matter. Hopefully with that, someone else may chime it.

[agaperrk]

There are 60 Trucks and about 70 drivers each table keeps track of different things 80 different Trailers.

Trailers can change daily trucks monthly drivers monthly, Sometimes weekely.

You are right Trucks need to be assigned a trailer and driver.

Up to 5 Users are possible. But Most of the time 1 Because of the motion of data is routed through one person.

Edited June 13, 2008 by Guest

[theOx26]

Ok, well the first thing to remember is that a portal is more for showing multiple related records. Unless you work like Fedex, I'm going to assume you only have one truck hooked to one trailer and one driver at any given point in time.

The best way to show this is to make a relationship between the tables that will be a truck number to truck number in each table. I won't into it too much but you can play with it to see more.

Then with that relationship, you can have one layout based in drivers that can also have a field from trucks and a field from trailers. YOu can use a value list based on the truck numbers and another based on the trailer numbers. Your numbers are small enough that that will works pretty easily.

It's up to you whether you work from driver or truck standpoint, but since both connect to a truck, I would do it from the truck standpoint and have reports that show from the other views.

So basically, value lists and relations based on numbers to pull in the data make the most sense to me.

If some of that isn't too clear, let me know.

[comment]

You need (at least) one more table of Assignments. In its most basic form, the table would have fields for TruckID, DriverID, TrailerID, StartDate and EndDate, and it would be related to the other three tables. This will enable you to tell who is (or was) driving what and when.