Re: use expression as field name in query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"Larry" wrote:
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



.

Relevant Pages

  • Re: Non-updateable query issue
    ... The issue arises when I create the query for the datasheet view to ... master table primary key. ... I am a big fan of normalization. ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... Jerry Whittle, Microsoft Access MVP ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Normalization woes - how do I reproduce a query?
    ... I would use a standard select query with code in the web page to manage ... I can now appreciate the advantages of normalization but cannot reproduce ... FROM Lodging INNER JOIN IconAssignments ON Lodging.LodgingID = ... and records that had no assigned icons didn't appear at all. ...
    (microsoft.public.access.queries)
  • Re: Syntax for FROM tablename on a form
    ... >What doesn't work is to recognize the FROM target from the form. ... There are many reasons why you ... executing an Update query). ... If you are unable to rectify the normalization issues, ...
    (microsoft.public.access.formscoding)