Hi,

I have a composite in a Microsoft Access Database that is not populating, when I add data to the two tables connected to it you would expect the Composite Key to populate as well wouldn't you? But this is not happening, can anyone expalain why and how this problem can be over come, keep in mind its an MS Access database as I know SQL DBs act differently with composite keys.

Please Help,

Jay.

Recommended Answers

All 15 Replies

do you mean a composite key or a computed field?

Thanks for replaying to my post as need all the help. I'm not sure what computed field is ? but I am sure the that this problem is a composite key.

You see what I have done is originally I had a many-to-many relationship but needed to sort (access doesn't like many-to many realtionships) this out so I put a Composite key between the two tables in the many-to-many relationship and this composite key has the just the primary keys of the two tables in it but the problem is that when I add data to the two related tables the composite key doesn't populate.

Is there anyway to do this? Maybe just do it with a form or anotherway? any ideas?

Anyone?

Jay.

sorry, but you are going to have to give me some details about how your tables are setup. does it look something like this?

table cars
CarID number
CarName vtext

table parts
PartID number
PartName text

table CarParts
CarID number
PartID number

Yes that is exactly right - with the carparts table acting as the composite with only the primary keys inside, nothing else.

So is it possible to make that table (carparts table) populate with data, as entries are added to the two connected tables (parts table & car table)?

If so how is this done or is there an alternative way?

ok, so you put entries in the cars table and entries in the parts table and then entries in the CarParts table to link them. Which table is having the problem?

The tables do have a link (relationship) between them, the question is, is it possible to make the carparts table populate with the primary keys (carID & PartID) when entries are added to the car table and parts tables?

i'm confused. what do you want it to populate with?
are you trying to have it do something like
you add
partid = 567, name = carburetor
and
carid = 256, name = chevy
at the same time and then the carparts table will automatically have
partid = 567, carid = 256 automatically added?

Yes that is correct. Can it be done?

well, it doesn't really make any sense to. let's say you add a new part that is going to be used on half of your cars. How is access suppose to know which ones to link to? All of them? the most recent one? none of them?

also, if you can give me some specifics about how your database, I might be able to help you figure something out

well, it doesn't really make any sense to. let's say you add a new part that is going to be used on half of your cars. How is access suppose to know which ones to link to? All of them? the most recent one? none of them?

That might be the case most situation but in my case where I have rooms (a room table) and staff (a staff table) then the the composite table/key (staff_rooms table) will show you which staff reside in which rooms and since that the composite key/table (staff_tooms table) is not not populating it does really show that information - so that is why I wanted to know if there was a way of getting this information to be displayed.

also, if you can give me some specifics about how your database, I might be able to help you figure something out

What specifics would you be needing?

so a staff person resides in more than one room?

Well not just resides but can also have onwership, as a caretaker can have ownership of more than one room as they look after many and in some situation some staff do reside in more than one.

But getting back to the original question - can the we some how get that that staff_rooms table to populate? Where it be use of VBA, form etc?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.