Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to base an update query on the results of another query?
Not my goal in life to do this, but I'm having trouble getting the correct
results in my update query.

BR
Claes
 
It depends on the nature of the "another" query and HOW you want to base on
the results. Please be more specific as to what you want to do.
 
I just tried to make one now.
Heres the code.

UPDATE 3ExpFöljesedelSpec SET 3ExpFöljesedelSpec.Kategori =
[3ExpQUpdatePart1]![Kategori2], 3ExpFöljesedelSpec.UtökatPris =
[3ExpQUpdatePart1]![UtökatPris2], 3ExpFöljesedelSpec.KNNummer =
IIf([Kategori] In
("Children"),[3ExpQUpdatePart1]![SistaförKN2],[3ExpQUpdatePart1]![SistaförHSCode]),
3ExpFöljesedelSpec.KN2 = [3ExpQUpdatePart1]![SistaförKN2],
3ExpFöljesedelSpec.Ursprungsland =
[3ExpQUpdatePart1]![SistaförUrsprungsland], 3ExpFöljesedelSpec.Varuslag =
[3ExpQUpdatePart1]![SistaförVaruslag]
WHERE
((([3ExpFöljesedelSpec].Följesedel)=[Forms]![3ExpQFöljesedel]![Följesedel]));

I get parameterboxes asking me to fill in values.
Why is that, when I get the correct data when I pre-view.

"TedMi" skrev:
 
For an update query, you would need to join 3ExpQUpdatePart1 to
3ExpFöljesedelSpec in some manner. Is there a field or fields that can be
used to define the relationship between the two.

That might look something like the following. I've aliased the tables to
make writing the query a bit easier

UPDATE 3ExpFöljesedelSpec As S INNER JOIN 3ExpQUpdatePart1 as Q
ON S.SomeField = Q.SomeField

SET S.Kategori = [Q].[Kategori2]
, S.UtökatPris = [Q].[UtökatPris2]
, S.KNNummer = IIf([Kategori] In
("Children"),[Q].[SistaförKN2],[Q].[SistaförHSCode])
, S.KN2 = [Q].[SistaförKN2]
, S.Ursprungsland = [Q].[SistaförUrsprungsland]
, S.Varuslag = [Q].[SistaförVaruslag]

WHERE .Följesedel=[Forms]![3ExpQFöljesedel]![Följesedel]

If you can write a SELECT query to shows all the data for both table and
query in the same row, then you can usually turn the select query into an
update query.

Claes D said:
I just tried to make one now.
Heres the code.

UPDATE 3ExpFöljesedelSpec SET 3ExpFöljesedelSpec.Kategori =
[3ExpQUpdatePart1]![Kategori2], 3ExpFöljesedelSpec.UtökatPris =
[3ExpQUpdatePart1]![UtökatPris2], 3ExpFöljesedelSpec.KNNummer =
IIf([Kategori] In
("Children"),[3ExpQUpdatePart1]![SistaförKN2],[3ExpQUpdatePart1]![SistaförHSCode]),
3ExpFöljesedelSpec.KN2 = [3ExpQUpdatePart1]![SistaförKN2],
3ExpFöljesedelSpec.Ursprungsland =
[3ExpQUpdatePart1]![SistaförUrsprungsland], 3ExpFöljesedelSpec.Varuslag =
[3ExpQUpdatePart1]![SistaförVaruslag]
WHERE
((([3ExpFöljesedelSpec].Följesedel)=[Forms]![3ExpQFöljesedel]![Följesedel]));

I get parameterboxes asking me to fill in values.
Why is that, when I get the correct data when I pre-view.

"TedMi" skrev:
It depends on the nature of the "another" query and HOW you want to base
on
the results. Please be more specific as to what you want to do.
Click to expand...
Click to expand...
 
Back
Top