| Author |
 Topic  |
|
|
VRP_Josh
Starting Member
5 Posts |
 Posted - 06/14/2012 : 10:05:13
|
Say I have two queries called lngproject and metalsminingproject and in each query is an award year, project type and project value column.
I want to JOIN the two together into a table with columns [Award Year], Sum of lng Projects, Sum of metalsmining Projects.
What's wrong with my code?
SELECT lngproject.[Award Year],
sum(lngproject.[project value]) AS LNG,
sum(metalsmining.[project value]) AS [Metals Mining]
FROM lngproject
JOIN metalsminingproject
ON lngproject.[Award Year]=metalsmining.[Award Year]
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/14/2012 : 10:27:09
|
Just what the eror message said, you need to use GROUP BY
SELECT lngproject.[Award Year],
sum(lngproject.[project value]) AS LNG,
sum(metalsmining.[project value]) AS [Metals Mining]
FROM lngproject
JOIN metalsminingproject
ON lngproject.[Award Year]=metalsmining.[Award Year]
GROUP BY lngproject.[Award Year]
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
VRP_Josh
Starting Member
5 Posts |
Posted - 06/14/2012 : 10:33:02
|
| I'm still getting "syntax error in FROM clause though |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/14/2012 : 11:09:22
|
I'm not seeing any syntax errors. Are you using Microsoft SQL Server?
Jim
Everyday I learn something that somebody else already knew |
|
|
|
VRP_Josh
Starting Member
5 Posts |
Posted - 06/14/2012 : 11:36:06
|
| I'm using SQL through Microsoft Access. This JOIN has me really frustrated... |
|
|
|
mhorseman
Starting Member
United Kingdom
12 Posts |
Posted - 06/14/2012 : 11:38:35
|
One other thing, your two tables are Ingproject and metalsminingproject, but you're referring to metalsmining (NOT metalsminingproject) in your select and join?
Mark |
|
|
|
VRP_Josh
Starting Member
5 Posts |
Posted - 06/14/2012 : 11:56:23
|
Mark you were right but after I fixed it, it's still giving me the same error. The code now is:
SELECT lngproject.[Award Year],
sum(lngproject.[project value]) AS LNG,
sum(metalsminingproject.[project value]) AS [Metals Mining]
FROM lngproject
JOIN metalsminingproject
ON lngproject.[Award Year]=metalsminingproject.[Award Year]
GROUP BY lngproject.[Award Year] |
|
|
|
mhorseman
Starting Member
United Kingdom
12 Posts |
Posted - 06/15/2012 : 06:17:20
|
Sorry, hadn't noticed Access was involved. Not used it for ages - does it need you to specify "INNER JOIN" rather than just "JOIN"?
Mark |
|
|
|
VRP_Josh
Starting Member
5 Posts |
Posted - 06/15/2012 : 07:09:10
|
| Changed the join to inner join but now it's saying query is too complex. Any other ideas? I feel it's close but I don't know what exactly is wrong |
|
|
|
mhorseman
Starting Member
United Kingdom
12 Posts |
Posted - 06/15/2012 : 11:40:50
|
Is your query really just the few lines you've entered, or is that psrt of a bigger query? Searching on "Query is too complex" gets results implying Access can't cope with too many columns, or too many joins .... etc.
Mark |
|
|
|
emam razib
Starting Member
Bangladesh
1 Posts |
Posted - 06/17/2012 : 05:32:57
|
Hello every one!
I am just new of this forum and also this wide world of mange.
Anyone had some good suggestions and shier their experience in here.
I am really like air gear and loved to work but don't know much else.
Thank you all.
unspammed |
|
|
| |
Topic |
|
Help JOIN
