Reference table column computationally in sql query - how

[PeterDuthie]

Hello All. I have a stacked query which returns a series of rows of numbers (1-5) which refers to one of 5 columns in a table. How can I (or maybe I can't do this) code the column names with the numbers returned from the stacked query. Here's an example of what I want to do.

I want to end up with a query such as
SELECT MyColumn1 (or MyColumn2, or MyColumn3, or MyColumn4, or MyColumn5)
FROM tblTestColumns;

tblTestColumns has columns named MyColumn1 ... MyColumn5

So I want to do something like
SELECT MyColumn & str(NumberFromStackedQuery)
FROM tblTestColumns;

Any help greatly appreciated as always. Maybe, I'm tacking this the wrong way?

Cheers,
Peter

 

[raskew]

Very likely I'm not interpreting the problem correctly, however, if a tblTestColumns looked something like this:

MyColumn1 MyColumn2 MyColumn3
dog chicken tuna
cat turkey catfish
horse eagle salmon

This code would return MyColumn 1, 2, 3 based on the parameter provided:

Function columnget(X As Integer)
'*******************************************
'Name: columnget (Function)
'Purpose: Return MyColumn 1, 2 or 3
' from tblTestColumns
'Author: Default
'Inputs: from debug window: ? columnget(1)
'Output: Column1 from tblTestColumns
'*******************************************

Dim db As Database, qd As QueryDef
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT MyColumn" & X & " " _
& "FROM tblTestColumns;"
'Debug.Print strSQL
On Error Resume Next

docmd.DeleteObject acQuery, "qryColumnGet"
Set qd = db.CreateQueryDef("qryColumnGet", strSQL)
db.QueryDefs.Refresh
docmd.OpenQuery "qryColumnGet", acViewNormal

End Function
*****

The strSQL statement is the key. Had I been able to figure out a method of using X in a parameter statement to complete the field name in a query, then the code could be avoided.

 

[PeterDuthie]

Thanks very much raskew - and you have understood the jist of the problem. But the code you suggest would need to be called from a procedure or function where a sql string was being built up. I was hoping to do something like MyColumn & str(NumberFromStackedQuery)right from the query. I'm newish to Access and still finding out what's possible and if it can't be done ... it can't be done. The trouble is that I need to associate a number (1-5) held in one table and returned by a stacked query with a column ("Column1" ... "Column5&quot in another table where the information I actually need is stored. This last query in the stack will have the summary information I need to generate a report.

Cheers,
Peter

 

[PatHartman]

It sounds like a table design issue. The columns 1-5 should probably be rows with a type code. Then you could do the join as you envision.

It is not possible to "parameterize" anything in a query other than variables in the where clause. SQL is not a programming language the way VBA is and does not have the capability to parse this type of code.

The code Bob posted is the best you can do with your current table design.

BTW, what is a "stacked" query.

 

[PeterDuthie]

Thanks for your interest Pat. A stacked query is a query that is stacked: a query which uses the result of another query(s).

There is a solution to my particular problem using UNION. What I did was split the recordset into 5 parts, each having one just one column which was identified by the "NumberFromStackedQuery" as above, in the WHERE clause. I then UNION(ed) the parts back together - in effect changing the columns into Row data. From there everything is easy.

(The database wasn't designed by me - I was hired to fix it.)

Cheers,
Peter

 

[raskew]

Peter-

Glad you got it working. Would be interested in seeing the query SQL you used.

Thanks to Pat for confirming the limits of parameterizing outside of the WHERE statement.

 

[PeterDuthie]

Hello raskew. I don't know how clear this will be to you but I'll be happy to try and explain. I'll try and keep this short.

These are the columns I've been talking about in table [Daily Record Sheet] (a sheet filled out by work gangs and recorded in the database every day - or so). This table is at the top of the database design.

OpCode1 OpCode2 OpCode3 OpCode4 OpCode5
200 220 220 201 0
200 220 220 201 240
220 240 220 0 0


Here is an edited excerpt from the table [Daily Labour] which has a 1 to many relationship with [Day Record Sheet] above.

Lab Desc Op Code
Mobile Crane Driver > 6 ton 1
Building Trade Crafstman 1
Building Trade Crafstman 1
Mobile Crane Driver > 6 ton 2
Building Trade Crafstman 2
....

This is the first query I use (saved as LabourCostsAggregate):
SELECT DISTINCTROW Sum(([Hrs@1:0]*[Ratex1:0])+([Hrs@1:5]*[Ratex1:5])+([Hrs@2:0]*[Ratex2:0])) AS [Labour Costs], [Daily Labour].[Op Code] AS Code, [Daily Labour].[Lab Desc] AS LabDesc, [Daily Record Sheet].SheetNum, [Daily Record Sheet].OpCode1 AS OC1, [Daily Record Sheet].OpCode2 AS OC2, [Daily Record Sheet].OpCode3 AS OC3, [Daily Record Sheet].OpCode4 AS OC4, [Daily Record Sheet].OpCode5 AS OC5
FROM [Daily Record Sheet], [Daily Labour]
WHERE ((([Daily Record Sheet].SheetID)=[Daily Labour].[SheetID]))
GROUP BY [Daily Labour].[Op Code], [Daily Labour].[Lab Desc], [Daily Record Sheet].SheetNum, [Daily Record Sheet].OpCode1, [Daily Record Sheet].OpCode2, [Daily Record Sheet].OpCode3, [Daily Record Sheet].OpCode4, [Daily Record Sheet].OpCode5, [Daily Record Sheet].SheetNum
ORDER BY [Daily Record Sheet].SheetNum;

Then for each column I use query LabourCostsOpCode1 ... 5
SELECT DISTINCTROW LabourCostsAggregate.[Labour Costs], LabourCostsAggregate.Code, LabourCostsAggregate.LabDesc, [Daily Record Sheet].SheetNum AS SheetNum, LabourCostsAggregate.OC1
FROM LabourCostsAggregate
WHERE (((LabourCostsAggregate.Code)=1));

Then I UNION them together in LabourCostsOpCodeUnion:
TABLE LabourCostsOpCode1
UNION
TABLE LabourCostsOpCode2
UNION
TABLE LabourCostsOpCode3
UNION
TABLE LabourCostsOpCode4
UNION TABLE LabourCostsOpCode5;

Then I finally arrive at the point where I can get at the data I want for a report with this query
SELECT Sum(LabourCostsOpCodeUnion.[Labour Costs]) AS Costs, LabourCostsOpCodeUnion.OC1, LabourCostsOpCodeUnion.LabDesc
FROM LabourCostsOpCodeUnion
GROUP BY LabourCostsOpCodeUnion.OC1, LabourCostsOpCodeUnion.LabDesc;

And this is how the final datasheet looks:

Costs OC1 LabDesc
£1,728.00 0 Building Trade Crafstman
£1,344.00 0 Construction Supervisor
£1,555.20 220 Labourer
£2,355.60 220 Mechanical Excavator Driver
£576.00 220 Mobile Crane Driver > 6 ton
£1,113.60 230 Mobile Crane Driver > 6 ton
£288.00 240 Labourer
.............
etc.

Still here??? I admire your patience. Sorry if I've gone into too much detail but it's easier for *me* that way.

Not very elegant but it does do the job. Of course, as Pat pointed out better design would have made all this unnecessary.

Cheers,
Peter

 

[raskew]

Peter,

Thanks for the example, it spoke volumes. Given the SheetNum and SheetID references in your code (but not shown in the table design), it would appear this is a spreadsheet converted to .mdb exercise, using a spreadsheet approach.

Agree fully with Pat Hartman's analysis that this is a table-design issue. Think you'd be doing your client a great service if you'd reevaluate the approach in terms of "…if I had to build this from scratch, how would I do it?"

Best of luck.

Bob