Re: Need help writing an update query
- From: John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 23 Jan 2008 10:33:05 -0700
On Tue, 22 Jan 2008 17:23:17 -0800, Rick <Rick@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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 Table:Date=1/11/2008]
then in [List Table:Publication 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]
.
- Prev by Date: Re: Null like "*" [Perameter] "*"
- Next by Date: Re: DateDiff partly failing
- Previous by thread: Re: Need help writing an update query
- Next by thread: Re: DateDiff question
- Index(es):
Relevant Pages
|
