Union Query dramas (1 Viewer)

[Pap265]

Hello good people,

I've been having difficulty with two of queries that I wish to join together..... maybe I am just going about it the wrong way :/


UPDATE qryStatFillingInStat8 SET qryStatFillingInStat8.Stat8 = "1 of 1"
WHERE (((qryStatFillingInStat8.OptionCodes) In (SELECT [OptionCodes] FROM [qryStatFillingInStat8] As Tmp GROUP BY [OptionCodes] HAVING Count(*)=1 )));
Union
UPDATE qryStatFillingInStat8 SET qryStatFillingInStat8.Stat8 = "1 of 2"
WHERE (((qryStatFillingInStat8.OptionCodes) In (SELECT [OptionCodes] FROM [qryStatFillingInStat8] As Tmp GROUP BY [OptionCodes] HAVING Count(*)=2 )));

any help or suggetions appricated .....regards Bill (Australia)

 

[theDBguy]

Hi. Welcome to AWF!

Unfortunately, you cannot UNION two action queries.

 

[Pap265]

Hi DB, well thats I can not get anywhere...... what if, I union them into a Select query and then change it to a UPDATE query?

 

[arnelgp]

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = Switch(DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'")=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'")=2,"1 of 2",
True,qryStatFillingInStat8.Stat8);

the sql code will work if OptionCode is Text.
If it is Numeric, change it to:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = Switch(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=2,"1 of 2",
True,qryStatFillingInStat8.Stat8);

 

[June7]

An UPDATE action cannot involve a non-editable query. A UNION is non-editable.

Saving calculated (especially aggregate) data is usually ill-advised. If you can calculate for UPDATE then can calculate when needed.

 

[Isaac]

An UPDATE action cannot involve a non-editable query

That's not correct. Attached db demonstrates an update query that involves a non-updateable query.

@Pap265 Can you explain what it is you are trying to do ? If you have 2 update queries, both of which you are satisfied with, you can simply run one after the other to accomplish the results of both. I am struggling conceptually speaking with what you might be intended by even wanting to do this--but I'd rather ask than guess.

 

[plog]

So, you are trying to UNION two UPDATE queries which each use a similar aggregate query in their WHERE clause both of which are based on the query you are trying to update? That's an Inception-level mess of a query you have there.

I suggest you tell us what you are ultimately trying to accomplish. Don't reference this query or even qryStatFillingInStat8 at all. Tell us about the tables and what this database does and what this specific task you are trying to achieve does for you.

 

[June7]

That's not correct. Attached db demonstrates an update query that involves a non-updateable query.

Okay, stand corrected. Certainly never has worked for me with an aggregate query.

 

[Pap265]

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = Switch(DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'")=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'")=2,"1 of 2",
True,qryStatFillingInStat8.Stat8);

the sql code will work if OptionCode is Text.
If it is Numeric, change it to:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = Switch(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=2,"1 of 2",
True,qryStatFillingInStat8.Stat8);

Hi arnelgp, that works ! thank you very much if I wanted to continue more steps for "1 of 3" "1 of 4" "1 of 5" "1 of 6" could you add the lines for me please and i will see the pattern for the rest I need to do....... thank you for your help.

 

[June7]

Access query object has a limit of 1024 characters in each cell of design grid. Sounds like you could hit that limit with repeating that complete DCount in Switch(). How high could a count get? The following syntax doesn't care, any count will work.

UPDATE qryStatFillingInStat8 SET Stat8 = "1 of " & DCount("*", "qryStatFillingInStat8", "OptionCodes=" & [OptionCodes]);

 

[arnelgp]

see this pattern:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 =
Switch(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=2,"1 of 2",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=3,"1 of 3",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=4,"1 of 4",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=5,"1 of 5",
True,qryStatFillingInStat8.Stat8);

 

[arnelgp]

or

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = IIF(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=0,qryStatFillingInStat8.Stat8,
"1 of " & DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes]));

 

[Pap265]

So, you are trying to UNION two UPDATE queries which each use a similar aggregate query in their WHERE clause both of which are based on the query you are trying to update? That's an Inception-level mess of a query you have there.

I suggest you tell us what you are ultimately trying to accomplish. Don't reference this query or even qryStatFillingInStat8 at all. Tell us about the tables and what this database does and what this specific task you are trying to achieve does for you.

Ok, I'll try to explain....

I have the Options field with duplicate data, and I want to populate the Stat8 filed with "1 of 35" duplicates for each of the 35 records found that are the same in the Options field. Hopei have explained it ok.

 

[Pap265]

see this pattern:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 =
Switch(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=2,"1 of 2",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=3,"1 of 3",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=4,"1 of 4",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=5,"1 of 5",
True,qryStatFillingInStat8.Stat8);

thank you arnelgp I will give it a try

 

[June7]

Should consider alternate syntax in posts 10 and 12.

 

[Pap265]

see this pattern:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 =
Switch(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=2,"1 of 2",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=3,"1 of 3",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=4,"1 of 4",
DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=5,"1 of 5",
True,qryStatFillingInStat8.Stat8);

I'm getting a Type Conversion Error........ All fields involved are TEXT

 

[arnelgp]

you add "'" delimiter if it is Text:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = IIF(DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'")=0,qryStatFillingInStat8.Stat8,
"1 of " & DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'"));

 

[Pap265]

you add "'" delimiter if it is Text:

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 = IIF(DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'")=0,qryStatFillingInStat8.Stat8,
"1 of " & DCount("1","qryStatFillingInStat8","OptionCodes='" & [OptionCodes] & "'"));

sorry arnelgp, im getting a syntax error now.....

 

[Pap265]

UPDATE qryStatFillingInStat8
SET qryStatFillingInStat8.Stat8 =
Switch(DCount("1","qryStatFillingInStat8","OptionCodes=" & [OptionCodes])=1,"1 of 1",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=2,"1 of 2",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=3,"1 of 3",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=4,"1 of 4",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=5,"1 of 5",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=6,"1 of 6",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=7,"1 of 7",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=8,"1 of 8",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=9,"1 of 9",
DCount("1","qryStatFillingInStat8","OptionCodes=’" & [OptionCodes]) & “’”)=10,"1 of 10",

True,qryStatFillingInStat8.Stat8);

 

[June7]

Why won't you consider the simpler alternative?