MS Access -> SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MS Access -> SQL

Author

Topic

pickslides
Starting Member

7 Posts

Posted - 2012-08-13 : 01:37:50

Hi there, been using Access forever and noticed while learning SQL to query I can cut and paste the code from Access's "SQL view" tab into SQL server and the query runs.

The problem I am having is as follows.

Sometimes in Access I piggyback queries to get the answer I need.

How would this translate into SQL?

Here are my Access queries, the first one just applying some criteria to a few fields, a bit of a clean up..


SELECT [2011-12 Q3].GBICCHARGE, [2011-12 Q3].MASTERID, [2011-12 Q3].CONSUMP, [2011-12 Q3].BILLDAYS
FROM [2011-12 Q3]
WHERE ((([2011-12 Q3].GBICCHARGE)="WU10") AND (([2011-12 Q3].CONSUMP)>0) AND (([2011-12 Q3].BILLDAYS) Between 42 And 100));

Then for some grouping and calculations I apply,

SELECT Query24.MASTERID, Sum(Query24.CONSUMP) AS SumOfCONSUMP, Sum(Query24.BILLDAYS) AS SumOfBILLDAYS, 1000*[CONSUMP]/[BILLDAYS] AS Expr1
FROM Query24
GROUP BY Query24.MASTERID, 1000*[CONSUMP]/[BILLDAYS];

The second query is referencing the first query.

Kind regards, MQ

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-13 : 04:37:11

You can do that in a sinlge query....

SELECT [2011-12 Q3].MASTERID, Sum([2011-12 Q3].CONSUMP) AS SumOfCONSUMP, Sum([2011-12 Q3].BILLDAYS) AS SumOfBILLDAYS, 1000* ([2011-12 Q3].CONSUMP) /([2011-12 Q3].BILLDAYS]) AS Expr1
FROM [2011-12 Q3]
WHERE ((([2011-12 Q3].GBICCHARGE)="WU10") AND (([2011-12 Q3].CONSUMP)>0) AND (([2011-12 Q3].BILLDAYS) Between 42 And 100));
GROUP BY MASTERID, 1000* ([2011-12 Q3].CONSUMP) /([2011-12 Q3].BILLDAYS])

You can use first query as subquery and group query as outer query

--------------------------
http://connectsql.blogspot.com/

pickslides
Starting Member

7 Posts

Posted - 2012-08-13 : 18:13:15

Thanks, this makes sense to me.

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind.

pickslides
Starting Member

7 Posts

Posted - 2012-08-14 : 22:12:43

Can you write 2 queries (reference a previous query) in SQL?

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources