use expression as field name in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry - 17 Feb 2008 19:21 GMT

I'm trying to use an expression to create a field name in a query.

I have an "Employee" file. that has fields "Sun", "Mon", "Tue" ... "Sat"
that contain their normal number of hours scheduled.

I have a function DayName(schdate) that uses the Weekdayname(date) to get
the day of the week, based on a date field, that returns the string "Mon" or
"Tue" etc.

I want to use the resulting DayName as the fieldname to get the normal
#hours. I have a function that returns the string "Mon" or "Tue" correctly
for the date.

the generic expression for the query field would be Expr1: [employee].[Tue]
to get the employees #hours for a Tuesday.

But I can't find a way to substitue the returned Dayname string for the
field [Tue].

My query field is - MasterSched: [employee].dayname(schdate)

I've tried surrounding the dayname(schdate) with every combination of
brackets, quotes, parentheses but I either get errors or just the string.

I've also tried having the DayName function return the entire
"[employee].[Tue]" but that didn't help.

How can I accomplish using an expression as a field name?

Thanks

Reply to this Message

Larry - 17 Feb 2008 21:12 GMT

I was finally able to do this using a Select Case with a series of DLookup
statements but seems like I should be able to do the below.

Any feedback would be appreciated.

Thanks

> I'm trying to use an expression to create a field name in a query.
>
[quoted text clipped - 26 lines]
>
> Thanks

Reply to this Message

Gary Walter - 18 Feb 2008 15:18 GMT

Hi Larry,

Meaning no offense (just trying to be humorous), but
"you're gonna poke someone's eye out " with your table
design...

you have "saved data in the field name."

Queries work best with "thin/tall" tables, say...

tblWklylSchedule
SID EmpID WkDay SchdHours
1 3 Sun 0
2 3 Mon 8
3 3 Tue 8
4 3 Wed 8
5 3 Thu 8
6 3 Fri 8
7 3 Sat 0
8 4 Sun 0
9 4 Mon 8
10 4 Tue 8
11 4 Wed 8
12 4 Thu 8
13 4 Fri 8
14 4 Sat 0

It is not completely clear how your
Employee table is constructed, but
one typical workaround for "Excel-like
data structure" is a UNION query.
Something like...

SELECT
E.Emp,
"Sun" As WkDay,
E.[Sun] As SchdHours
FROM
Employee As E
UNION ALL
E.Emp,
"Mon",
E.[Mon],
FROM
Employee AS E
UNION ALL
E.Emp,
"Tue",
E.Tue
FROM
Employee AS E
UNION ALL

{and so on for all days of week}

You will have to type out this query
in SQL View of a new query.

But you will be better served in the long
run if you redesign for "thin/tall."

Here are 3 good online tutorials that John V. once
referenced:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

Another good design tutorial (of many out there):

http://www.sqlteam.com/item.asp?ItemID=122
Database Design and Modeling Fundamentals
This article covers the basics of database design including normalization,
relationships and cardinality. A great tutorial on database design.

As Tom E. once phrased

"First the socks, then the shoes."

I truly suggest in this case that you take off your "shoes,"
and put your "socks on first."

Again...no offense intended in my *attempts*
to be humorous...

good luck,

gary

> I'm trying to use an expression to create a field name in a query.
>
[quoted text clipped - 29 lines]
>
> Thanks

Reply to this Message