[Field Name] returned as data - how?

[Martin Lacoste]

Can I get field names to be returned as data in a query's
results?
E.g. I have 3 fields (City, Town, Village), and want the
results to
incorporate these as data, e.g.:

City 2
Town 4
Village 5
Town 3
City 9

Hope this makes sense - any ideas?

Thanks!
Martin Lacoste

 

[Steve Schapel]

Martin,

No, I can't grasp the concept here. Can you say what the numbers in
your example refer to? And maybe an example of the table data would help.

 

[Martin Lacoste]

Hi Steve - thanks for taking the time to help out!

As I continue to experiment, it should be that I don't need
to go this route. However, if it is managable, I can see
this being advantageous in many cases. So, if you have any
ideas as to the feasibility of this, I'm all ears. Here is
(hopefully) a better explanation:

We have a database of 11000+ records (musical chants - I
won't bore you..), each of which is found in any number of
manuscripts (up to 80+) - could be found in only 1, or
could be in 20 or more. This perhaps explains a
not-too-clever or efficient table design.

ID MS1 MS2 MS3 MS4.....to MS80+
15 a
16 b
17 a c
18 d

Certainly I welcome any suggestions as to the design of the
table, provided this can be done automatically via a query
or other method (due to the size and nature of the data).

Originally (as per my original query), I was wishing to get
the following results:

ID MS
15 MS1
16 MS2
17 MS1
17 MS3
19 MS4

where I could get the FieldName (names of the manuscripts,
here denoted by MS1, MS2, etc..) returned as data.

As I've been thinking this through, I think I can get the
results I want if I can construct a query that will
generate the following results instead (from data above):

ID MS
1 a
2 b
3 a
3 c
4 d

etc..

From this, I should be able to do what I need. Now (please
excuse my access-newbieness) I'm trying to figure out how
to do this - I'm sure this should be easy, and I've
probably even done it in the past, and yet, well,... here I
am asking you experts.

Hope this helps to clarify - and many thanks for your time!
Martin Lacoste

 

[Steve Schapel]

Martin,

It is not possible to get the results you want directly via a query with
your existing data structure. You could write a user-defined function,
which will loop through all the MSx fields and return a value based on
the entry in the field, and then use this function in your query to get
what you desired outcome. However, I would recommend normalising the
data, which in effect would mean putting it into a table in pretty much
the same form that you want your query to show. To achieve this, you
will need to make a table with fields ID and Manuscript, and then run
80+ Append Queries, one for each of the MSx fields in your existing
table. This will be a bit of a bore, I'm sure, but once you get in the
swing of it will only take you a few minutes. (Make sure you have a
backup of your database!) Then you can remove all the MSx fields from
the existing table, but you will retain the existing table, as I presume
there are also other fields in this table that you haven't indicated,
relating to each of the chants, yes? Hope that makes sense. Once you
have got the data in this structure, you will find many things easier...
though I imagine you will need to sort out a suitable form design for
the entry and management of this data.

 

[Martin Lacoste]

Steve:
Many thanks for your assistance. I was able to do
something similar to what you suggested using union queries
and appending into a new table. It's too bad the nature
of the data necessitated such a poor table design, but,
well, the job's done, and I very much appreciate your help
in thinking through different solutions!

Take care!
Martin Lacoste