Update query with IIfs?

J

juicegully

I have two tables, tbBulk and tbAtts. Some sample data include:

tbBulk:
Name, AttrName, AttrValue (Field names)
Apple, Num, 1
Apple, Addr1, 2
Apple, Addr2, 3
Banana, Dwg, 4
Banana, Addr1, 5
Banana, Addr2, 6

tbAtts is initially blank. I run an append query which populates tbAtts to
look like:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, _
Banana, 4, _

where the underscore is an empty field.

The append query is:

INSERT INTO Atts ( Name, DrawingNo )
SELECT [Name], [AttrValue]
FROM tbBulk
WHERE (AttrName='Num' Or AttrName='Dwg');

So now I need to fill in the AddrNo column in tbAtts. The requirements are
that for each fruit, if the AttrName is Num, the Attrvalue to be used should
come from the row where AttrName is Addr1. If the AttrName is Dwg, the
Attrvalue to be used should come from the row where AttrName is Addr2.

The final data should look like this:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, 2
Banana, 4, 6

I'm really stuck! So far my update query looks like this:

UPDATE tbAtts INNER JOIN tbBulk ON tbAtts.Name=tbBulk.Name SET tbAtts.AddrNo
= tbBulk.AttrValue
WHERE tbBulk.AttrName=IIF(tbBulk.AttrName='Num','Addr1','Addr2') And Not
IsNull(tbBulk.AttrValue) And tbBulk.AttrValue<>'';

Any help is greatly appreciated!

Thank you.
 
G

giorgio rancati

Hi juicegully,

try this
----
UPDATE (tbAtts INNER JOIN tbBulk
ON tbAtts.Name = tbBulk.Name
AND tbAtts.DrawingNo = tbBulk.AttrValue)
INNER JOIN tbBulk AS tbBulk_1 ON tbBulk.Name = tbBulk_1.Name
SET tbAtts.AddrNo = [tbBulk_1].[AttrValue]
WHERE tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Num","Addr1",Null) Or
tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Dwg","Addr2",Null);
 
J

juicegully

Hi Giorgio,

I tried it and I get a dialog box ("Enter parameter value") asking me for
tbBulk_1.AttrValue. What do you think I should do now?

Thanks


giorgio rancati said:
Hi juicegully,

try this
----
UPDATE (tbAtts INNER JOIN tbBulk
ON tbAtts.Name = tbBulk.Name
AND tbAtts.DrawingNo = tbBulk.AttrValue)
INNER JOIN tbBulk AS tbBulk_1 ON tbBulk.Name = tbBulk_1.Name
SET tbAtts.AddrNo = [tbBulk_1].[AttrValue]
WHERE tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Num","Addr1",Null) Or
tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Dwg","Addr2",Null);
----

Bye
--
Giorgio Rancati
[Office Access MVP]


juicegully said:
I have two tables, tbBulk and tbAtts. Some sample data include:

tbBulk:
Name, AttrName, AttrValue (Field names)
Apple, Num, 1
Apple, Addr1, 2
Apple, Addr2, 3
Banana, Dwg, 4
Banana, Addr1, 5
Banana, Addr2, 6

tbAtts is initially blank. I run an append query which populates tbAtts to
look like:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, _
Banana, 4, _

where the underscore is an empty field.

The append query is:

INSERT INTO Atts ( Name, DrawingNo )
SELECT [Name], [AttrValue]
FROM tbBulk
WHERE (AttrName='Num' Or AttrName='Dwg');

So now I need to fill in the AddrNo column in tbAtts. The requirements are
that for each fruit, if the AttrName is Num, the Attrvalue to be used
should
come from the row where AttrName is Addr1. If the AttrName is Dwg, the
Attrvalue to be used should come from the row where AttrName is Addr2.

The final data should look like this:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, 2
Banana, 4, 6

I'm really stuck! So far my update query looks like this:

UPDATE tbAtts INNER JOIN tbBulk ON tbAtts.Name=tbBulk.Name SET
tbAtts.AddrNo
= tbBulk.AttrValue
WHERE tbBulk.AttrName=IIF(tbBulk.AttrName='Num','Addr1','Addr2') And Not
IsNull(tbBulk.AttrValue) And tbBulk.AttrValue<>'';

Any help is greatly appreciated!

Thank you.
Click to expand...
Click to expand...
 
G

giorgio rancati

I used the fields name from your post.
Check your fields name.
Here the sample
----
http://web.tiscali.it/giorgiorancati/msaccess/ng/juicegully.zip
----

bye
--
Giorgio Rancati
[Office Access MVP]

juicegully said:
Hi Giorgio,

I tried it and I get a dialog box ("Enter parameter value") asking me for
tbBulk_1.AttrValue. What do you think I should do now?

Thanks


giorgio rancati said:
Hi juicegully,

try this
----
UPDATE (tbAtts INNER JOIN tbBulk
ON tbAtts.Name = tbBulk.Name
AND tbAtts.DrawingNo = tbBulk.AttrValue)
INNER JOIN tbBulk AS tbBulk_1 ON tbBulk.Name = tbBulk_1.Name
SET tbAtts.AddrNo = [tbBulk_1].[AttrValue]
WHERE tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Num","Addr1",Null) Or
tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Dwg","Addr2",Null);
----

