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

SQL to query value from field of current record only

P: n/a
Hi Everyone,

I am hoping you can help me.
I am using MS Access 2000 for this. Here is the scenario:

I have two tables:

tblMain
--------
ID (Primary Key)
Item
Chain
Name
Date
Page01
Page02
Page03
tblChains
---------
Chain (Primary Key)
Description
OwnedBy
Page01
Page02
Page03
Comments
I have a created a 1 to Many relationship between these 2 tables the
field being "Chain". As in 1 Chain can have many Items.

What I need to happen is when I enter the name of a chain the correct
pages should be put into the page fields in the tblMain table. I
currently have a query set for the Page01, Page02 & Page03 fields.

SELECT tblChains.Page01, tblChains.Chain
FROM tblChains RIGHT JOIN tblMain ON tblChains.Chain = tblMain.Chain
WHERE (((tblChains.Chain)=[tblMain].[Chain]));

What happens however is that when I have 2 records in the tblMain
table, and they have 2 different values for the chain (there are 155
different chains), then the Page01-03 in a record do show the correct
pages but unfortunately they also show the pages for the the other
record as well instead of only for the corresponding one.

Could you please help?

Best regards,
OSMeier

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ho, hang on. Your relationship is backwards.

tblMain
--------
ID (Primary Key)
Item
Chain
Name
Date
Page01
Page02
Page03

tblChains
---------
Chain (Primary Key)
***MainID (Foreign Key from MAIN) <== can't create a relationship w/o
it!
Description
OwnedBy
Page01
Page02
Page03
Comments

Did you
1. enforce referential integrity between Main and Chains? If not, that
would explain the lack of links. Then in the subform (Open frmMain in
design view and set the LinkMaster and LinkChild fields), if you link
the parent and child tables via PK and FK, you should be sorted.

Nov 13 '05 #2

P: n/a
Hi There,

What I have done is used the Chain field as a Primary Key in tblChains
and then as a foreign key in tblMain. Is this what you mean by
backwards?
Bit of a noob to all this.

Thanks,
O

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.