By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

SQL statement

P: n/a
Hi all,

I hope someone can help me with the following,
I have several (lookup) tables, were id's and names are stored.
All table all brought together by relationships (many to 1) in the
'master' table (by referring to the id's of the records in the lookup
tables).
I want to make a query where all names are looked up using joins:

SELECT
s.[id_master],
s.[date],
s.[description],
s.[table_x_id],
s.[table_y_id],
x.[table_x_name],
y.[table_y_name]

FROM
tblMaster s
join tblX x on s.table_x_id = x.table_x_name
join tblY y on s.table_y_id = y.table_y_name
;

Showing me the corresponding names from tables tblX and tblY, instead of
the id's (that are stored in tblMaster)...

Looks simple, but the SQL statement from above doesn't work!

Many thanks in advance!

Zeff
Feb 13 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Feb 13, 10:56 am, Zeff <z...@trash.netwrote:
Hi all,

I hope someone can help me with the following,
I have several (lookup) tables, were id's and names are stored.
All table all brought together by relationships (many to 1) in the
'master' table (by referring to the id's of the records in the lookup
tables).
I want to make a query where all names are looked up using joins:

SELECT
s.[id_master],
s.[date],
s.[description],
s.[table_x_id],
s.[table_y_id],

x.[table_x_name],
y.[table_y_name]

FROM
tblMaster s
join tblX x on s.table_x_id = x.table_x_name
join tblY y on s.table_y_id = y.table_y_name
;

Showing me the corresponding names from tables tblX and tblY, instead of
the id's (that are stored in tblMaster)...

Looks simple, but the SQL statement from above doesn't work!

Many thanks in advance!

Zeff
I'm just guessing...should your joins be

join tblX x on s.table_x_id = x.table_x_id
join tblY y on s.table_y_id = y.table_y_id

instead of

join tblX x on s.table_x_id = x.table_x_name
join tblY y on s.table_y_id = y.table_y_name

?

Bruce

Feb 13 '07 #2

P: n/a
Dear Bruce,

Thanks for your quick reply. For posting, I made a kind of 'abstraction'
of the original query. Actually, my complete query looks like this:

SELECT
s.[id_sample],
s.[reference],
s.[date],
s.[ref_to_id_files],
s.[ref_to_id_species],
s.[ref_to_id_identification],

f.[name files],
i.[name identification],
s.[name species]

FROM
tblSample s
JOIN tblFiles f on s.ref_to_id_files = f.id_files
JOIN tblIdentification i ON s.ref_to_id_identification = i.id_identification
JOIN tblSpecies s ON s.ref_to_id_species = s.id_species;

Hope someone can help...

Many thanks!

Zeff.

Bruce wrote:
On Feb 13, 10:56 am, Zeff <z...@trash.netwrote:
>Hi all,

I hope someone can help me with the following,
I have several (lookup) tables, were id's and names are stored.
All table all brought together by relationships (many to 1) in the
'master' table (by referring to the id's of the records in the lookup
tables).
I want to make a query where all names are looked up using joins:

SELECT
s.[id_master],
s.[date],
s.[description],
s.[table_x_id],
s.[table_y_id],

x.[table_x_name],
y.[table_y_name]

FROM
tblMaster s
join tblX x on s.table_x_id = x.table_x_name
join tblY y on s.table_y_id = y.table_y_name
;

Showing me the corresponding names from tables tblX and tblY, instead of
the id's (that are stored in tblMaster)...

Looks simple, but the SQL statement from above doesn't work!

Many thanks in advance!

Zeff

I'm just guessing...should your joins be

join tblX x on s.table_x_id = x.table_x_id
join tblY y on s.table_y_id = y.table_y_id

instead of

join tblX x on s.table_x_id = x.table_x_name
join tblY y on s.table_y_id = y.table_y_name

?

Bruce
Feb 14 '07 #3

P: n/a
Hi,
I hope someone can help me with the following,
I have several (lookup) tables, were id's and names are stored.
.......
.......
Showing me the corresponding names from tables tblX and tblY, instead
of the id's (that are stored in tblMaster)...
That's the behavior and evil of Lookup Fields ......

You can read more about it here :

http://www.mvps.org/access/lookupfields.htm

Regards
Jens
Feb 14 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.