Access Query of a Table

[bilal1234567]

I have an access table in the following format:
Project_ID January February March .......
1 $1000 $2000 $1500
2 $0 $150 $345
3 $500 $600 $250
.
.
.

I want to have the data in the following format:
Project_ID Month Revenue
1 January $1000
1 February $2000
1 March $1500
2 January $0
2 February $150
2 March $345
3 January $500
3 Febuary $600
3 March $250
.
.
.
Is there a way to do this? I can replace the Month Names with Number such as 01 for january, 02 for February, 03 for March.....if the names are an issue.

Thanks,

Bilal

[Rabbit]

Use a union query.

SELECT 'Jan' AS RevMonth, field2
FROM someTable
 
UNION ALL
 
SELECT 'Feb' AS RevMonth, field3
FROM someTable

[bilal1234567]

Can you give the exact query for one record and i will duplicate for the rest. I am still learning and am getting error messages.

Thanks

[NeoPa]

Bilal:
Can you give the exact query for one record

Rabbit's done that already pretty much (He posted examples for Jan and Feb in fact). Including the table name is impossible at this stage as you haven't told us what that is. A slightly closer match to your question might be :

SELECT [Project_ID], 'January' AS [Month], [January]
FROM   [YourTable]
 
UNION ALL
 
SELECT [Project_ID], 'February' AS [Month], [February]
FROM   [YourTable]
 
UNION ALL
 
...

PS. If you still have problems then say just that is very little help. We would need the SQL you are actually using and a full indication of the problem you're getting.

[bilal1234567]

Actual Table Name is ExpectedRevenue

I have the following Query and when I run, I get the following error.

SELECT [Project_ID], 'January' AS [Month], [January] 
FROM   [ExpectedRevenue] 
 
UNION ALL;

Invalid SQL statement, expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

I am using Access 2007. I am not sure what I am missing.

Thanks,

[Luuk]

UNION ALL is to 'connect' two SQL statements together, one before the union, and one after the union....

select id, 'jan', january from ExpectedRevenue

gives something like:
1, 'jan', $1000
2, 'jan', $2000

and:

select id, 'feb', februari from ExpectedRevenue

gives something like:
5, 'feb', $1500
6, 'feb', $1750

then:

select id, 'jan', january from ExpectedRevenue
union all
select id, 'feb', februari from ExpectedRevenue

gives:
1, 'jan', $1000
2, 'jan', $2000
5, 'feb', $1500
6, 'feb', $1750

[NeoPa]

I think Luuk has explained it pretty well, but I was curious how you ended up with what you posted. It was certainly not anything I'd posted as I showed the queries for both Jan and Feb as well as ending with an ellipsis (...) indicating that more of the same was to follow. Perhaps I should have made clearer the point Luuk made, which was that the last UNION ALL must come before the last SELECT query.