IF THEN in query..Maybe IIf

[Guest]

I have a table that imports data from a file. However, I want to use an
address depending on a condition in the data. Here is the scenario.
Everyone in the table works at the same location, call it "1234 Main St,
Sydney, MN 66789." However, if the data file has the person's home address
I want the query to update the table with the home address.

Here is what I have done so far. Since everyone works in the same place, I
have hard coded the work address into the table:

SELECT "1234 Main St." AS Address, "Sydney" AS City, "MN" AS State, "66790"
AS ZIP

by doing this every single person in the table has the same default address,
which is the work address.

Now here is the trick, if the data file has a home address then I want to
replace the work address with the home address, if there is no home address
then I want to simply default to the work address (which is already the
default).

So, if the person lives at 999 Oak St., Sydney, MN 66793 I want to replace
the default work address with the home address. The original data file has
a field, "HOME_ADDRESS' which is populated with either Y, YES, N, or NO. So
I will have to use the "LIKE" function, here is what I want to do in pseudocode:

If [HOME_ADDRESS] like 'Y*' then
INSERT INTO TABLE
[(HOME_ADDRESS.Street),(HOME_ADDRESS.city),(HOME_ADDRESS.State),(HOME_ADDRESS.Zip)]


Since the table is already populated with the work address for every
individual there is no need for an "ELSE" statement.


I have been looking and found that the IIf statement may do what I want, but
I haven't been able to get the syntax correct, or I really don't understand
it. What would the proper access SQL statement be or could I do something
in the design view?

 

[tina]

assuming that there is a primary key for the existing records in the data
table, which i'll call TableA, that matches a primary key in the imported
data, which i'll call TableB, you could try the following:

create a SELECT query on the two tables, INNER JOINed on the primary keys,
and
WHERE HOME_ADDRESS In ("Y", "YES")
that should pull all the matching records in the two tables, where the
TableB record is a home address.
now turn the query into an Update query. set each address field in TableA to
the address field in TableB. example:

