Access SQL

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

I am wanting to use an update query in Access - I notice that you can
assemble the SQL visually by clicking fields and putting conditions in
certain fields, but I also noticed you can edit the SQL directly.

If I write my own SQL will the visual bit still work ?

I want to update a number of fields based on a condition - the field I
want to update depends on the value already in the field - can I do that
? Sort of like this;

Pseudo Code

UPDATE Main INNER JOIN NewImport ON Main.UID = NewImport.Field1

If Main.PayDate1 = 0 (not sure how to test if there is a date)
SET Main.PayDate1 = Date (Todays Date)
Else If Main.PayDate1 <> 0
SET Main.PayDate2 = Date (Todays Date)
End If


Not sure how SQL works with these types of conditions - any help
appreciated.

Thanks
 
Isis said:
I am wanting to use an update query in Access - I notice that you can
assemble the SQL visually by clicking fields and putting conditions in
certain fields, but I also noticed you can edit the SQL directly.

If I write my own SQL will the visual bit still work ?

I want to update a number of fields based on a condition - the field I
want to update depends on the value already in the field - can I do
that ? Sort of like this;

Pseudo Code

UPDATE Main INNER JOIN NewImport ON Main.UID = NewImport.Field1

If Main.PayDate1 = 0 (not sure how to test if there is a date)
SET Main.PayDate1 = Date (Todays Date)
Else If Main.PayDate1 <> 0
SET Main.PayDate2 = Date (Todays Date)
End If


Not sure how SQL works with these types of conditions - any help
appreciated.
Click to expand...

Go back to the "visual bit". In the "Update To" underneath PayDate1 put...

IIf([PayDate1] Is Null, Date(), [PayDate1])

....and underneath the field PayDate2 put...

IIf(IIf([PayDate1] Is Not Null, [PayDate1], [PayDate2])

Once you put a field in an UPDATE query you cannot conditionally choose to
not update it, but you can conditionally set its value to itself which has
the same effect.
 
Isis said:
I am wanting to use an update query in Access - I notice that you can
assemble the SQL visually by clicking fields and putting conditions in
certain fields, but I also noticed you can edit the SQL directly.

If I write my own SQL will the visual bit still work ?

I want to update a number of fields based on a condition - the field I
want to update depends on the value already in the field - can I do that
? Sort of like this;

Pseudo Code

UPDATE Main INNER JOIN NewImport ON Main.UID = NewImport.Field1

If Main.PayDate1 = 0 (not sure how to test if there is a date)
SET Main.PayDate1 = Date (Todays Date)
Else If Main.PayDate1 <> 0
SET Main.PayDate2 = Date (Todays Date)
End If


Not sure how SQL works with these types of conditions - any help
appreciated.

Thanks
Click to expand...

A qualified yes. The Query Builder is quite good at parsing SQL, but
I've encountered complex queries where it just can't cope. However,
you'll learn a lot if you always look at the SQL when you've used the
Query Builder to create a query.

You're still thinking in VB - might be worth reading a short primer on
SQL to make the necessary adjustment. The approach is to start with a
query which will select the records you want to update, and then take it
the further step to add the updating action by changing the query type
and adding "update to" expressions or values.

I have some misgivings about your pseudo-code. Would you want to skip
the update to PayDate2 if you've had to update a Null value in PayDate1?
That's what the logic says. Plus you don't need to test the same
thing again in the ELSE clause.

You can select records meeting criterion1 AND criterion2 by putting both
conditions on one line of the matrix. If you want to select records
matching criterion1 OR criterion2, put the second condition on a
separate line. But you need to select all records, as PayDate1 is
either Null or not. What you need to do is to calculate the value to be
updated, and when.

For that you need the IIF function - loosely: "If condition a is true,
return b, else c". I figure you'll need two "update to" expressions:
loosely:

one (updating PayDate1):
=IIF (PayDate1 IS NULL, today's date, the existing value)


the other (for PayDate2):
=IIF (PayDate1 IS NOT NULL, today's date, the existing value)

Think of the second comma as meaning "ELSE".

You might find it useful to fool around with the Expression Builder to
see how it works, and then use it to construct these "update to"
expressions in the correct syntax.

Phil
 
You could do this by running two queries in this sequencer

UPDATE Main INNER JOIN NewImport
ON Main.UID = NewImport.Field1
SET Main.Paydate2 = Date()
WHERE Main.PayDate1 is Not Null

UPDATE Main INNER JOIN NewImport
ON Main.UID = NewImport.Field1
SET Main.Paydate1 = Date()
WHERE Main.PayDate1 is Null

Or you can do it by using one query (maybe a bit slower)

UPDATE Main INNER JOIN NewImport
ON Main.UID = NewImport.Field1
SET Main.Paydate1 = IIF([PayDate1] is Null,Date(),[Paydate1])
, Main.Paydate2 = IIF([PayDate1] is not null,Date(),[PayDate2])

Yes, if you write a query in the SQL view, the design view will still
work in many cases. But there are queries, you can write in SQL view
that cannot be represented in design view.

-- Union queries and pass through queries cannot be represented in
design view.

--Queries that use a join other than the standard Table.Field =
Table.field in the join clause cannot be represented. For instance:
SELECT * FROM TableA INNER JOIN TableB on TableA.Field1<=TableB.Field1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top