Query Wizard help...

[Joe Mac]

All...

I generated a query using the Query Design tool...
I would like to update a field in one table with a value from a second table
based on a common field (albeit unique names) link between the tables...
below is the SQL that was generated... when I run the query I receive an
"Invalid Argument" message box... ANY HELP WOULD BE APPRECIATED

UPDATE [All TTP WOs May thru June], [TTP Stores] SET [All TTP WOs May thru
June].[DMA_Name] = [TTP Stores]![DMA_NAME]
WHERE (([All TTP WOs May thru June]![Storekey]=[TTP Stores]![STORE_NBR]));--

Joe Mac

 

[Steve Schapel]

Joe,

I hesitate to assist here, for fear of being accused of being an
accessory to crime. It would appear that you are using some
un-normalised ideas here. I would be pretty certain that you really
shouldn't have a DMA_Name field in the [All TTP WOs May thru June] table
at all in any case. Let us know if you want to explore this aspect
ralting to your database design.

So with that proviso... try it like this:

UPDATE [All TTP WOs May thru June] INNER JOIN [TTP Stores] ON [All TTP
WOs May thru June].[Storekey]=[TTP Stores].[STORE_NBR] SET [All TTP WOs
May thru June].[DMA_Name] = [TTP Stores].[DMA_NAME]

 

[John W. Vinson]

I generated a query using the Query Design tool...
I would like to update a field in one table with a value from a second table
based on a common field (albeit unique names) link between the tables...
below is the SQL that was generated... when I run the query I receive an
"Invalid Argument" message box... ANY HELP WOULD BE APPRECIATED

You need to *join* the two tables. In the query grid this qould be done by
dragging the linking field name from one table icon to the other. I'm GUESSING
here, since I'm not certain which field is the actual link, but back up your
database (in case this updates the wrong thing!) and try:

UPDATE [All TTP WOs May thru June] INNER JOIN[TTP Stores]
ON [All TTP WOs May thru June]![Storekey]=[TTP Stores]![STORE_NBR]
SET [All TTP WOs May thru June].[DMA_Name] = [TTP Stores]![DMA_NAME];

Assuming that Storekey is the Primary Key this should work.

One question: are you sure that you should have DMA_NAME in both tables? It
appears to be redundant. You may be able to just use a query to look it up if
needed.

 

[Joe Mac]

No need to hesitate, that is what is great about these Discussion Groups...
I'm no expert at all in database design, nor in the use of the tools I have
"access" to (no pun intended)... while you don't agree with the update
processing, I do see the fact that I've got a non-normalised data set, your
response did trigger the error of my ways... there is no need to update the
[All TTP WOs May thru June] table to include the DMA_Name, I believe that I
can just update the values that I really want to update based upon the
condition of the DMA_Name of alternating stores being consistent when the
stores are not consistent but both exist in the same DMA_Name (Market); i.e.,
there will be a third table to include to make this happen
1) TTP Stores 2) All TTP WOs May thru June 3) Combined Billed

Where table 2 and table 3 each have a store number which is not equal to one
another but both store exist in the same DMA_Name (Market) and table 1
contains the store numbers and the DMA_Name...

I'll give it a try and see how I make out, before my next response... thank
you for the feedback-
Joe
--

Joe Mac

Joe,

I hesitate to assist here, for fear of being accused of being an
accessory to crime. It would appear that you are using some
un-normalised ideas here. I would be pretty certain that you really
shouldn't have a DMA_Name field in the [All TTP WOs May thru June] table
at all in any case. Let us know if you want to explore this aspect
ralting to your database design.

So with that proviso... try it like this:

UPDATE [All TTP WOs May thru June] INNER JOIN [TTP Stores] ON [All TTP
WOs May thru June].[Storekey]=[TTP Stores].[STORE_NBR] SET [All TTP WOs
May thru June].[DMA_Name] = [TTP Stores].[DMA_NAME]

--
Steve Schapel, Microsoft Access MVP

All...

I generated a query using the Query Design tool...
I would like to update a field in one table with a value from a second table
based on a common field (albeit unique names) link between the tables...
below is the SQL that was generated... when I run the query I receive an
"Invalid Argument" message box... ANY HELP WOULD BE APPRECIATED

UPDATE [All TTP WOs May thru June], [TTP Stores] SET [All TTP WOs May thru
June].[DMA_Name] = [TTP Stores]![DMA_NAME]
WHERE (([All TTP WOs May thru June]![Storekey]=[TTP Stores]![STORE_NBR]));--

Joe Mac

 

[Steve Schapel]

Joe,

If I understand what you are doing, I think the key point is that if you
have a [STORE_NBR] field in your [TTP Stores] master table, and this
uniquely identifies each record, and each is associated (as it appears
to be) in that table with a [DMA_NAME], then... well, any other table
with a [STORE_NBR] field (or equivalent, such as your [Storekey] field),
you do not need a [DMA_NAME] field. The database already "knows" what
the [DMA_NAME] value is, based on the [STORE_NBR] or equivalent, and you
can easily retrieve the [DMA_NAME] whenever you need it, via a query.
Which saves you having to store it (no pun intended) redundantly. Which
is one of the points of using a relational database in the first place.
See?