Bye
--
Giorgio Rancati
[Office Access MVP]


"juicegully" <[email protected]> ha scritto nel messaggio
I have two tables, tbBulk and tbAtts. Some sample data include:

tbBulk:
Name, AttrName, AttrValue (Field names)
Apple, Num, 1
Apple, Addr1, 2
Apple, Addr2, 3
Banana, Dwg, 4
Banana, Addr1, 5
Banana, Addr2, 6

tbAtts is initially blank. I run an append query which populates tbAtts to
look like:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, _
Banana, 4, _

where the underscore is an empty field.

The append query is:

INSERT INTO Atts ( Name, DrawingNo )
SELECT [Name], [AttrValue]
FROM tbBulk
WHERE (AttrName='Num' Or AttrName='Dwg');

So now I need to fill in the AddrNo column in tbAtts. The requirements are
that for each fruit, if the AttrName is Num, the Attrvalue to be used
should
come from the row where AttrName is Addr1. If the AttrName is Dwg, the
Attrvalue to be used should come from the row where AttrName is Addr2.

The final data should look like this:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, 2
Banana, 4, 6

I'm really stuck! So far my update query looks like this:

UPDATE tbAtts INNER JOIN tbBulk ON tbAtts.Name=tbBulk.Name SET
tbAtts.AddrNo
= tbBulk.AttrValue
WHERE tbBulk.AttrName=IIF(tbBulk.AttrName='Num','Addr1','Addr2') And Not
IsNull(tbBulk.AttrValue) And tbBulk.AttrValue<>'';

Any help is greatly appreciated!

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
J

juicegully

Thank you so much Giorgio! It was perfect :)

giorgio rancati said:
I used the fields name from your post.
Check your fields name.
Here the sample
----
http://web.tiscali.it/giorgiorancati/msaccess/ng/juicegully.zip
----

bye
--
Giorgio Rancati
[Office Access MVP]

juicegully said:
Hi Giorgio,

I tried it and I get a dialog box ("Enter parameter value") asking me for
tbBulk_1.AttrValue. What do you think I should do now?

Thanks


giorgio rancati said:
Hi juicegully,

try this
----
UPDATE (tbAtts INNER JOIN tbBulk
ON tbAtts.Name = tbBulk.Name
AND tbAtts.DrawingNo = tbBulk.AttrValue)
INNER JOIN tbBulk AS tbBulk_1 ON tbBulk.Name = tbBulk_1.Name
SET tbAtts.AddrNo = [tbBulk_1].[AttrValue]
WHERE tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Num","Addr1",Null) Or
tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Dwg","Addr2",Null);
----

Bye
--
Giorgio Rancati
[Office Access MVP]


"juicegully" <[email protected]> ha scritto nel messaggio
I have two tables, tbBulk and tbAtts. Some sample data include:

tbBulk:
Name, AttrName, AttrValue (Field names)
Apple, Num, 1
Apple, Addr1, 2
Apple, Addr2, 3
Banana, Dwg, 4
Banana, Addr1, 5
Banana, Addr2, 6

tbAtts is initially blank. I run an append query which populates tbAtts to
look like:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, _
Banana, 4, _

where the underscore is an empty field.

The append query is:

INSERT INTO Atts ( Name, DrawingNo )
SELECT [Name], [AttrValue]
FROM tbBulk
WHERE (AttrName='Num' Or AttrName='Dwg');

So now I need to fill in the AddrNo column in tbAtts. The requirements are
that for each fruit, if the AttrName is Num, the Attrvalue to be used
should
come from the row where AttrName is Addr1. If the AttrName is Dwg, the
Attrvalue to be used should come from the row where AttrName is Addr2.

The final data should look like this:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, 2
Banana, 4, 6

I'm really stuck! So far my update query looks like this:

UPDATE tbAtts INNER JOIN tbBulk ON tbAtts.Name=tbBulk.Name SET
tbAtts.AddrNo
= tbBulk.AttrValue
WHERE tbBulk.AttrName=IIF(tbBulk.AttrName='Num','Addr1','Addr2') And Not
IsNull(tbBulk.AttrValue) And tbBulk.AttrValue<>'';

Any help is greatly appreciated!

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
J

juicegully

Hi Giorgio,

Is tbBulk_1 being created dynamically? and does it just deleted after the
query is run?

Thanks

giorgio rancati said:
I used the fields name from your post.
Check your fields name.
Here the sample
----
http://web.tiscali.it/giorgiorancati/msaccess/ng/juicegully.zip
----

bye
--
Giorgio Rancati
[Office Access MVP]

juicegully said:
Hi Giorgio,

I tried it and I get a dialog box ("Enter parameter value") asking me for
tbBulk_1.AttrValue. What do you think I should do now?

Thanks


giorgio rancati said:
Hi juicegully,

