By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

Access Query of a Table

P: 3
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
Mar 21 '12 #1

✓ answered by Luuk

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

Expand|Select|Wrap|Line Numbers
  1. select id, 'jan', january from ExpectedRevenue
gives something like:
1, 'jan', $1000
2, 'jan', $2000

and:
Expand|Select|Wrap|Line Numbers
  1. select id, 'feb', februari from ExpectedRevenue
gives something like:
5, 'feb', $1500
6, 'feb', $1750

then:
Expand|Select|Wrap|Line Numbers
  1. select id, 'jan', january from ExpectedRevenue
  2. union all
  3. select id, 'feb', februari from ExpectedRevenue
gives:
1, 'jan', $1000
2, 'jan', $2000
5, 'feb', $1500
6, 'feb', $1750

Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Use a union query.
Expand|Select|Wrap|Line Numbers
  1. SELECT 'Jan' AS RevMonth, field2
  2. FROM someTable
  3.  
  4. UNION ALL
  5.  
  6. SELECT 'Feb' AS RevMonth, field3
  7. FROM someTable
Mar 21 '12 #2

P: 3
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
Mar 22 '12 #3

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Project_ID], 'January' AS [Month], [January]
  2. FROM   [YourTable]
  3.  
  4. UNION ALL
  5.  
  6. SELECT [Project_ID], 'February' AS [Month], [February]
  7. FROM   [YourTable]
  8.  
  9. UNION ALL
  10.  
  11. ...
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.
Mar 22 '12 #4

P: 3
Actual Table Name is ExpectedRevenue

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

Expand|Select|Wrap|Line Numbers
  1. SELECT [Project_ID], 'January' AS [Month], [January] 
  2. FROM   [ExpectedRevenue] 
  3.  
  4. 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,
Mar 22 '12 #5

Expert 100+
P: 1,034
UNION ALL is to 'connect' two SQL statements together, one before the union, and one after the union....

Expand|Select|Wrap|Line Numbers
  1. select id, 'jan', january from ExpectedRevenue
gives something like:
1, 'jan', $1000
2, 'jan', $2000

and:
Expand|Select|Wrap|Line Numbers
  1. select id, 'feb', februari from ExpectedRevenue
gives something like:
5, 'feb', $1500
6, 'feb', $1750

then:
Expand|Select|Wrap|Line Numbers
  1. select id, 'jan', january from ExpectedRevenue
  2. union all
  3. select id, 'feb', februari from ExpectedRevenue
gives:
1, 'jan', $1000
2, 'jan', $2000
5, 'feb', $1500
6, 'feb', $1750
Mar 22 '12 #6

NeoPa
Expert Mod 15k+
P: 31,418
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.
Mar 23 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.