Populate a field in a query with the value of record

[Ben8765]

Hello,

I would like to make a query (based on a table) do this:

Where the value of FieldA = D, get the value of FieldB of that record, and
populate Expr1 (the entire field) with that value.

FieldA __ FieldB ___ Expr1
__A _______1 ______4
__B_______ 2______ 4
__C_______3______ 4
__D_______ 4______ 4

Idea: Can i store the value of FieldB, where FieldA = D, in a variable, and
then
make Expr1 = that variable?

Thanks,

-Ben

 

[kc-mass]

Hi Ben

Use two queries like :

Qry1

SELECT tblAandB.FieldA, tblAandB.FieldB AS Expr1
FROM tblAandB
WHERE (((tblAandB.FieldA)="D"));

Qry2

SELECT tblAandB.FieldA, tblAandB.FieldB, qryThDValue.Expr1
FROM tblAandB, qryThDValue;

Regards

Kevin

 

[Bob Barrows]

Hello,

I would like to make a query (based on a table) do this:

Where the value of FieldA = D, get the value of FieldB of that
record, and populate Expr1 (the entire field) with that value.

FieldA __ FieldB ___ Expr1
__A _______1 ______4
__B_______ 2______ 4
__C_______3______ 4
__D_______ 4______ 4

Idea: Can i store the value of FieldB, where FieldA = D, in a
variable, and then
make Expr1 = that variable?

Yes. Assuming that FieldA is the primary key so that there can never be more
than one record where FieldA contains "D", there are a couple of ways to do
this. One way is to use a cross join of the table with itself. The sql would
look like this:
UPDATE Table3, Table3 AS D SET Table3.Expr1 = [D].[FieldB]
WHERE (((D.FieldA)="D"));

Another way would be to use a subquery, like this:
UPDATE Table3 SET Expr1 =
(SELECT FieldB FROM Table3 WHERE FieldA='D')

However, it boggles my mind why you would want to do this. It is very, very
rare to need to store a result that can be derived or calculated from the
other data that is already stored in the table. There are many reasons not
to do this, including the problems of maintenance that will arise. A better
plan would be to create a query that provides a calculated field that always
contains the current value of the row where Field_A contains "D". Like this:

SELECT Table3.FieldA, Table3.Field_B, D.FieldB AS FieldBWhereFieldAContainsD
FROM Table3, Table3 AS D
WHERE (((D.FieldA)="D"));

There is no need to store this value.

 

[John Spencer]

You could use the VBA DLookup function to get the value.

DLookup("FieldB","YourTable","FieldA = 'd'")

You can use that as an expression in your query.

You could also use a subquery in the select clause
SELECT FieldA, FieldB
, (SELECT FieldB FROM SomeTable As TEMP WHERE FieldA = "D") as Expr1
FROM SomeTable

If you want to make the change and permanently store the data, you would need
to use an update query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I would like to make a query (based on a table) do this:

Where the value of FieldA = D, get the value of FieldB of that
record, and populate Expr1 (the entire field) with that value.

FieldA __ FieldB ___ Expr1
__A _______1 ______4
__B_______ 2______ 4
__C_______3______ 4
__D_______ 4______ 4

Idea: Can i store the value of FieldB, where FieldA = D, in a
variable, and then
make Expr1 = that variable?

Yes. Assuming that FieldA is the primary key so that there can never be more
than one record where FieldA contains "D", there are a couple of ways to do
this. One way is to use a cross join of the table with itself. The sql would
look like this:
UPDATE Table3, Table3 AS D SET Table3.Expr1 = [D].[FieldB]
WHERE (((D.FieldA)="D"));

Another way would be to use a subquery, like this:
UPDATE Table3 SET Expr1 =
(SELECT FieldB FROM Table3 WHERE FieldA='D')

However, it boggles my mind why you would want to do this. It is very, very
rare to need to store a result that can be derived or calculated from the
other data that is already stored in the table. There are many reasons not
to do this, including the problems of maintenance that will arise. A better
plan would be to create a query that provides a calculated field that always
contains the current value of the row where Field_A contains "D". Like this:

SELECT Table3.FieldA, Table3.Field_B, D.FieldB AS FieldBWhereFieldAContainsD
FROM Table3, Table3 AS D
WHERE (((D.FieldA)="D"));

There is no need to store this value.

 

[Bob Barrows]

Even when I did Access full time I avoided those domain functions. It's
small wonder I never think of them now