Sorry I missed including the original message with my reply earlier. My suggestion is to import the data into a normalized structure before querying.
Glenn
Sent from mobile
From: 'Dave Williams' davewillgmale@gmail.com [MS_Access_Professionals]
Sent: Wednesday, October 28, 7:44 PM
Subject: [MS_AccessPros] Can I synthesize a field name?
To: 'Dave Williams' davewillgmale@gmail.com [MS_Access_Professionals]
I have a table 'Attendance' (derived from an Excel import) with fields
Surname, Forename,
followed by many other text fields all named as dates e.g. 28/10/15. The
table has about 300
surnames, and each person's name has a possible letter in each date field
e.g. "M"
(for Member) or "V" (for Visitor), indicating their presence at a meeting on
that date.I display a subform covering the 10 dates in each year, showing attendance.
The parent form has
as its data source a table 'AllDates' with fields Year1, Date1,...Date10.
The table contains
the relevant dates for each of 8 years. I have 10 textboxes on the parent
form which show the
relevant dates for one year, derived from 'AllDates', acting as headers for
the fields in the
subform.At present I can achieve my aim by creating separate queries for each year,
picking out the
relevant fields from 'Attendance'. I select the appropriate query by joining
"Q" with the year
from a text box 'Year1' on the parent form, making query names like 'Q2008'.I would like to have just one query instead of ever-increasing numbers of
year-queries. To do
this I started by making a query 'DateSelect', giving all the names, with
columns Date1 to
Date10 showing the same 10 dates opposite each name, derived from
'AllDates'.I now want to extract the corresponding characters from 'Attendance' held in
the columns for
those dates. So I made another Query that had a string expression
"[Attendance].[" &
[AllDates].[Date1] & "]" in field 'D1:', and similarly for the fields up to
'D10:'. This
resulted in strings like "[Attendance].[17/09/08]" in the listing, but not
the contents of
those fields. I thought that Eval function applied to the strings would
convert them into their
contents, but all I get when I run that query is "#Error" in those cells -
not very helpful.I've searched on line for answers, but the two similar requests I found did
not receive proper
answers. Can anybody here say whether this is possible, and if so how?Maybe I could synthesise the SQL to generate a query for one year, and
operate that somehow without having to save it every time?
--
Dave W
Posted by: argeedblu@gmail.com
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar