SQL Nested Select Statements - Combining Queries

[M8KWR]

I am trying to combined to queries. This is my first

SELECT dbo_Campaign.Campaign_Name, dbo_Drop_Downs.Description, dbo_History.History_Guid, dbo_Drop_Downs.Order_Value
FROM (dbo_Campaign INNER JOIN dbo_History ON dbo_Campaign.Campaign_Guid = dbo_History.Campaign_Guid) INNER JOIN dbo_Drop_Downs ON dbo_History.STATUS = dbo_Drop_Downs.DropDown_Guid
WHERE (((dbo_Campaign.Campaign_Name)=[forms]![main form]![camp code]));

At present the above one is saved as 'z_CampaignStats_Breakdown1', and then used within this one.

SELECT z_CampaignStats_Breakdown1.Description AS [Lead Code], Count(z_CampaignStats_Breakdown1.History_Guid) AS Count
FROM z_CampaignStats_Breakdown1
GROUP BY z_CampaignStats_Breakdown1.Description, z_CampaignStats_Breakdown1.Order_Value
ORDER BY z_CampaignStats_Breakdown1.Order_Value;


What I would like to happen, is to get the SQL statement looking like this

SELECT z_CampaignStats_Breakdown1.Description AS [Lead Code], Count(z_CampaignStats_Breakdown1.History_Guid) AS Count
FROM (SELECT dbo_Campaign.Campaign_Name, dbo_Drop_Downs.Description, dbo_History.History_Guid, dbo_Drop_Downs.Order_Value
FROM (dbo_Campaign INNER JOIN dbo_History ON dbo_Campaign.Campaign_Guid = dbo_History.Campaign_Guid) INNER JOIN dbo_Drop_Downs ON dbo_History.STATUS = dbo_Drop_Downs.DropDown_Guid
WHERE (((dbo_Campaign.Campaign_Name)=[forms]![main form]![camp code]))) AS z_CampaignStats_Breakdown1
GROUP BY z_CampaignStats_Breakdown1.Description, z_CampaignStats_Breakdown1.Order_Value
ORDER BY z_CampaignStats_Breakdown1.Order_Value;

But Access gives me errors...

Am i doing something wrong, any help would be appreciated

Many thanks for looking.

 

[HarleyQuinn]

What errors do you get?

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[M8KWR]

I get the error of

"Syntax error in FROM clause"

Many thanks

 

[HarleyQuinn]

If it highlights the BY in the GROUP BY it's because you're missing some spaces in your query (e.g. AS [red]z_CampaignStats_Breakdown1GROUP[/red] BY)

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[M8KWR]

after clicking ok to error, it then highlights the SELECT of the sub query!

 

[PHV]

Why not simply this ?

SELECT dbo_Drop_Downs.Description AS [Lead Code], Count(dbo_History.History_Guid) AS [Count]
FROM (dbo_Campaign
INNER JOIN dbo_History ON dbo_Campaign.Campaign_Guid = dbo_History.Campaign_Guid)
INNER JOIN dbo_Drop_Downs ON dbo_History.STATUS = dbo_Drop_Downs.DropDown_Guid
WHERE dbo_Campaign.Campaign_Name=[forms]![main form]![camp code]
GROUP BY dbo_Drop_Downs.Description, dbo_Drop_Downs.Order_Value
ORDER BY dbo_Drop_Downs.Order_Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[M8KWR]

PHV - that is brilliant - many thanks

Another quick one, if you do not mind. I have this sql statement, that I am trying to count the company_guid values

SELECT DISTINCT dbo_Campaign_Data.Company_Guid
FROM dbo_Campaign_Data INNER JOIN dbo_Campaign ON dbo_Campaign_Data.Campaign_Guid = dbo_Campaign.Campaign_Guid
GROUP BY dbo_Campaign_Data.Company_Guid, dbo_Campaign.Campaign_Name
HAVING (((dbo_Campaign.Campaign_Name)=[forms]![main form]![camp code]));

Would this need a sub query etc.