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. 3 2792
<iv***********@gmail.com> schreef in bericht news:11*********************@j55g2000cwa.googlegro ups.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
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_id = wives.id AND husbands.child_id = children.id
AND wife.age>21 AND children.age<7
------------------
"Axel" <iv***********@gmail.com> schreef in bericht news:11**********************@c74g2000cwc.googlegr oups.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_id = 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Chris Powell |
last post by:
I am using Excel/Access 2000 and have two large Excel files (25,000 rows
each) that I wish to create linked tables in Access rather than
importing into Access. The two source Excel files change...
|
by: Internet Arrow Limited |
last post by:
Hi,
I have a requirement to write an access application that must run
under access97 and access2K. Some users will use Acess2K to access
data that will also be accessed by Access97 users. The...
|
by: Alfonso Esteban Gonzalez Sencion |
last post by:
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.
When I consult the linked...
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For...
|
by: Lewis Veale |
last post by:
I have an Access 2000 front-end pointing at a SQL Server backend, with
around 80 linked tables and views. I frequently need to point the
front-end at different versions of the back-end, and achieve...
|
by: brettclare |
last post by:
I have linked a large SQL Server table to Access, however 'only'
2,195,439 records are shown and are available to query. Can I increase
the size (cache??)/number of records showing in Access?
...
|
by: BillCo |
last post by:
I've been running a text-to-table import as part of a daily routine for
years and I've just added a few new fields and updated the import spec.
The strange thing is that it works perfectly when...
|
by: Chandan Ahuja |
last post by:
Hello ,
I use MFC DAO classes to access my MS access database .
I am using some linked tables(Linked to Oracle) in my MS Access database .
Also i have some queries in my databse ,...
|
by: bubbles |
last post by:
Using Access 2003 front-end, with SQL Server 2005 backend.
I need to make the front-end application automatically refresh the
linked
SQL Server tables.
New tables will be added dynamically in...
|
by: Bobby |
last post by:
Hi,
Not sure if this is Access, SQL or ODBC. I have a SQL database with an
Access Front End. They are linked with ODBC. Occasionally (it's
happened 3 times in 4 months) some of the linked tables...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |