Access concatenation question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has 3 similiar fields. Let's call them Issue fields-1,2,
& 3. I need to take all the issues from all fields, sort them together to
find the Top 5 issues for a certain timeframe. How do I concatenate these
three?

What I need is this.
56
75
67
56
56

Not this 56, 75, 67
56
56

I am rather new to Access 2003 so please be detailed.

Thanks
 
A UNION query could handle this. Access cannot show these graphically, so
you will be typing in SQL View (View menu in query design.) Ultimately it's
like 3 query statements with UNION ALL in between. Something like this:

SELECT Field1 AS TheIssue
FROM Table1
UNION ALL
SELECT Field2 AS TheIssue
FROM Table1
UNION ALL
SELECT Field3 AS TheIssue
FROM Table1;

If you need to know which field the value came from, use this kind of thing:
SELECT Field1 AS TheIssue, "Field1" As TheSource
FROM Table1
UNION ALL
SELECT Field2 AS TheIssue, "Field2" As TheSource
FROM Table1
UNION ALL
SELECT Field3 AS TheIssue, "Field3" As TheSource
FROM Table1;

If you need to eliminate the nulls:
SELECT Field1 AS TheIssue, "Field1" As TheSource
FROM Table1
WHERE Field1 Is Not Null
UNION ALL
SELECT Field2 AS TheIssue, "Field2" As TheSource
FROM Table1
WHERE Field2 Is Not Null
UNION ALL
SELECT Field3 AS TheIssue, "Field3" As TheSource
FROM Table1
WHERE Field3 Is Not Null;


For the long term, the best solution would probably be to create a related
table where you can have a *record* for each of these entries, instead of a
field for each of them in the one table.
 
Thank Your so Much!!! I did come up on this last night and got it partially
working. I'm glad I am on the right track. I kept trying the graphical
queries but it did not handle what I wanted to do. Thanks again!!!
 
Back
Top