Update query

[KHogwood-Thompson]

I have a query that produces a list of nominal codes and YTD values for a
period based on a parameter in a form.

I would like the total of the YTD values to be used in an update query to
change the value of a nominal code in a table.

The query that produces the list has the following SQL:

SELECT DISTINCTROW [P&L ALL].[NOMINAL REF], [P&L ALL].PERIOD, [P&L ALL].YTD
FROM [P&L ALL];

I would like an update to query to take the total of the YTD column above
and update the YTD field on a table called [NOMINAL YTD SUMMARY TABLE] for
one nominal code called "4800", the field name in the table being "NOM REF"

K Hogwood-Thompson

 

[Dale Fye]

What have you tried so far?

Make sure you are working with a copy.

If your orginal query is called Query1, then you might try something like:

Update [Nominal YTD Summary Table]
SET [YTD] = DLOOKUP("YTD", "Query1", "[Nominal Ref] = 4800")
WHERE [Nominal YTD Summary Table].[Nom Ref] = 4800

This assumes the 4800 is numeric, if it is text, then wrap it in single
quotes ('4800')

Hope you don't mind, but why are you using DISTINCTROW in this query? Is
there any chance that you could have duplicate values in the [Nominal Ref]
field in the [P&L All] table?


HTH



--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[KHogwood-Thompson]

Hi Dale,

Thanks for this, yes I am using a copy of the table.

The problem with this is that the query does not contain the nominal code
"4800", what I want is for the total of all nominal codes in the query to be
used for the update of table [Nominal YTD Summary Table].

Not sure why I have DistinctRow, I have deleted this from the SQL and I
still get the same result !!
--
K Hogwood-Thompson

What have you tried so far?

Make sure you are working with a copy.

If your orginal query is called Query1, then you might try something like:

Update [Nominal YTD Summary Table]
SET [YTD] = DLOOKUP("YTD", "Query1", "[Nominal Ref] = 4800")
WHERE [Nominal YTD Summary Table].[Nom Ref] = 4800

This assumes the 4800 is numeric, if it is text, then wrap it in single
quotes ('4800')

Hope you don't mind, but why are you using DISTINCTROW in this query? Is
there any chance that you could have duplicate values in the [Nominal Ref]
field in the [P&L All] table?


HTH



--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a query that produces a list of nominal codes and YTD values for a
period based on a parameter in a form.

I would like the total of the YTD values to be used in an update query to
change the value of a nominal code in a table.

The query that produces the list has the following SQL:

SELECT DISTINCTROW [P&L ALL].[NOMINAL REF], [P&L ALL].PERIOD, [P&L ALL].YTD
FROM [P&L ALL];

I would like an update to query to take the total of the YTD column above
and update the YTD field on a table called [NOMINAL YTD SUMMARY TABLE] for
one nominal code called "4800", the field name in the table being "NOM REF"

K Hogwood-Thompson