should this be a query?

[Guest]

HI!
I'm not sure if I'm starting in the right place but, I have an access table
that I need to format to load to another access database. The data comes
from a text file in this format:
branch; total results; 2005A; 2004A; 2003A; (to 1990) 2005B; 2004B; 2003B
(to 1990).
I need it be formatted like this but cannot get a query to do it:
branch; total;year (can be 2005a and 2005b for another year)

Does anyone konw if this can be done???

 

[Duane Hookom]

Could you provide some sample records with the desired final format?

 

[Guest]

Hi, here's the original format:

branch: total: 2004a, 2003a, 2002a, 2001a, 2004b 2003b 2002b
toronto, 24,000, 3000 2000 7000 1000 5000 2000 4000
ottawa, 20,000, 4000, 2000, 5000, 3000, 4000 1000 1000

needs to come out like this:
toronto 3000 2004a
toronto 2000 2003a
toronto 7000 2002a
toronto 1000 2001a
toronto 5000 2004b
toronto 2000 2003b
toronto 4000 2002b
ottawa 4000 2004a
ottawa 2000 2003a
ottawa 5000 2002a
ottawa 3000 2001a
ottawa 4000 2004b
ottawa 1000 2003b
ottawa 1000 2002b

hope this helps! thanks

 

[Duane Hookom]

SELECT Branch, [2004a] as TheVal, "2004a" as TheYear
FROM tblOriginal
UNION ALL
SELECT Branch, [2003a] , "2003a"
FROM tblOriginal
UNION ALL
....etc...