Author |
Topic |
VRP_Josh
Starting Member
5 Posts |
Posted - 2012-06-14 : 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 lngprojectJOIN metalsminingprojectON lngproject.[Award Year]=metalsmining.[Award Year] |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-14 : 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 lngprojectJOIN metalsminingprojectON lngproject.[Award Year]=metalsmining.[Award Year]GROUP BY lngproject.[Award Year]JimEveryday I learn something that somebody else already knew |
|
|
VRP_Josh
Starting Member
5 Posts |
Posted - 2012-06-14 : 10:33:02
|
I'm still getting "syntax error in FROM clause though |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-14 : 11:09:22
|
I'm not seeing any syntax errors. Are you using Microsoft SQL Server?JimEveryday I learn something that somebody else already knew |
|
|
VRP_Josh
Starting Member
5 Posts |
Posted - 2012-06-14 : 11:36:06
|
I'm using SQL through Microsoft Access. This JOIN has me really frustrated... |
|
|
mhorseman
Starting Member
44 Posts |
Posted - 2012-06-14 : 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 - 2012-06-14 : 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 lngprojectJOIN metalsminingprojectON lngproject.[Award Year]=metalsminingproject.[Award Year]GROUP BY lngproject.[Award Year] |
|
|
mhorseman
Starting Member
44 Posts |
Posted - 2012-06-15 : 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 - 2012-06-15 : 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
44 Posts |
Posted - 2012-06-15 : 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
5 Posts |
Posted - 2012-06-17 : 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 |
|
|
emam razib
5 Posts |
Posted - 2013-11-10 : 21:32:44
|
I am really like here gear and loved to work but don't know much else.unspammed |
|
|
|