How can we use data from other fields in a look up table?

General discussion on MS Access and its databases.

How can we use data from other fields in a look up table?

Postby MikeLON » Mon May 12, 2014 7:53 am

In Access 2010, in a table or form, are you able to use data from a lookup table like you can in a vlookup in Excel?

I am creating a Student database starting with the MS Students template. I will have a Parameter Table that contains weighting factors for each test for a given class. When scores for a particular test are entered for each student in a Test Results table, I would like to use the weighting factors for that test from the Parameter Table. These would be used in formulas/expressions to calculate a weighted score for the particular test as well as class-to-date average for all the tests to that point.

It seems this can be done in a query, but I am wondering if it can also be done in form so that when the instructor inputs the test scores the calculations are performed and the results of the calculations displayed. Or, if not, can the query be "built in" to the form to accomplish the same effect?

I am new to Access.

Thanks
MikeLON
 
Posts: 32
Joined: Tue Mar 25, 2014 6:18 am

Re: How can we use data from other fields in a look up table

Postby Ricky G. Marion » Mon May 12, 2014 11:49 am

Hi,
The following Options will help you to solve this problem:

1. Parameter table can be included in the form query, join on the test ID fields, use join type "Include all records from TestResults and only those from Parameter that match". This will make the related info available for reference

2. Combobox for selecting test that has a query of the Parameter table as RowSource, include the desired fields as columns of the combobox, this also makes the related info available for reference

3. DLookup function
Thanks
Ricky G. Marion
 
Posts: 25
Joined: Mon Mar 24, 2014 6:33 am

Re: How can we use data from other fields in a look up table

Postby UniSET » Mon May 12, 2014 11:56 am

Hi,
Please follow the Options given below you can easily understand the solution of this problem:

1. Parameter table can be included in the form query, join on the test ID fields, use join type "Include all records from TestResults and only those from Parameter that match". This will make the related info available for reference

2. combobox for selecting test that has a query of the Parameter table as RowSource, include the desired fields as columns of the combobox, this also makes the related info available for reference

3. DLookup function

Thanks
UniSET
 
Posts: 22
Joined: Tue Mar 25, 2014 6:33 am


Return to MS Access- Help Forum

Who is online

Users browsing this forum: No registered users and 1 guest

cron