try this
----
UPDATE (tbAtts INNER JOIN tbBulk
ON tbAtts.Name = tbBulk.Name
AND tbAtts.DrawingNo = tbBulk.AttrValue)
INNER JOIN tbBulk AS tbBulk_1 ON tbBulk.Name = tbBulk_1.Name
SET tbAtts.AddrNo = [tbBulk_1].[AttrValue]
WHERE tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Num","Addr1",Null) Or
tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Dwg","Addr2",Null);
----

Bye
--
Giorgio Rancati
[Office Access MVP]


"juicegully" <[email protected]> ha scritto nel messaggio
I have two tables, tbBulk and tbAtts. Some sample data include:

tbBulk:
Name, AttrName, AttrValue (Field names)
Apple, Num, 1
Apple, Addr1, 2
Apple, Addr2, 3
Banana, Dwg, 4
Banana, Addr1, 5
Banana, Addr2, 6

tbAtts is initially blank. I run an append query which populates tbAtts to
look like:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, _
Banana, 4, _

where the underscore is an empty field.

The append query is:

INSERT INTO Atts ( Name, DrawingNo )
SELECT [Name], [AttrValue]
FROM tbBulk
WHERE (AttrName='Num' Or AttrName='Dwg');

So now I need to fill in the AddrNo column in tbAtts. The requirements are
that for each fruit, if the AttrName is Num, the Attrvalue to be used
should
come from the row where AttrName is Addr1. If the AttrName is Dwg, the
Attrvalue to be used should come from the row where AttrName is Addr2.

The final data should look like this:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, 2
Banana, 4, 6

I'm really stuck! So far my update query looks like this:

UPDATE tbAtts INNER JOIN tbBulk ON tbAtts.Name=tbBulk.Name SET
tbAtts.AddrNo
= tbBulk.AttrValue
WHERE tbBulk.AttrName=IIF(tbBulk.AttrName='Num','Addr1','Addr2') And Not
IsNull(tbBulk.AttrValue) And tbBulk.AttrValue<>'';

Any help is greatly appreciated!

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
G

giorgio rancati

no, tbBulk_1 is the tbBulk alias

tbBulk_1 doesn't exists in the database.

Bye
--
Giorgio Rancati
[Office Access MVP]

juicegully said:
Hi Giorgio,

Is tbBulk_1 being created dynamically? and does it just deleted after the
query is run?

Thanks

giorgio rancati said:
I used the fields name from your post.
Check your fields name.
Here the sample
----
http://web.tiscali.it/giorgiorancati/msaccess/ng/juicegully.zip
----

bye
--
Giorgio Rancati
[Office Access MVP]

"juicegully" <[email protected]> ha scritto nel messaggio
Hi Giorgio,

I tried it and I get a dialog box ("Enter parameter value") asking me for
tbBulk_1.AttrValue. What do you think I should do now?

Thanks


:

Hi juicegully,

try this
----
UPDATE (tbAtts INNER JOIN tbBulk
ON tbAtts.Name = tbBulk.Name
AND tbAtts.DrawingNo = tbBulk.AttrValue)
INNER JOIN tbBulk AS tbBulk_1 ON tbBulk.Name = tbBulk_1.Name
SET tbAtts.AddrNo = [tbBulk_1].[AttrValue]
WHERE tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Num","Addr1",Null) Or
tbBulk_1.AttrName=IIf([tbBulk].[AttrName]="Dwg","Addr2",Null);
----

Bye
--
Giorgio Rancati
[Office Access MVP]


"juicegully" <[email protected]> ha scritto nel messaggio
I have two tables, tbBulk and tbAtts. Some sample data include:

tbBulk:
Name, AttrName, AttrValue (Field names)
Apple, Num, 1
Apple, Addr1, 2
Apple, Addr2, 3
Banana, Dwg, 4
Banana, Addr1, 5
Banana, Addr2, 6

tbAtts is initially blank. I run an append query which populates tbAtts to
look like:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, _
Banana, 4, _

where the underscore is an empty field.

The append query is:

INSERT INTO Atts ( Name, DrawingNo )
SELECT [Name], [AttrValue]
FROM tbBulk
WHERE (AttrName='Num' Or AttrName='Dwg');

So now I need to fill in the AddrNo column in tbAtts. The
Click to expand...
requirements
are
that for each fruit, if the AttrName is Num, the Attrvalue to be used
should
come from the row where AttrName is Addr1. If the AttrName is Dwg, the
Attrvalue to be used should come from the row where AttrName is Addr2.

The final data should look like this:

tbAtts:
Name, DrawingNo, AddrNo
Apple, 1, 2
Banana, 4, 6

I'm really stuck! So far my update query looks like this:

UPDATE tbAtts INNER JOIN tbBulk ON tbAtts.Name=tbBulk.Name SET
tbAtts.AddrNo
= tbBulk.AttrValue
WHERE tbBulk.AttrName=IIF(tbBulk.AttrName='Num','Addr1','Addr2')
Click to expand...
And
Not
IsNull(tbBulk.AttrValue) And tbBulk.AttrValue<>'';

Any help is greatly appreciated!

Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combining fields in query 2
Update Query Question 3
Update query with autonumber field 4
Update Query with Parameter 3
Help required in Update query 2
Date update in append query 6
merge with Access can't find query 0
Update Table Field Value with SQL Query 2

Top