Ask Experts Questions for FREE Help !
Ask
    cajalat's Avatar
    cajalat Posts: 469, Reputation: 66
    Full Member
     
    #1

    Oct 14, 2010, 04:53 PM
    Need help creating a relationship in Access 2010
    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.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Oct 14, 2010, 05:03 PM

    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.
    cajalat's Avatar
    cajalat Posts: 469, Reputation: 66
    Full Member
     
    #3

    Oct 14, 2010, 05:20 PM
    Quote Originally Posted by ScottGem View Post
    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
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Oct 14, 2010, 05:29 PM

    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.
    cajalat's Avatar
    cajalat Posts: 469, Reputation: 66
    Full Member
     
    #5

    Oct 14, 2010, 05:40 PM
    Quote Originally Posted by ScottGem View Post
    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

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Bishop Co. Began operations on January 1, 2010. Financial statements for 2010 and 201 [ 1 Answers ]

Bishop Co. began operations on January 1, 2010. Financial statements for 2010 and 2011 con- tained the following errors: Dec. 31, 2010 Dec. 31, 2011 Ending inventory $132,000 too high $156,000 too low Depreciation expense 84,000 too high Insurance expense 60,000 too low 60,000 too high...

Creating a report/query in Access [ 16 Answers ]

I have an acess Database that my boss set up for me. I enter employee names what time they sign in sign out for lunch sign back in and sign out for the day... How do I make a report/or query that can give me a monthly average for each person?

Creating simple queries in Access 2007 [ 12 Answers ]

Hello there. I'm a student in Information Technology Management and am working on an assignment for Access 2007. I ran into some issues with creating my queries and cannot seem to get the right results. Can anyone help?

MS Access 2003 Creating a form with input of ranges of data [ 4 Answers ]

Dear Scott, I tried attaching the front and back end of my database and I got the following message, "Your submission could not be processed because a security token was missing. If this occurred unexpectedly, please inform the administrator and describe the action you performed before you...

Creating a relationship in Access [ 1 Answers ]

I am trying to link Customer Contact Information to numerous orders they have placed. I have created the relationship, but not sure on how to creat the form view to correspond the two relationships. Any insight would be very helpful.


View more questions Search