By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,501 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,501 IT Pros & Developers. It's quick & easy.

[Field name] returned as data in query results

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Use a crosstab query. When you design a new query select Cross Tab query
wizard and it will walk you through.

--
Reggie

----------
"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
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

Nov 13 '05 #2

P: n/a
ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:
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


your three fields need to be two fields, one for the municipality
type, the second for your number.

That way, when the government decides to merge all the towns into
one megacity, you do not have to alter your table, queries, forms
reports and macros in order to capture the changing reality by
adding a field. You just add the new definition to the list of
municipality types.

Bob Quintal

Nov 13 '05 #3

P: n/a
Bob Quintal <bq******@generation.net> wrote in message news:<c9******************************@news.terane ws.com>...
ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:
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


your three fields need to be two fields, one for the municipality
type, the second for your number.

That way, when the government decides to merge all the towns into
one megacity, you do not have to alter your table, queries, forms
reports and macros in order to capture the changing reality by
adding a field. You just add the new definition to the list of
municipality types.

Bob Quintal

Thanks for the reply, Bob - I over-simplified the example to minimize
confusion (my own, likely). What I have is an existing table, with
11000+ records, and more than 80 fields that are similar in the type
of data they contain. This is looney, I know, and there is
undoubtedly a better way to design it, but, alas, here we are. Each
record is a musical chant that appears in any number of manuscripts
(many occur in one only, but some do appear in 20 or 30 or more). If
you have any grand ideas as to the table's design, I'm all ears.

Failing that, I think if I can get the data into a second table (or
query), I can get the job done. Here's an example:

ID MS1 MS2 MS3 MS4.....to MS80+
1 a
2 b
3 a c
4 d

If I can construct a query that will
generate the following results (from data above)...:

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

.... then 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
Nov 13 '05 #4

P: n/a
ma*******@rogers.com (Martin Lacoste) wrote in message news:<d4**************************@posting.google. com>...
Bob Quintal <bq******@generation.net> wrote in message news:<c9******************************@news.terane ws.com>...
ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:
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


your three fields need to be two fields, one for the municipality
type, the second for your number.

That way, when the government decides to merge all the towns into
one megacity, you do not have to alter your table, queries, forms
reports and macros in order to capture the changing reality by
adding a field. You just add the new definition to the list of
municipality types.

Bob Quintal

Thanks for the reply, Bob - I over-simplified the example to minimize
confusion (my own, likely). What I have is an existing table, with
11000+ records, and more than 80 fields that are similar in the type
of data they contain. This is looney, I know, and there is
undoubtedly a better way to design it, but, alas, here we are. Each
record is a musical chant that appears in any number of manuscripts
(many occur in one only, but some do appear in 20 or 30 or more). If
you have any grand ideas as to the table's design, I'm all ears.

Failing that, I think if I can get the data into a second table (or
query), I can get the job done. Here's an example:

ID MS1 MS2 MS3 MS4.....to MS80+
1 a
2 b
3 a c
4 d

If I can construct a query that will
generate the following results (from data above)...:

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

... then 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


One way of fixing this is to create a final table with the (ID,MS)
fields. Then you could loop through all the records
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.