Got stuck with table relations for MS Access

Question

0.00/5 (No votes)

See more:

I would like to start by saying thanks to everyone who takes some time to view this thread and try to help.

I have 3 tables in MS Access, which shall be dimmed as T, A1, and A2.

Application, which was written in C++ using WIN32 API, has a menu with two items, labeled as same as the above mentioned tables ( A1 and A2 ).

EDIT #1:
************************************
When user selects one of 2 items from the menu ,specific column in table T should reference the corresponding table's data.

The problem is that the data isn't simple attribute to be inserted into column, but a table A1 or table A2, depending on the user's choice, and that represents an issue for me.
I should design relations between tables, but I don't know how.
*********************************
I should make primary and foreign keys to reference mentioned tables, but I do not know how.

Value from tables A1 and A2 can appear in table T many times, but value for table T can appear in tables A1 and A2 only once, so I guess it is 1:N relationship for both cases.

Furthermore, I guess I should make T's primary key a foreign key for both tables ( A1 and A2 ).

Also, tables A1 and A2 are, in my opinion, weak entities, and depend on table T.

A small, improvised, graphical help:

T <------ A1 ( if user selected option A1 from menu )
^
|
|
A2 ( if user selected option A1 from menu )

So my question is:

What are proper relations for these 3 tables ( foreign key, primary key ... )?
Should I introduce 4th table, and if so, what should be primary key, foreign key... ?
Thank you,kindly.

Posted 21-May-13 9:33am

MyOldAccount

Updated 21-May-13 15:54pm

v5

Add a Solution

Comments

Maciej Los 21-May-13 17:12pm

It sounds like you are talking about hierarchical data. Please, follow these links:
Hierarchical Data Binding in ASP.NET[^]
Walkthrough: Displaying Hierarchical Data in a TreeView Control[^]

MyOldAccount 21-May-13 21:31pm

Thank you for reply, but I work in C++ using pure WIN32 API.
I am inexperienced with ER diagrams and normalization.
I will use ADO to query MS Access database, but I just don't know how to create above mentioned relations and tables.
Again,thank you for replying.

2 solutions

Solution 1

I think the relationship between A1 and T is 1:N (A1 is 1, T is N) and also the relationship between A2 and T.
When user selects A1, user can select data from T as below:
select * from T where T.column1 in (select A1.column1 from A1)
and the similar to A2.

Posted 21-May-13 17:06pm

thanh_bkhn

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-05-21T19:46:00

I will use ADO to query MS Access database, but I just don't know how to create above mentioned relations and tables.

If you can query MS Access database, you can create tables using sql statement: CREATE TABLE (MS Access)[^]. In the same way you can create relationships between tables: How to: Define Relationships Between Tables Using Access SQL[^]