Dynamic queries using arrays

[jcookmo]

I am trying to develop a dynamic query which selects a particular field from a table and then sums that column by groups that I'll designate in the code. In this particular situation I have seven fields in a table(plus others)which hold order quantities. Each of these columns represents a day of the week. COUNT_1 is Monday, etc. I only need to sum on the field which corresponds to a particular day of the week. I can't alter the table as it's not in my area to work in.

Can an Array function be used in conjunction with a Weekday function to define a field name in a query?

An User selected ActiveX calendar.value would be used to drive the weekday function.

COUNTFIELD = Array(COUNT_1, COUNT_2, COUNT_3, COUNT_4, COUNT_5, COUNT_6, COUNT_7)

QUERYFIELD = COUNTFIELD(Weekday(Forms.USERCALENDAR.VALUE))

If this can be used, where does the QUERYFIELD variant go in the code, which I believe I would execute on a command button event procedure. I'm a relative novice, hoping that my hunch is correct.

 

[The_Doc_Man]

Well, there are ways to do this in the way you asked, but they usually involve doing something to denormalize a table. For a little private database, it might be OK, but if the database is going to (1) be shared or (2) grow in size and complexity, then arrays are probably not a real good idea.

You would do far better to include the day of the week as a field in your table and then select for that day number. In straight VB, arrays are nice. In VBA, they lead you down the garden path every time.

Stated another way:

If you were going to use the day number as a subscript, you could also use it as a selection criterion with equal relevance but far easier syntax.

Now, the issue is, can you use the day number to somehow affect the name of the data field you retrieve? Yes, but not in a simple query. You need to be in VBA so you can use the syntax of .Fields("prefix_" & CStr(suffix)).Value to retrieve something for which the name was synthesized. Can't do this from SQL or a query. (Well, maybe you could, but it would be incredibly tedious.)

Put as simply as possible, taking this approach makes more work than it is worth. Add the day number (or compute it) from your raw data table and you can do fine without ever having to deal with an array.

Do you have the actual date in the table? Because if you do, you can write queries that just base their actions on the Weekday function. (Look it up in the Help files.)

In other words, you can write a query for which the query grid contains

Weekday([ActionDate]) in the Field row and
1 (or 2, 3,...7) for the criteria row.

The Help topic should tell you which day is which.

Now, you want to do totals for a single day? No problem. Just pick the day number and make it your criterion.

 

[jcookmo]

I'm not sure exactly what your saying. The table I'm attempting to query has the following fields:

RUN_DATE
ACCOUNT_NUMBER
PACKAGE
COUNT_1
COUNT_2
COUNT_3
COUNT_4
COUNT_5
COUNT_6
COUNT_7

What I want to do is total on the field that corresponds to the weekday of RUN_DATE using a query. COUNT_1 is Monday, COUNT_2 is Tuesday, etc. There are actually a few other fields but these are the main ones I'm dealing with and grouping by.

The query should basically have four fields:

RUN_DATE
ACCOUNT_NUMBER
PACKAGE
COUNT_?

The original data is in an Oracle db accessed via ODBC. This db is updated twice a day to the best of my knowledge. My Access db will be shared and on a server and will most definitely change in the near future. Because the Oracle table is huge, I am using append and delete queries to filter records by date and store them locally on my Access db in a table that is a small version of the original. This local table is the one I am querying. Perhaps I should have said this to begin with. Any assistance is appreciated.

 

[Jon K]

To dynamically select fields for a query, you must build the query in VBA.
The following shows you how to build and run a Select statement from the click event of a command button.

----------------------------
Private Sub Command0_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim SQL As String
Dim Calendar As Date
Dim WeekDayNum As Byte

Calendar = Forms!myForm!USERCALENDAR.Value

WeekDayNum = WeekDay(Calendar, vbMonday)

SQL = "SELECT Run_Date, Account_Number, Package, sum(Count_" & _
WeekDayNum & ") as SumOfCount_" & WeekDayNum & _
" FROM myTable WHERE Run_Date = #" & Calendar & _
"# GROUP BY Run_Date, Account_Number, Package "

Set db = CurrentDb

' delete previous qryTemp
On Error Resume Next
db.QueryDefs.Delete "qryTemp"
On Error GoTo 0

Set qd = db.CreateQueryDef("qryTemp", SQL)

DoCmd.OpenQuery qd.Name
Set db = Nothing

End Sub
------------------------
(Substitute with the names of your form and table. )

After running the code, a query qryTemp is added in the database. If you want, you can add the line to delete it before setting db to Nothing.

As I don't have the ActiveX Calendar, I have used a text box on the form for testing.

The code is written in Access 97, so DAO is used. If you write your own code in Access 2000, you must make a reference to the Microsoft DAO 3.6 Object Library (when the code window is open, select menu Tools, References...)

Hope this helps.

 

[jcookmo]

Thanks for the reply. It was very helpful. Although I didn't get to use if for long as I'm no longer with that company. I'll have need of it in the future though.