Could someone explain how an update query works?

[Jeff4505]

Hi

I am working on an Access project for my ICT A2 level and was hoping someone could help me with update queries, we currently have a temp tutor who knows very little of this territory.

Basically, I have to create a database for a Car sale and lease firm. I need an update query for a few things, these being,

-Changing the availability of a car when it is leased or sold to "unavailable", preventing it from being sold twice etc.

-Changing customers that do not provide business in 2 years to "inactive" and if they place business again within 1 year then they are changed back to "active" and if not, then they are deleted from the system.

I know some of this overlaps onto forms, and perhaps you could give me a pointer with that too, I have a sales performance figure for staff, and each time they sell or lease a car, their figure must be increased by 1.

Regards,

Jeff

 

[Nick Druce]

One way to do this is to use a macro that runs a requerry function each time a change is made to the form

 

[Mile-O]

One way to do this is to use a macro that runs a requerry function each time a change is made to the form

Requerying a form has got nothing to do with the question which was around the subject of update queries.

The structure of an update query is:

UPDATE [TableName]
SET [FieldName] = newValue
WHERE [CriteriaField] = criteriaValue;

Changing a customer to inactive iif their activity has failed over two years would be like this:

UPDATE CustomerTable
SET Status = "Inactive"
WHERE LastActivity <= DateAdd("yyyy",-2,Date());