Union Query dramas (1 Viewer)

[Pap265]

Why won't you consider the simpler alternative?

June7 which alternative?

 

[June7]

See posts 10, 12, 17.

 

[Galaxiom]

This is going to get very ugly when extended to the nth variation. Running multiple nearly identical queries (or Dcounts) isn't a something that would normally be done.

Moreover, updating values in tables with counts from queries is generally a wrong way to manage the requirement because it is storing calculated values.

I think you are heading down entirely the wrong path.

 

[Pap265]

It worked ! !! It took a few minutes as I had 19,000 records to analyze. Many thanks to arnelgp. I have a couple of other "Long Winded" queries that need better and more efficient programming but I won't bother anyone one for now. Have a great weekend everyone here...


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

 

[Galaxiom]

It worked ! !! It took a few minutes as I had 19,000 records to analyze.

Hopefully it isn't something you run more than very occasionally as that is pretty poor performance on a query working with so few records.

 

[theDBguy]

It worked ! !! It took a few minutes as I had 19,000 records to analyze. Many thanks to arnelgp. I have a couple of other "Long Winded" queries that need better and more efficient programming but I won't bother anyone one for now. Have a great weekend everyone here...


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

Hi. Glad to hear you're okay for now. Good luck with your project.

 

[June7]

Would probably be a lot faster as just SELECT aggregate query calculating this value instead of updating into table. VBA procedure might even be faster. Domain aggregate functions can slow performance but that is really bad for so few records.