UPDATE TableA INNER JOIN TableB ON TableA.PrimaryKey = TableB.PrimaryKey SET
TableA.StreetAddress = [TableB].[StreetAddress], TableA.City =
[TableB].[City], TableA.State = [TableB].[State], TableA.Zip =
[[TableB].[Zip]
WHERE HOME_ADDRESS In ("Y","Yes")

when testing an action query, it's always a good idea to back up the
database first - just in case...!

hth


in message

I have a table that imports data from a file. However, I want to use an
address depending on a condition in the data. Here is the scenario.
Everyone in the table works at the same location, call it "1234 Main St,
Sydney, MN 66789." However, if the data file has the person's home address
I want the query to update the table with the home address.

Here is what I have done so far. Since everyone works in the same place, I
have hard coded the work address into the table:

SELECT "1234 Main St." AS Address, "Sydney" AS City, "MN" AS State, "66790"
AS ZIP

by doing this every single person in the table has the same default address,
which is the work address.

Now here is the trick, if the data file has a home address then I want to
replace the work address with the home address, if there is no home address
then I want to simply default to the work address (which is already the
default).

So, if the person lives at 999 Oak St., Sydney, MN 66793 I want to replace
the default work address with the home address. The original data file has
a field, "HOME_ADDRESS' which is populated with either Y, YES, N, or NO. So
I will have to use the "LIKE" function, here is what I want to do in pseudocode:

If [HOME_ADDRESS] like 'Y*' then
INSERT INTO TABLE
[(HOME_ADDRESS.Street),(HOME_ADDRESS.city),(HOME_ADDRESS.State),(HOME_ADDRES
S.Zip)]


Since the table is already populated with the work address for every
individual there is no need for an "ELSE" statement.


I have been looking and found that the IIf statement may do what I want, but
I haven't been able to get the syntax correct, or I really don't understand
it. What would the proper access SQL statement be or could I do something
in the design view?

 

[Vincent Johns]

I wasn't sure I should post this, since Tina's answer is correct and is
probably sufficient. However, since in your pseudocode you used
[HOME_ADDRESS] as both a field name and the name of a Table, I thought
it might help to show the SQL using your original names, along with an
example.

Using Tina's [TableA] as the name of the Table to which you want to add
the imported addresses, and [HOME_ADDRESS] as the Table that you copied
from the file, the SQL would look like this:

UPDATE HOME_ADDRESS INNER JOIN TableA
ON HOME_ADDRESS.PersonID = TableA.PersonID
SET TableA.Address = [HOME_ADDRESS]![Address],
TableA.City = [HOME_ADDRESS]![City],
TableA.State = [HOME_ADDRESS]![State],
TableA.ZIP = [HOME_ADDRESS]![ZIP]
WHERE (((HOME_ADDRESS.HOME_ADDRESS) Like "y*"));

where [PersonID] is the primary key of [TableA] (and is a matching, we
hope, foreign key in [HOME_ADDRESS]). I don't know what you use for a
key value, but an employee badge number would be a good choice, as it
would be guaranteed to be unique. For this example, I'm using names.

Example:
[TableA] before updating:
PersonID Address City State ZIP
-------- ------------ ------------ ----- -----
Bill 1234 Main St Sydney MN 66789
John 1234 Main St Sydney MN 66789
Mary 1234 Main St Sydney MN 66789

[HOME_ADDRESS] Table:
PersonID Address City State ZIP HOME_ADDRESS
-------- ------------ ------------ ----- ----- ------------
John 885 Maple Lake Wobegon MN 54321 Yes
Mary 85 E Main Edina MN 56565 No
Paul 105 Elm Lake Wobegon MN 54321 Yes

Now we run the Update Query, and [TableA] changes.

[TableA] after updating:
PersonID Address City State ZIP
-------- ------------ ------------ ----- -----
Bill 1234 Main St Sydney MN 66789
John 885 Maple Lake Wobegon MN 54321
Mary 1234 Main St Sydney MN 66789

John's record is updated, but not Mary's (since [HOME_ADDRESS] was not
"Yes"), and not Bill's (no matching name in [HOME_ADDRESS].[PersonID]).
Paul's record in [HOME_ADDRESS] is ignored, since there is no matching
record in [TableA], and Access gives you no warning that it's ignored.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

assuming that there is a primary key for the existing records in the data
table, which i'll call TableA, that matches a primary key in the imported
data, which i'll call TableB, you could try the following:

create a SELECT query on the two tables, INNER JOINed on the primary keys,
and
WHERE HOME_ADDRESS In ("Y", "YES")
that should pull all the matching records in the two tables, where the
TableB record is a home address.
now turn the query into an Update query. set each address field in TableA to
the address field in TableB. example:

UPDATE TableA INNER JOIN TableB ON
TableA.PrimaryKey = TableB.PrimaryKey SET
TableA.StreetAddress = [TableB].[StreetAddress], TableA.City =
[TableB].[City], TableA.State = [TableB].[State], TableA.Zip =
[[TableB].[Zip]
WHERE HOME_ADDRESS In ("Y","Yes")

when testing an action query, it's always a good idea to back up the
database first - just in case...!

hth


in message

I have a table that imports data
from a file. However, I want to use an
address depending on a condition in
the data. Here is the scenario.
Everyone in the table works at the
same location, call it "1234 Main St,
Sydney, MN 66789." However, if the data
file has the person's home address
I want the query to update the table
with the home address.

Here is what I have done so far. Since
everyone works in the same place, I
have hard coded the work address into the table:

SELECT "1234 Main St." AS Address,
"Sydney" AS City, "MN" AS State, "66790"
AS ZIP

by doing this every single person
in the table has the same default address,
which is the work address.

Now here is the trick, if the data file
has a home address then I want to
replace the work address with the home
address, if there is no home address
then I want to simply default to the
work address (which is already the
default).

So, if the person lives at 999 Oak St.,
Sydney, MN 66793 I want to replace
the default work address with the home address.
The original data file has
a field, "HOME_ADDRESS' which is populated
with either Y, YES, N, or NO. So
I will have to use the "LIKE" function,
here is what I want to do in pseudocode:
If [HOME_ADDRESS] like 'Y*' then
INSERT INTO TABLE
[(HOME_ADDRESS.Street),(HOME_ADDRESS.city),
(HOME_ADDRESS.State),(HOME_ADDRESS.Zip)]

Since the table is already populated
with the work address for every
individual there is no need for an "ELSE" statement.

I have been looking and found that the
IIf statement may do what I want, but
I haven't been able to get the syntax
correct, or I really don't understand
it. What would the proper access SQL
statement be or could I do something
in the design view?