Table Relationship

[BillT]

Hi:

Here's the scenario: (first database)

I'm trying to create a database for Forklifts and how
their batteries are used. (charged)

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck.

2. After inputting the dead battery#, the program should
display what fresh battery is available for that
particular truck.

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries.

My problem: There are two batteries for each truck. I am
unable to link my battery table to the truck table because
there are twice as many records in the battery table .
Truck#'s are unique: 14, 15, 16 etc.
Battery#'s are also unique: 14a, 14b, 15a, 15b etc.
Charger#'s are also unique: 14,15,16 etc.

My tables:
tblTruckModel linked to tblBatteries
one to many Reach, Clamp identifies each different battery.

tblLiftTruck unlinked
Truck#

tlbBatteries unlinked

tblEmployees unlinked

TIA
BillT

 

[Guest]

Hi BillT

It looks like TruckType has many Trucks, BatteryType has many Batteries, ChargerType has many Chargers, a Truck Uses many Batteries, a Battery is Charged many times, and an Employee is tied into who is changing/charging the battery?

If all you have is the four tables you listed, then maybe you should re-analyze/normalize your tables. I’ve listed some table ideas - look at this layout and see if it doesn’t help you along. You may need more or fewer tables than this, but I THINK that this will allow you to query what batteries are charged and not in use yet. It definately solves the problem you stated about not being able to link more than one battery to a truck

tblTruckTyp
TtypeI
(Brand, other descriptive fields…

tblTruck
TruckI
TtypeI
(PurchDate, other descriptive fields…

tblBattTyp
BtypeI
(Brand, other descriptive fields…

tblBattInventor
BattI
BtypeI
(DateRec, DateDestroyed, other descriptive fields…

tblTruckBattUsag
UsageI
TruckI
BattI
(DateInst, DateRemoved, other descriptive fields…

tblChargerTyp
ChgrTypeI
(Brand, other descriptive fields…

tblCharge
ChgrI
ChgrTypeI
(other descriptive fields…

tblEmploye
EmpI
(FirstName, LastName, Position [could be another table?], other descriptive fields…

tblBattHistor
HistI
UsageI
ChgrI
EmpI
(DateChgd, other descriptive fields…

Hope this helps

rp

----- BillT wrote: ----

Hi

Here's the scenario: (first database

I'm trying to create a database for Forklifts and how
their batteries are used. (charged

1. The program should prompt the user to input the dead
battery's number, the truck# it is coming from and how
many hours the battery was used on the truck

2. After inputting the dead battery#, the program should
display what fresh battery is available for that
particular truck

There are two different types of trucks that use only
specific batteries. eg. Reach trucks uses only Reach
batteries and Clamp trucks only use Clamp batteries

My problem: There are two batteries for each truck. I am
unable to link my battery table to the truck table because
there are twice as many records in the battery table
Truck#'s are unique: 14, 15, 16 etc
Battery#'s are also unique: 14a, 14b, 15a, 15b etc
Charger#'s are also unique: 14,15,16 etc

My tables:
tblTruckModel linked to tblBatterie
one to many Reach, Clamp identifies each different battery

tblLiftTruck unlinke
Truck

tlbBatteries unlinke

tblEmployees unlinke

TI
Bill

 

[BillT]

Thanks so much, this will certainly get me on my way.

Much appreiciat the time you took with your answer.
BillT

-----Original Message-----
Hi BillT,

It looks like TruckType has many Trucks, BatteryType has

many Batteries, ChargerType has many Chargers, a Truck
Uses many Batteries, a Battery is Charged many times, and
an Employee is tied into who is changing/charging the
battery?

If all you have is the four tables you listed, then maybe

you should re-analyze/normalize your tables. Iâ?Tve
listed some table ideas - look at this layout and see if
it doesnâ?Tt help you along. You may need more or fewer
tables than this, but I THINK that this will allow you to
query what batteries are charged and not in use yet. It
definately solves the problem you stated about not being
able to link more than one battery to a truck.

tblTruckType
TtypeID
(Brand, other descriptive fieldsâ?¦)

tblTrucks
TruckID
TtypeID
(PurchDate, other descriptive fieldsâ?¦)

tblBattType
BtypeID
(Brand, other descriptive fieldsâ?¦)

tblBattInventory
BattID
BtypeID
(DateRec, DateDestroyed, other descriptive fieldsâ?¦)

tblTruckBattUsage
UsageID
TruckID
BattID
(DateInst, DateRemoved, other descriptive fieldsâ?¦)

tblChargerType
ChgrTypeID
(Brand, other descriptive fieldsâ?¦)

tblCharger
ChgrID
ChgrTypeID
(other descriptive fieldsâ?¦)

tblEmployee
EmpID
(FirstName, LastName, Position [could be another

table?], other descriptive fieldsâ?¦)