Dynamic Field Selection - Calculation

[Guest]

I would like to replace the hard coded field names (i.e. [200626]) with
dynamic field names selected from a drop down box. The drop down box should
be created using field names from the history table...these field names
change weekly.

SELECT History.[Item Nbr], Sum(History.[200626]) AS SumOfField 1,
Sum(History.[200630]) AS SumOfField 2, Sum([200630]-[200626]) AS Change
FROM History

 

[Pieter Wijnen]

you should rework your db layout
data retrieval is not about fieldnames, but about data
it is quite simple to do though <g>

NTMH
--
Pieter Wijnen

My feeble Access pages (good links though)
http://www.thuleeng.com/access
When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca/
http://allenbrowne.com/
http://www.lebans.com/

I would like to replace the hard coded field names (i.e. [200626]) with
dynamic field names selected from a drop down box. The drop down box
should
be created using field names from the history table...these field names
change weekly.

SELECT History.[Item Nbr], Sum(History.[200626]) AS SumOfField 1,
Sum(History.[200630]) AS SumOfField 2, Sum([200630]-[200626]) AS Change
FROM History

 

[Dirk Goldgar]

I would like to replace the hard coded field names (i.e. [200626])
with dynamic field names selected from a drop down box. The drop down
box should be created using field names from the history
table...these field names change weekly.

SELECT History.[Item Nbr], Sum(History.[200626]) AS SumOfField 1,
Sum(History.[200630]) AS SumOfField 2, Sum([200630]-[200626]) AS
Change FROM History

I've got to say that a table that changes field names weekly is probably
not a good design and should be revisited. That may not be terribly
helpful, though -- especially if you have no control over the data
design -- so let's see what can be done under these circumstances.

You can set the RowSourceType of a combo box to "Field List" -- that,
combined with a RowSource of "History", will get you a list of fields
from the table to choose from. But you won't be able to write a static
SQL statement that directly refers to the combo box on the form to get
its field names. Instead, you'll have to write the SQL dynamically,
after the field name(s) have been chosen.

You might have a template for the SQL statement, and swap in the field
names using the Replace function. Something like this:


Const conSQLTemplate = _
"SELECT [Item Nbr], " & _
"Sum([$F1$]) AS SumOfField1, " & _
"Sum([$F2$]) AS SumOfField2, " & _
"Sum([$F2$]-[$F1$]) AS Change " & _
"FROM History"

Dim strSQL As String

strSQL = Replace(conSQLTemplate, "$F1$", Me!cboField1)
strSQL = Replace(strSQL, "$F2$", Me!cboField2)

Now the question is, what to do with the SQL? You might open a
recordset directly on it, if you want to process the results
programmatically. If you want to display it, your best bet is probably
to assign it as the RecordSource of a form that you have previously
defined:

DoCmd.OpenForm "frmShowResults"
Forms("frmShowResults").RecordSource = strSQL

Those are just some ideas. I hope they help.

 

[Guest]

You would be be off to change your table structure to have a DataTime field
to record the date instead of a field name for the YearWeek.