Dynamically Build SQL string in Access

Question

I have a query called FilingHeaderQry in MS Access.

Due to some crosstab joins, the number of columns are constantly changing based on the crosstab and a lot are duplicated.

I would like to use some VBA to dynamically build the select statement that will call only the columns that don't end with 'ELIM'.

For example, FilingHeaderQry will have the following columns:

State, Product, Date, Count, StateElim, ProductElim, DateElim, Sum

I would like the select statement to be:

Select State, Product, Date, Count, Sum
from FilingHeaderQry

Please post the SQL of your crosstab as it seems like Count, Sum, and other non-Elim are not part of the dynamic part of query. — Parfait
– Parfait, Commented Feb 21, 2019 at 19:19

Lee Mac · Accepted Answer · 2019-02-21 19:42:48Z

1

Rather than attempting to dynamically build a select statement using VBA, a far easier solution would be to revise the selection criteria of your crosstab query such that columns ending with Elim are not included in the output.

For example, something along the lines of:

TRANSFORM <AggregateFunction>(YourTable.ValueField) AS CrossTabValue
SELECT YourTable.RowField
FROM YourTable
WHERE YourTable.ColumnField NOT LIKE '*Elim'
GROUP BY YourTable.RowField
PIVOT YourTable.ColumnField

answered Feb 21, 2019 at 19:42

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pineapple Pig Over a year ago

Sorry, I don't think I explained this well enough. I have FilingHeaderQry that joins to List_Crosstab. I kept the columns in the crosstab so that I could use them as the join criteria with FilingHeaderQry. The issue is that since the columns in the crosstab can change, I could only do a select . FilingHeaderQry looks something like this:Select State, Product, Date, Location, crosstab. from FilingHeaderQry FHQ left join crosstab on FHQ.State = crosstab.State and FHQ.Product = crosstab.Product

Pineapple Pig Over a year ago

I think I figured it out. I combined FilingHeaderQry and another query via a join and then did the pivot on those two. That eliminated the column duplication issue.

Collectives™ on Stack Overflow

Dynamically Build SQL string in Access

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related