Search multiple fields (80!) in a query

[Martin Lacoste]

(Access 2000)

Two issues:
Within a query, I need to return a field name as data (see
eg. below).

I need to search within 80 fields (same criteria) - is
there a way to
avoid 80 separate expressions (the 80 field names are
stored in a
table - can I get the query to look each of these up?)?

Here's an example:

CURRENT DATA SAMPLE:
(Field names Field 1 Field 2 Field 3
(Data 14
15
16a

RESULTS DESIRED:
(Field names Field Name Data
(Data Field 1 14
Field 2 15
Field 3 16a

Note that 'Field 1', formerly the name of the field, is now
desired as
tabular data. This seems, from my very basic research, to
be the
reverse of a crosstab query.

Any ideas are greatly appreciated - thanks!!!

Martin Lacoste

 

[Douglas J. Steele]

In my opinion, anytime you need to return a field name as data, it's a sign
that your database isn't properly normalized. The fact that you have 80
fields (or more) in a single table is another sure sign of that to me.

Maybe if you explain your setup a little more, we can suggest a better way
of achieving what you're trying to do.

 

[Allen Browne]

Martin, I also answered your question in comp.databases.ms-access

 

[Martin Lacoste]

-----Original Message-----
In my opinion, anytime you need to return a field name as data, it's a sign
that your database isn't properly normalized. The fact that you have 80
fields (or more) in a single table is another sure sign of that to me.

This may very well be. I've contemplated (with my
semi-limited knowledge) other designs, but don't know if
any will actually improve the situation.

Maybe if you explain your setup a little more, we can suggest a better way
of achieving what you're trying to do.

There are 11000+ records (musical chants) that appear in
any number of manuscripts (80 so far). If each chant
appeared in only one or two, then I could see an easier way
to set up the database - were it only so easy. Many appear
in only 1 or 2 - but some are found in 20 or more - 80 is
possible. Here's a simplified example:

ID MS1 MS2 MS3 MS4.....to MS80+
1 a
2 b
3 a c
4 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.

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 (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

 

[Douglas J. Steele]

SELECT ID, MS1 AS MS
FROM MyTable
WHERE MS1 IS NOT NULL
UNION
SELECT ID, MS2 AS MS
FROM MyTable
WHERE MS2 IS NOT NULL
UNION
....

You won't be able to handle all 80 fields in one query: there's a limit of
32 tables in a single query, but even so I think the number of subqueries
you can union together is less than that. However, even if you need 5 or 6
different queries, you can then use each of those UNION queries to form an
APPEND query to let you store the normalized data in a new table.

 

[Martin Lacoste]

Doug:

Many thanks!! This worked like a charm - two union queries,
and I got the results I needed.

Much appreciated!
Martin Lacoste

 

[Dale Fye]

Absolutely!

I am pretty rusty on this and just getting back into Access this week after a few years off.

Wouldn't it be better to structure the table like this?
Proposed Structure....
ID MS# Data
1 MS1 a
2 MS2 b
3 MS1 a
3 MS2 c
4 MS3 d
These 3 columns could make up a Primary Index.

Your Original Stucture

 

[John Vinson]

I am pretty rusty on this and just getting back into Access this week after a few years off.

Wouldn't it be better to structure the table like this?
Proposed Structure....
ID MS# Data
1 MS1 a
2 MS2 b
3 MS1 a
3 MS2 c
4 MS3 d

Exactly - that's what Douglas' "normalizing union query" was designed
to create.

 

[Douglas J. Steele]

Exactly - that's what Douglas' "normalizing union query" was designed
to create.

Well, except for the fact that I forgot to include the literal MS1, MS2,
etc! The risk of looking only at the sample output table!