query to add new column using two datasets

[nlreid]

Hi,

I'm very new to Access so responses in as simple of language as possible would be much appreciated.

I have two datasets. One has a list of teachers, their ID#s and other variables; one just has a list of the ID#s for teachers who have an A Rating.

What I need to do is create a new column in the big dataset titled ARated and have a "yes" in the column for all the teachers who have an ARating.

I know i have to create some type of query or macro to do this, but i'm not sure what/how. if you point me in the right direction (or tell me exactly how to write the syntax into the macro or something) i'm sure i can figure it out.

I've created a query with the fields in the right place, but I'm not sure how to:
1- create a new column in the table
2- have it say "yes" in the new column rather than the A Rating.



Also, how can I keep this file updated if I have to do this each week with a new dataset?

Thank you so so so so much

[weekend00]

If you eant to keep this file updated, you should not add a column to the table. Instead, you should create a query to get the rated status of the teachers. When you want to see the status, just open the query.

A sample query is bellow, modify it to meet your needs:

select distinct teacher.*, isnull(tRate.ID) as Rated from teacher left join tRate on teacher.id=tRate.id

[NTC]

I don't fully understand your post. But possibly what you are looking for is a 'calculated field' in a query. In your Access textbook you will want to look this up.

A query of a table - you can add all the fields of that table; but you can create addition fields in the query (not the table itself) and these are calculated fields. Generally they are indeed calculating among values of other fields. One can use math functions. A very helpful use is with IIF - this is another thing you can look up. For instance:

ARatingStatus: iif([Rating] like "A","Yes","No")

Possibly this helps.

[KathyL]

The question I have for you, is, are you wanting to store a “yes” in this additional field when you match the two tables? And if yes, why?

Matching two tables is a simple function of a select query. In the query, the two tables have to be matched on one or more fields; in your case, the one field will be the teacher ID’s. In the top part of the query, you’d have a line connecting IDs in one table to the IDs in the other table. The criteria for selecting records in this query would be teachers who have an A Rating. (You don’t say how teachers are marked with an A Rating, but let’s suppose, you have a field called “Rating”, and the value would be “A” for those teachers. In the query’s criteria, you would simply put “A”.

You can add one or more calculated fields in your query. Or, if you’re going to use this query as the source to a report, you could put a calculation in a test box on the report, like : =Iif([Rating] ="A","Yes","No")

Kathy
College Computer Science Instructor