how to get a value stored in tables and use it in Access Query?

[Walter]

I am having problem calculating data for a new column in a Query. I want to
be able to use data stored in a table to calculate new data. For example,
the table has the following data:

Subject Year Weighting
1st Year 1
2nd Year 2

In a query I want to retrieve a weighting of 2nd year and multiply by count
of subjects in another table. I want to be able to group them into Department

Department Subject Year
A Accounting 1st
A Calculus 2nd
A Statistic 2nd
B Calculus 2nd

so I want to be able to count all 2nd year subjects for each department and
then multiply the count with number stored in the first table.

Result I wanted:
Department Weighted Count (2nd year)
A 4
B 2

How do I go about doing that? I am using Access 2003.

 

[Edward G]

Assuming that all the fields in your 2 tables are text fields except
Weighting which should be a number field, you
would create a Totals query with your 2 tables Inner Joined. You will need
to change the data in either the Subject
Year or Year fields so that they are comparable. So either 1st Year or 1st
but NOT BOTH.
In your Totals query Group By Dept and Year from your 2nd table.(if you have
never run a totals query just click on the Greek symbol Sigma on the Toolbar
at the top of the window. Note that the QBE grid now contains a row called
Total In the criteria row of Year enter "2nd" or "2nd Year" after you
decide which standardize on. In the third column of your totals query put
Weighting from your 1st table and set the Totals Row to "SUM".
That should do it.