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

How do I make one field value determine value of second field in form?

P: 10
In my database, I have a table that has a list of procedure codes as one field and a cost assigned to each procedure as a second field.

I have a second table where I enter one of the procedure codes (looked-up from my original table/typed in), and I would like the value associated with this code in the original table to auto-populate a second field "after update".

Is there a way to make this field pull from the same table as the procedure code is pulling rather than having to type hundreds of "If/Then" statements in VBA?
Jul 19 '12 #1

✓ answered by zmbd

jcrist30:

I would normally do this in a query.
As given in your first table, hopefully, the procedure codes are your primary key. These codes are then a foreign key in the second table and you would establish a one to many relationship between the tables on the common field... no need to have the same data in two tables as you are working in a relational database.

You would then use the tables in queries to do your data pulls and other calculations. I also create my lookup controls at the query and form level.

I must ask, due to the nature of your question, about your experience level with Access? Starting out, done some work etc...

Respectfully, I ask that you take a read thru the following:
Database Normalisation and Table Structures. This article does a fairly good job explaining the normalization concepts - which for many, is a difficult thing to master.... I know, been there once a long time ago myself.

This website has a great basic tutorial on RDMS design... you're not going to design a hospital admin application with this site... well, maybe you can... the steps would be basically the same: A Tutorial for Access

Of particular note is tables page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. When you post back, this is the type of information we'll need to help you along in your project. You don't need a fancy gridded table to do this... just a line by line layout.

As for lookup fields in tables... I avoid lookup fields at the table level! :Are Lookup Fields in Tables Evil? ... despite the fact that these are supported in MSAccess, they are not supported in other databases should you need to upgrade and writing queries later on will be somewhat problematic. IMHO: The ONLY exception I've seen for this deals with a share-point integration.
There will be other people that argue that these are just fine at the table level... I side with with the MVPs that do this stuff for a living.

-z

Share this Question
Share on Google+
2 Replies


zmbd
Expert Mod 5K+
P: 5,397
jcrist30:

I would normally do this in a query.
As given in your first table, hopefully, the procedure codes are your primary key. These codes are then a foreign key in the second table and you would establish a one to many relationship between the tables on the common field... no need to have the same data in two tables as you are working in a relational database.

You would then use the tables in queries to do your data pulls and other calculations. I also create my lookup controls at the query and form level.

I must ask, due to the nature of your question, about your experience level with Access? Starting out, done some work etc...

Respectfully, I ask that you take a read thru the following:
Database Normalisation and Table Structures. This article does a fairly good job explaining the normalization concepts - which for many, is a difficult thing to master.... I know, been there once a long time ago myself.

This website has a great basic tutorial on RDMS design... you're not going to design a hospital admin application with this site... well, maybe you can... the steps would be basically the same: A Tutorial for Access

Of particular note is tables page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. When you post back, this is the type of information we'll need to help you along in your project. You don't need a fancy gridded table to do this... just a line by line layout.

As for lookup fields in tables... I avoid lookup fields at the table level! :Are Lookup Fields in Tables Evil? ... despite the fact that these are supported in MSAccess, they are not supported in other databases should you need to upgrade and writing queries later on will be somewhat problematic. IMHO: The ONLY exception I've seen for this deals with a share-point integration.
There will be other people that argue that these are just fine at the table level... I side with with the MVPs that do this stuff for a living.

-z
Jul 20 '12 #2

P: 75
the secret of your problem is in the query .
Jul 20 '12 #3

Post your reply

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