Creating relationship, need help.

[Calvinle]

Hi guys,

I am having problem creating the relationship for the following table. Can anyone help me out on this?

I was able to create from the first 2 columns, and once I reach the 3rd, it won't let me add duplicate data as the primary key.

Any help will be appreciated.

Thanks.Relationship.zip

[John_G]

I can't see from your diagram what your table looks like; you talk about columns, but where are they? Can you post the the description you have for the table in MS Access, and what the primary key is?

If you only have one table, then you can't have any relationships - relationships are defined between two tables, not within tables.

Can you explain what it is you want to achieve?

[Calvinle]

Hi,

That is my problem. I tried to created the table, and because of the primary key, I could not continu. Each column you can see that in the word attached. There is 7 columns.
I guess I need 7 tables?

Here is what I have done so far:
tblGender:
Gender (TEXT) (PK)

tblCat:
Gender (TEXT)
Category (TEXT) (PK)

tblAgeCat:
Category (TEXT)
AgeCat (TEXT) (PK)

After trying to add new record to Category 1 -> Kid, then Category 2 -> Kid, I got stuck saying I cannot have same value in PK. But if I do not add that PK, then I will not be able to link to the next table, which is tblSituation:
AgeCat (TEXT)
Situation (TEXT) PK


What I want to achieve:
I want to put many combobox as to answer question 1 by 1:
Start off with Gender, it will list "MEN" and "WOMEN". If "MEN" is selected, then the next combobox will open (as per diagram) to choose between "CATEGORY 1", "CATEGORY 2" or "CATEGORY 3".
Wether 1, 2 or 3 is chosen, it will open the combobox for the next option, "KID", or "ADULT".
Then if the user choose wether "KID" or "ADULT", it will open to "DEATH", "ALIVE" or "UNKNOWN".
By this, it will open to only "CLAIM AMOUNT", which they can enter an amount themself but store as TEXT.
After the amount is entered, it will open the combobox for "CASH", "WIRE" or "CHEQUE".
If the "CASH" is chosen, then it will open combobox for "TIME", "PHONE".
If the "WIRE" is chosen, then it will open the same combobox but with option "ACC#" only.
If the "CHEQUE" is chosen, then it will open the same combobox but with option "ADDR HOME" or "ADDR BUSI".

I know it can be done by setting each combobox by "as per value", then thru VBA. However, In case I need to add more field, instead of going thru VBA to add. I can simply add to the table and the option should be listed as per the table.

Thank you.

[John_G]

You are unclear as to what a Primary Key (PK) is. A PK is a table field which contains a value which uniquely identifies each record in the table. Your diagram does not contain any field that will do that.

How many tables do you have in your database, and what do they look like, i.e. what are the fields in each? some will be quite small (which is perfectly OK), for example tblGender will have at most 2 records - ever.

What exactly is the data you are trying to track here? There has to be some sort of "Main" table.

You need to leave Access alone for a while, and take some time to properly analyze your data to determine what the entities (which become tables in Access) are, what the data in them will be, and how they relate to each other. Only then should you start to build your database.