Update Query Question

I know this is a very basic question but I honestly don't know where to find
the information specific to this question.

I have a table of employees names and their immunizations. The immunization
information comes from Query 1 of another database which is updated each
week. I have been deleting the contents of the table and replacing it with
the contents of Query 1 each week; the big problem is some employees don't
have their immunization information in the Query 1 it comes from yet another
database and I re-fill in those blanks each week and others are marked N/A.

I would like to construct an update query that will take the "Yes" from
Query 1 and put into the immunization column of table 1 only if that space in
table 1 is N or contains N/A.

Obviously I've never made an update query before and in fact I'd forgotten
about them until this weekend when I was thinking--there has to be an easier
way.

How do I set this up?

TABLE 1:

Employee ID, Employee Name, Department, Immunization (y/n/NA

QUERY 1
Employee ID, Employee Name, Immunization

Thanks

I'd do this in three stages:

1. Append new employees form Query 1 to Table 1.

2. Update the Employee Name column in Table 1 unconditionally from Query 1.
Any whose values have changes will be changed to the new values, any who have
not changed will simply be updated to what they were already, so in effect
won't be changed. I note that Query 1 does not have a Department column, so
these will remain unchanged in Table 1.

3. Update the Immunization column in Table 1 from Query 1 only where the
Query 1 Immunization values are 'Y' and Table 1 contains values other than
'Y' or is Null.

So you'd need one 'append' and two simple 'update' queries for this, which
you'd execute in sequence:

First, to insert new employees, if any:

INSERT INTO [Table 1] ([Employee ID], [Employee Name], [Immunization])
SELECT [Employee ID], [Employee Name], [Immunization]
FROM [Query 1]
WHERE NOT EXISTS
(SELECT *
FROM [Table 1]
WHERE [Table 1].[Employee ID] = [Query 1].[Employee ID]);

To update employee names:

UPDATE [Table 1] INNER JOIN [Query 1]
ON [Table 1].[Employee ID] = [Query 1].[Employee ID]
SET [Table 1].[Employee Name] = [Query 1].[Employee Name];

To update the immunization column:

UPDATE [Table 1] INNER JOIN [Query 1]
ON [Table 1].[Employee ID] = [Query 1].[Employee ID]
SET [Table 1].[Immunization] = [Query 1].[Immunization]
WHERE [Query 1].[Immunization] = "Y"
AND ([Table 1].[Immunization] "Y"
OR [Table 1].[Immunization] IS NULL);

Note that in the last query the positions of the parentheses are important to
force the OR operation to evaluate independently of the AND operation in the
WHERE clause.

All of the above assumes that the Employee ID values do not change of course
as these are used to join the query and table. Assuming that to be the case
you could automate the whole operation by executing the three queries in
sequence with a single click of a button on an unbound dialogue form.

Ken Sheridan
Stafford, England

randlesc wrote:
I know this is a very basic question but I honestly don't know where to find
the information specific to this question.

I have a table of employees names and their immunizations. The immunization
information comes from Query 1 of another database which is updated each
week. I have been deleting the contents of the table and replacing it with
the contents of Query 1 each week; the big problem is some employees don't
have their immunization information in the Query 1 it comes from yet another
database and I re-fill in those blanks each week and others are marked N/A.

I would like to construct an update query that will take the "Yes" from
Query 1 and put into the immunization column of table 1 only if that space in
table 1 is N or contains N/A.

Obviously I've never made an update query before and in fact I'd forgotten
about them until this weekend when I was thinking--there has to be an easier
way.

How do I set this up?

TABLE 1:

Employee ID, Employee Name, Department, Immunization (y/n/NA

QUERY 1
Employee ID, Employee Name, Immunization

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

Thank you. Sorry for the delay, I must have forgotten to check "notify me of
replies."

This looks great. My only question is, there is some data in immunization
that doesn't come from the second query--these are people, about 100, whose
information comes from a variety of systems across the company, mostly legacy
systems--if I've entered this into the main table will these be overwritten
when I run the query because they won't match with what the Query 1 will
return (query 1 will return them as blanks, most times).

Thanks again. I can't wait to try this out.

"KenSheridan via AccessMonster.com" wrote:

I'd do this in three stages:

1. Append new employees form Query 1 to Table 1.

2. Update the Employee Name column in Table 1 unconditionally from Query 1.
Any whose values have changes will be changed to the new values, any who have
not changed will simply be updated to what they were already, so in effect
won't be changed. I note that Query 1 does not have a Department column, so
these will remain unchanged in Table 1.

3. Update the Immunization column in Table 1 from Query 1 only where the
Query 1 Immunization values are 'Y' and Table 1 contains values other than
'Y' or is Null.

So you'd need one 'append' and two simple 'update' queries for this, which
you'd execute in sequence:

First, to insert new employees, if any:

INSERT INTO [Table 1] ([Employee ID], [Employee Name], [Immunization])
SELECT [Employee ID], [Employee Name], [Immunization]
FROM [Query 1]
WHERE NOT EXISTS
(SELECT *
FROM [Table 1]
WHERE [Table 1].[Employee ID] = [Query 1].[Employee ID]);

To update employee names:

UPDATE [Table 1] INNER JOIN [Query 1]
ON [Table 1].[Employee ID] = [Query 1].[Employee ID]
SET [Table 1].[Employee Name] = [Query 1].[Employee Name];

To update the immunization column:

UPDATE [Table 1] INNER JOIN [Query 1]
ON [Table 1].[Employee ID] = [Query 1].[Employee ID]
SET [Table 1].[Immunization] = [Query 1].[Immunization]
WHERE [Query 1].[Immunization] = "Y"
AND ([Table 1].[Immunization] "Y"
OR [Table 1].[Immunization] IS NULL);

Note that in the last query the positions of the parentheses are important to
force the OR operation to evaluate independently of the AND operation in the
WHERE clause.

All of the above assumes that the Employee ID values do not change of course
as these are used to join the query and table. Assuming that to be the case
you could automate the whole operation by executing the three queries in
sequence with a single click of a button on an unbound dialogue form.

Ken Sheridan
Stafford, England

randlesc wrote:
I know this is a very basic question but I honestly don't know where to find
the information specific to this question.

I have a table of employees names and their immunizations. The immunization
information comes from Query 1 of another database which is updated each
week. I have been deleting the contents of the table and replacing it with
the contents of Query 1 each week; the big problem is some employees don't
have their immunization information in the Query 1 it comes from yet another
database and I re-fill in those blanks each week and others are marked N/A.

I would like to construct an update query that will take the "Yes" from
Query 1 and put into the immunization column of table 1 only if that space in
table 1 is N or contains N/A.

Obviously I've never made an update query before and in fact I'd forgotten
about them until this weekend when I was thinking--there has to be an easier
way.

How do I set this up?

TABLE 1:

Employee ID, Employee Name, Department, Immunization (y/n/NA

QUERY 1
Employee ID, Employee Name, Immunization

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

.

Don't worry, any employees already in the table won't be consigned to
oblivion. Of the three 'action' queries I posted the first adds any new
employees, i.e. any in Query 1 with an EmployeeID not already in the table;
the second updates any who do already exist in the table but whose names
might have changed (unlikely, but theoretically possible); the third updates
the Immunization column for any employees whose value in the table is
currently anything but "Y" if the value being imported in Query 1 is "Y".
The rows for other employees, i.e. those already in the table but whose
EmployeeID is not in Query 1, will not be affected at all.

Nevertheless, as with any set update operation, it is of course imperative
that the table is backed up before trying this.

Ken Sheridan
Stafford, England

randlesc wrote:
Thank you. Sorry for the delay, I must have forgotten to check "notify me of
replies."

This looks great. My only question is, there is some data in immunization
that doesn't come from the second query--these are people, about 100, whose
information comes from a variety of systems across the company, mostly legacy
systems--if I've entered this into the main table will these be overwritten
when I run the query because they won't match with what the Query 1 will
return (query 1 will return them as blanks, most times).

Thanks again. I can't wait to try this out.

I'd do this in three stages:

[quoted text clipped - 78 lines]

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1