Re: IF THEN in query..Maybe IIf

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


"DieSpammersDie" <DieSpammersDie-no-spam@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in message
news:YW50aWdvbmU=.4e3147c457e8d942c2e889a24c827957@xxxxxxxxxxxxxxxxxxxxxxxxxx
> 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?
>
>


.

Relevant Pages

  • Re: Subselect Query Problem
    ... I'm testing what you posted in the Query area ... Table A actually uses an autonumber primary key. ... >FROM tableA LEFT JOIN (SELECT project, status FROM tableB WHERE ...
    (microsoft.public.access.queries)
  • Re: help
    ... How do I bring that first query in the second one, just put it in the criteria or in the field name in design view?? ... > TableA and only those records in TableB that equal the join field in TableA. ... About the primary key, I don't have the ... > employeeID as the primary key on the Training table since I realized that ...
    (microsoft.public.access.queries)
  • Re: IF THEN in query..Maybe IIf
    ... Using Tina's [TableA] as the name of the Table to which you want to add the imported addresses, and as the Table that you copied ... Bill 1234 Main St Sydney MN 66789 John 1234 Main St Sydney MN 66789 Mary 1234 Main St Sydney MN 66789 ... now turn the query into an Update query. ... if the data file ...
    (microsoft.public.access.queries)
  • Re: How easy is this?
    ... > I am trying to create a form based on a query that is updatable and ... > TableA with 3 Fields: ... > and TableB with 2 Fields: ... ,CONSTRAINT pk_TableA_10232005_2 PRIMARY KEY ...
    (microsoft.public.access.queries)
  • Add data to specific fields within existing records in a table from another table.
    ... I would like to use a query or other method to add data from one ... Example: TableA and TableB ... field in TableA based on "Customer ID" which is the primary key in each ...
    (microsoft.public.access.queries)