Trying to query off of a table field name

[tbmarlie]

I have a table called Tax_Sum_Hist where it has fields
Gross_Pd_01,Gross_Pd_02, Gross_Pd_03, etc. ("Pd" represents period) I
am trying to just sum the Gross_PD fields where the user has input
those particular periods on the user input form. For example, If the
user chooses Periods 02 and 03, I would only want my qry to sum
Gross_Pd_02 & Gross_Pd_03. I realize that I probably will have to try
to create some sql code in the criteria in my access query builder,
but have not had any sucess. Any help would be appreciated.

 

[John W. Vinson]

I have a table called Tax_Sum_Hist where it has fields
Gross_Pd_01,Gross_Pd_02, Gross_Pd_03, etc. ("Pd" represents period) I
am trying to just sum the Gross_PD fields where the user has input
those particular periods on the user input form. For example, If the
user chooses Periods 02 and 03, I would only want my qry to sum
Gross_Pd_02 & Gross_Pd_03. I realize that I probably will have to try
to create some sql code in the criteria in my access query builder,
but have not had any sucess. Any help would be appreciated.

Well, for starters, your table structure IS WRONG. You're "committing
spreadsheet" with one *FIELD* per period, rather than one REOCRD per period.

Access works well with properly normalized data; it's much more difficult with
"sheet" data. Any chance of restructuring the table into a tall-thin one with
fields Period, Amount, and a foreign key to the (presumed) parent table? If
not, a big and snarky UNION query may be required.

 

[John Spencer]

Very tough to do with that structure. Even if you fix the table design, you
are probably going to need to build the query string using VBA.

The first question is can you redesign the table structure as John Vinson
suggested. If you can't you can use a Union query (built using VBA) or a VBA
function to sum multiple fields in the row and will probably need to build the
query using VBA.

Also, how is the user going to specify the desired periods? A multi-choice
listbox on a form? Multiple checkboxes on a form?

How MANY periods are there?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dr GUI]

I don't think you can do this in QBE but you can try the following in a subroutine/function. In my example below, I am using 2 dropdown combo boxes with all the possible periods, id. 01, 02, 03, 04...12 (maybe each representing a month).

I pull Period1 and Period2 from the combo boxes and using a for-next approach I get the sum of each period using DSUM. I then add it to a running total, intTotal. Then I display the total in a text box on the form, txtTotal (if needed). Hope this helps.

Dim strPeriod1 As String
Dim strPeriod2 As String
Dim intTotal As Integer
Dim i As Integer

strPeriod1 = Me.cboPeriod1.Value ' example: 01
strPeriod2 = Me.cboPeriod2.Value ' example: 04

intTotal = 0
For i = Int(strPeriod1) To Int(strPeriod2)
intTotal = intTotal + CLng(DSum("Gross_PD_" & Format(i, "00"), "Tax_Sum_Hist"))
Next

Me.txtTotal.Value = intTotal