Hi all,

I'm trying to build an access db (I don't know access very well) to keep track of my rechargeable batteries (geeky I know). Here are my tables:

BatteryType: Just contains the different types of batteries available
Inventory: contains batterytype, serial number, purchase date, etc
ChargeHistory: for each inventory item I keep track of when charged, how charged (break-in, refresh, normal), etc

The above is all good (I think). The last table is where I'm having a difficult time figuring out. My last table is called Electronics. I have all sorts of devices in the house that use the above batteries... remote controls, toys (tons of those), smoke detectors, game consoles, etc. So of these electronic devices use 1xAA, 2xAA, 3xAAA, 2xAAA, 1x9V, etc... you get the idea.

My question is, how do I design that last table where say a particular toy that takes 4xAA has specific AA's from the Inventory table... i.e. I serialize my batteries.

Any help greatly appreciated.

You need another table like so:

Tblbatteryusage
BatteryUsageID (PK Autonumber)
DeviceID (foreign key)
BatteryID (FK)

This will allow you to keep track of each battery used in a device.

you need another table like so:

tblbatteryusage
BatteryUsageID (PK Autonumber)
DeviceID (foreign key)
BatteryID (FK)

This will allow you to keep track of each battery used in a device.

Thanks Scott... so let me see if I understand this correctly. (again I JUST started to learn access about 3 days ago). Do I create the table above as a join table where I still have an Electronics Table and Inventory Table and a typical tblbatteryusage would have a record like this:

BatteryUsageID, DeviceID, BatteryID
Autonumber, Toy1, Serial1_for_batt
Autonumber, Toy1, Serial2_for_batt
Autonumber, Toy1, Serial3_for_batt

How do I limit or control how many Batteries Toy1 gets for instance if only it can take 2 batteries?

Casey

I thought that name sounded familiar. Welcome back.

I would include a separate field in the device table for the # of batteries. Then you can use that to limit the number of records for a given device.

I thought that name sounded familiar. Welcome back.

I would include a separate field in the device table for the # of batteries. Then you can use that to limit the number of records for a given device.

Thanks Scott. Hopefully this time I'll stick around longer before I get snagged by uncle sam again :)

I'll try your suggestion and see how far I can go.

Casey