many records from one record query question

  • Thread starter Thread starter Jon Frost
  • Start date Start date
J

Jon Frost

I'm trying to create a simple query that builds a single
expression by gathering information from a single table.

I'm trying to look at 3 fields in the table (catagory1-
catagory3) and collect the results VERTICALLY in the
query with a new record for EACH piece of Catagory
information.

Example:
Table
rec # cat1 cat2 cat3
001 aaa ggg ttt
002 rrr ddd aaa

Query
aaa
ggg
ttt
rrr
ddd
aaa


Can someone point me in the right direction?
 
Jon Frost said:
I'm trying to create a simple query that builds a single
expression by gathering information from a single table.

I'm trying to look at 3 fields in the table (catagory1-
catagory3) and collect the results VERTICALLY in the
query with a new record for EACH piece of Catagory
information.

Example:
Table
rec # cat1 cat2 cat3
001 aaa ggg ttt
002 rrr ddd aaa

Query
aaa
ggg
ttt
rrr
ddd
aaa
Click to expand...

You need to use a Union Query to accomplish this. Check the Help, but
here's an example:

select cat1 from table1
UNION ALL select cat2 from table1
UNION ALL select cat3 from table1;

HTH,
Randy
 
I'm trying to create a simple query that builds a single
expression by gathering information from a single table.

I'm trying to look at 3 fields in the table (catagory1-
catagory3) and collect the results VERTICALLY in the
query with a new record for EACH piece of Catagory
information.

Example:
Table
rec # cat1 cat2 cat3
001 aaa ggg ttt
002 rrr ddd aaa
Click to expand...

Your query is difficult because your table structure is not properly
normalized. If a Record can belong in many Categories, and each
Category can pertain to many records, a proper table structure would
have a Categories table (with values aaa, ggg, ddd and so on) and a
CategoryAssignment table related one to many to your Records, with
fields for Rec# and Category. This would have entries like

001 aaa
001 ggg
001 ttt
002 rrr

and so on.

Lacking that - or, I'd suggest, as a way to get to that desirable end!
- you can use a "Normalizing Union" query. You must go to the SQL
window to do this. See the online help for UNION, but as a start try

SELECT [Rec#], [Cat1] AS Cat
FROM tablename
WHERE Cat1 IS NOT NULL
UNION
SELECT [Rec#], [Cat2]
FROM tablename
WHERE Cat2 IS NOT NULL
UNION
SELECT [Rec#], [Cat3]
FROM tablename
WHERE Cat3 IS NOT NULL
 
Back
Top