Re: Update Query, updating wrong table

Tech-Archive recommends: Fix windows errors by optimizing your registry

No, you don't (actually you can't) union action queries. You would have to
run the 9 queries sequentially.

The reason you are getting zero records to update is that you have no
records that meet the criteria you have set up. IsNull() in Access is a VBA
function that checks to see if a value is Null or not. It returns true or
false. So

tblImportProducts.Each<>IsNull([tblImportProducts].[Each])
Is checking to see if tblImportProducts.Each is equal to True or False

Perhaps you can explain what you are trying to test for?
Do you want to know if tblImportProducts.Each is not Null? That test is
tblImportProducts is Null or

IsNull(tblImportProducts.Each) = False

If you are just testing to see if the fields are not null, then the query
requires all nine sets to be not nullbefore it will return any record to be
updated.

I've aliased the tables to make life easier for me

UPDATE tblTestUpdate as U INNER JOIN tblImportProducts as I
ON U.MaterialNumber = I.MaterialNumber
SET
U.Each = [I]![Each]
, U.[Catalog] =[I]![Catalog]
, U.Manufacturer =[I]![Manufacturer]
, U.Category =[I]![Category]
, U.Description =[I]![Description]
, U.[Sub-Category] =[I]![Sub-Category]
, U.AddedNote =[I]![AddedNote]
, U.Required =[I]![Required]
, U.NoList =[I]![NoList]

WHERE I.Each is not Null
OR I.Catalog is not Null
OR I.Manufacturer is not Null
OR I.Category is Not Null
OR I.Description is Not Null
OR I.[Sub-Category] is not Null
OR I.AddedNote is Not Null
OR I.[Required] is Not Null
OR I.NoList is Not Null

IF you want U.Each replace with I.Each unless I.Each is null you can use the
NZ Function, which says to use the first value unless it is null and then
use the second value.
For example.
U.Each = NZ([I].[Each],[U].[Each])

So you could rewrite the above to do that for each item you are updating.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

<matt17@xxxxxxxxx> wrote in message
news:1170430927.450278.229110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 2, 8:54 am, "mat...@xxxxxxxxx" <mat...@xxxxxxxxx> wrote:
On Feb 2, 8:07 am, "mat...@xxxxxxxxx" <mat...@xxxxxxxxx> wrote:



Yeah I have had the set switched around before and all that did was
change where it was grabbing it from an updating the values in the
same spot.

Let me try to explain myself better, here are the values I am trying
to grab:

tblImportProducts!Each:
I set all the values to-
99999
99999
99999
99999

tblTestUpdate.Each:
I know the values are-
42.3
57.7
98.2
26.4

So when I reversed the SET it updated to the right table but grabbed
the wrong values, and before I changed the SET it would grab the right
values but it updated the wrong table. So, I guess I am closer to the
right combination, but I still need it to grab from the right table.

Thanks,
Matt Pierringer

I don't know why it didn't work before, but it works now... Thanks!!!

Ok, well my problem is now I added in all the other values to have it
update, I followed the same format I currently have. When I go to
update it displays "Update 0 Rows" when I know that there is
information that is different... do I have to make 9 separate update
queries and then make a Union query to join them all together? I
guess I will get started on that, but it would be nice if I didn't
have to.

SQL:
UPDATE tblTestUpdate INNER JOIN tblImportProducts ON
tblTestUpdate.MaterialNumber = tblImportProducts.MaterialNumber SET
tblTestUpdate.Each = tblImportProducts!Each, tblTestUpdate.[Catalog] =
[tblImportProducts]![Catalog], tblTestUpdate.Manufacturer =
[tblImportProducts]![Manufacturer], tblTestUpdate.Category =
[tblImportProducts]![Category], tblTestUpdate.Description =
[tblImportProducts]![Description], tblTestUpdate.[Sub-Category] =
[tblImportProducts]![Sub-Category], tblTestUpdate.AddedNote =
[tblImportProducts]![AddedNote], tblTestUpdate.Required =
[tblImportProducts]![Required], tblTestUpdate.NoList =
[tblImportProducts]![NoList]
WHERE (((tblImportProducts.Each)<>IsNull([tblImportProducts].[Each]))
AND ((tblImportProducts.Catalog)<>IsNull([tblImportProducts].
[Catalog])) AND
((tblImportProducts.Manufacturer)<>IsNull([tblImportProducts].
[Manufacturer])) AND
((tblImportProducts.Category)<>IsNull([tblImportProducts].[Category]))
AND ((tblImportProducts.Description)<>IsNull([tblImportProducts].
[Description])) AND ((tblImportProducts.[Sub-
Category])<>IsNull([tblImportProducts].[Sub-Category])) AND
((tblImportProducts.AddedNote)<>IsNull([tblImportProducts].
[AddedNote])) AND
((tblImportProducts.Required)<>IsNull([tblImportProducts].[Required]))
AND ((tblImportProducts.NoList)<>IsNull([tblImportProducts].
[NoList])));


Thanks,
Matt Pierringer



.

Relevant Pages

  • Re: Another Database SQL Update question
    ... besides queries that set it. ... You have to grab the value *immediately* after ... a query or you might get a _Tally for some other action. ... could throw one for a loop due to an OKL triggering. ...
    (microsoft.public.fox.programmer.exchange)
  • Query appends incorrect data
    ... I have several archive tables and queries. ... Items appends to Archive_Items (Table inner joined with a ... then the archive immediately runs to grab any outdated information. ...
    (microsoft.public.access.queries)
  • Re: Adding colored font to queries
    ... but it's where you see the icons ... Just grab that, and move it up, so you've got room ... what goes with what (I use the properties field for that, ... >>to keep track of which queries are related to each other. ...
    (microsoft.public.access.queries)
  • Catching error on insert statement
    ... I have a piece of code that queries an existing table to retrieve the last ... that procedure is that someone else could grab the same number and use it ... Prev by Date: ...
    (microsoft.public.access.modulesdaovba)