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

How can I extract the value of other fields of table 1 using its PK in table 2

wasseypurian
P: 9
I have two tables which are as follows

1. Table Project now referred as tblProj, whose columns are
ID | ProjCode | ProjName | Country |.. | CountryName
2. Table Country as tblCountry, whose columns are
ID(PK) | Country


In tblProj I have stored input from the Form but the value stored in the country column Country of tblProj is the primary key value of the Country(in tblCountry)

Now I am trying to generate a code (ProjCode) which uses the actual requires the text value of "Country" in tblCountry, but when I refer to [Country] in tblProj I get the numeric value(its PK value).

I have tried to add a new column in tblProj as "CountryName" using the query
Expand|Select|Wrap|Line Numbers
  1. SELECT list_country.ID, list_country.Country
  2. FROM tbl_project INNER JOIN list_country ON tbl_project.Country = list_country.ID
  3. WHERE (((list_country.ID)=[tbl_project].[Country]));
but this lists all the county name instead of just the particular country with the ID equal to the value stored in that particular row in tblProj

I have even tried to set "CountryName" using Dlookup in the form in the Private Sub_Country_AfterUpdate() as
Expand|Select|Wrap|Line Numbers
  1. Me.CountryName = Dlookup("[Country]","[tblCountry]","[tbl_Country].[ID] = [Forms]![Project Form]![Country]")
but this one does not seem to work.

I have an idea to modify the primary key of tblCountry which include the first character of each country but I just found out that calculated field values cannot be set as the primary key.
Thanks in advance :)
Jun 26 '14 #1
Share this Question
Share on Google+
2 Replies


wasseypurian
P: 9
I tried using Ubound box in the form that displays the desired code but unable to store that particular value in the table.
Jun 26 '14 #2

twinnyfo
Expert Mod 2.5K+
P: 3,482
wasseypurian,

Your question is very confusing, but I think it is because of unclear English, more so than you question.

In addition, you say:

1. Table Project now referred as tblProj, whose columns are
ID | ProjCode | ProjName | Country |.. | CountryName
2. Table Country as tblCountry, whose columns are
ID(PK) | Country
Your code says:

Expand|Select|Wrap|Line Numbers
  1. SELECT list_country.ID, list_country.Country 
  2. FROM tbl_project INNER JOIN list_country ON tbl_project.Country = list_country.ID 
  3. WHERE (((list_country.ID)=[tbl_project].[Country]));
What are the names of your tables????

Second, Your query is doing exactly what you want it to do. You are asking the query to generat a list of countries, which have an entry in your Projects Table.

Third, You have your Tables (if your descsribing them accurately) set up correctly with a PK for each record, and it seems your projects table properly looks at the index, and not the actual tet ofr the country. This is great! Why would you now want to mess with your PKs???? Don't break a good thing!

Fourth, what, exactly, is your question? What ar eyou trying to do? Everything points to the index of your Countries Table, which is how it ought to be, so I am confused about trying to assign the text value somewhere.

Please provide a little better description. Thanks!
Jun 26 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.