Need help writing an update query

[Rick]

I'm not good at this so I need some help. Here's what I want to do. I have
two tables with fields that need linked to add an update. To show what I
want to do I have put the table and fields in brackets. Thanks. Here it is:

If [Status Table:Status=Sold or No Bid] and [Status Tableate=1/11/2008]
then in [List Tableublication Fee] enter [$30.00]

 

[John W. Vinson]

I'm not good at this so I need some help. Here's what I want to do. I have
two tables with fields that need linked to add an update. To show what I
want to do I have put the table and fields in brackets. Thanks. Here it is:

If [Status Table:Status=Sold or No Bid] and [Status Tableate=1/11/2008]
then in [List Tableublication Fee] enter [$30.00]

How are the Status Table and the List Table linked (if at all)? Do they have a
field in common? Have you defined a Relationship between the tables? If there
are 509 records in the Status Table and 167 records in the List Table, which
records should be searched for status and date, and which records should be
updated - and how can you tell?

Please use standard syntax rather than all this gibberish with colons:

[tablename].[fieldname] = "Sold or No Bid"

for example.

John W. Vinson [MVP]

 

[Rick]

Thanks for the response.

I'm sorry I don't know gibberish from syntax. If I did, I may not be
posting this question. I'll try to answer your questions with what I do know.

How are the Status Table and the List Table linked (if at all)?

They are linked by a common field called Cert Number

Do they have a field in common?
Yes, the Cert Number.

Have you defined a Relationship between the tables?
I don't know what that means

If there are 509 records in the Status Table and 167 records in the List
Table, which
records should be searched for status and date, and which records should be
updated - and how can you tell?
Both tables have the same number of fields (Cert Number); they just have
different sets of information.

Please use standard syntax rather than all this gibberish with colons:

[tablename].[fieldname] = "Sold or No Bid"

for example.

John W. Vinson [MVP]

 

[John W. Vinson]

I'm not good at this so I need some help. Here's what I want to do. I have
two tables with fields that need linked to add an update. To show what I
want to do I have put the table and fields in brackets. Thanks. Here it is:

If [Status Table:Status=Sold or No Bid] and [Status Tableate=1/11/2008]
then in [List Tableublication Fee] enter [$30.00]

First off, learn about relationships and table normalization. They are
ABSOLUTELY FUNDAMENTAL to any productive use of Access. See some of the
tutorials at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Next, be sure that [Cert Number] is the Primary Key of the List table
(assuming that table has one record for each Cert Number). Open the
Relationships window - the toolbar button has three little datasheets with
lines connecting them. Add the two tables, and drag Cert Number from List
Table to Status Table. Check the "Enforce Referential Integrity" check box;
this will ensure that you cannot set a status for a nonexistant cert number.

Then, create a new Query in the queries window. Add the Status table and the
List table to the query window. Drag the [Cert Number] field from the "one"
side table to the [Cert Number] field in the related table. You should get a
join line connecting them.

Add the Status, Date, and Publication Fee fields to the query by dragging them
from the table icons into the query grid.

On the first criteria line under Status type "Sold"; on the next line under
that, type "No Bid". Putting the criteria on two lines will use OR logic
returning the record if the status has either one of these values.

On *both* criteria lines under the Date field put

[Enter date:]

This will prompt you for a date (you can type 1/11 or whatever date you want
to see) when you run the query. It needs to be on both lines so that you find
those items with Sold and 1/11, *or* those items with No Bid and 1/11.

First, open the query datasheet as it stands. Do you see the records that you
want to see?

If so, change the query to an Update query using the Query menu option or the
query type tool in the toolbar. On the Update To line under the Publication
Fee type

30

Run the query by clicking the ! icon.

John W. Vinson [MVP]