Select from "linked" tables?

[ivanov.alexei]

It's my first post and first DB application.
Environment: MFC DAO, MS Access database

I want to have several tables which have links to the fields to other
tables. The following example explains the basic idea.

CREATE TABLE husbands (
id COUNTER,
name TEXT,
age LONG,
wife_id LONG);

CREATE TABLE wives (
id COUNTER,
name TEXT,
age LONG,
husband_id LONG);

Now, I want to create the query which returns me all guys whose wives
are, say, older than 21. Any hints and links are welcomed.

Alexei.

[Arno R]

<iv***********@ gmail.com> schreef in bericht news:11******** *************@j 55g2000cwa.goog legroups.com...

It's my first post and first DB application.
Environment: MFC DAO, MS Access database

I want to have several tables which have links to the fields to other
tables. The following example explains the basic idea.

CREATE TABLE husbands (
id COUNTER,
name TEXT,
age LONG,
wife_id LONG);

CREATE TABLE wives (
id COUNTER,
name TEXT,
age LONG,
husband_id LONG);

Now, I want to create the query which returns me all guys whose wives
are, say, older than 21. Any hints and links are welcomed.

Alexei.

IMO your basic idea is not a good idea.
What if someone has a partner of the same sex?
also: Do *not* store age but calculate the age!

I would suggest that you Create a Table TblPersons
with a field like Sex (Male or Female)
BirthDate instead of Age
Partner_ID instead of husband_id / wife_id

Search Google (this group) for a function to calculate age when you have the BirtDate.

Arno R

[Axel]

Thanks for your comments, you're right (my sample was not very safe and
politically correct).

But... I'm afraid I have not stated the problem clearly. I had problem
with building the queries, not with the structure of the table.
Actually, I'm working on "audio DB" which has multiple tables which I
wanted to connect just like [husbands] and [wives], i.e. via "id"
field. The sample I gave was realy abstract, just to show how the
tables are related. As I understood now (after 2 h search) I needed
something like subquery or INNER JOIN.

Moreover, in my audio DB app I have one "main" table connected to two
other tables. Now, assume that I extended [husbands] properly and the
table [children] is similar to [wives]. Do you have any comments about
this solution (can it be done faster, "better")? And another question:
how can I use INNER JOIN for this example?

"Searching for guys with wives older than 21 and children younger than
7"
------------------
SELECT husbands.*
FROM husbands, wives, children
WHERE husbands.wife_i d = wives.id AND husbands.child_ id = children.id
AND wife.age>21 AND children.age<7
------------------

[Arno R]

"Axel" <iv***********@ gmail.com> schreef in bericht news:11******** **************@ c74g2000cwc.goo glegroups.com.. .

Thanks for your comments, you're right (my sample was not very safe and
politically correct).

But... I'm afraid I have not stated the problem clearly. I had problem
with building the queries, not with the structure of the table.
Actually, I'm working on "audio DB" which has multiple tables which I
wanted to connect just like [husbands] and [wives], i.e. via "id"
field. The sample I gave was realy abstract, just to show how the
tables are related. As I understood now (after 2 h search) I needed
something like subquery or INNER JOIN.

Moreover, in my audio DB app I have one "main" table connected to two
other tables. Now, assume that I extended [husbands] properly and the
table [children] is similar to [wives]. Do you have any comments about
this solution (can it be done faster, "better")? And another question:
how can I use INNER JOIN for this example?

"Searching for guys with wives older than 21 and children younger than
7"
------------------
SELECT husbands.*
FROM husbands, wives, children
WHERE husbands.wife_i d = wives.id AND husbands.child_ id = children.id
AND wife.age>21 AND children.age<7
------------------

SELECT Husbands.*
FROM (Husbands INNER JOIN Wives ON Husbands.ID = Wives.ID)
INNER JOIN Children ON Husbands.ID = Children.ID
WHERE (Wives.Age)>21 AND Children.Age<7

Just select the tables in the query grid and connect the ID-fields to get the INNER JOIN.

Arno R