Solved

Access 97 SQL question- making a subquery in the FROM clause

Posted on 2001-07-05
5
380 Views
Last Modified: 2010-05-18
Rather than having to make and save several queries as objects in the database menu, is there a way for me to make a subquery in the from clause (in SQL view) and be able to reference that information?  As of right now, It gives me an error - pointing to the word SELECT even though I have the subquery in parentheses.

TIA,
boody
0
Comment
Question by:boody
  • 3
5 Comments
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6256019
I am not sure what you are trying to accomplish boody, can you clarify?  are you trying to change your query depending on different criteria?  if so, that is easily accomplished.
0
 

Author Comment

by:boody
ID: 6256225
Say I want to calculate my sales for a year.  I have my database broken down into years and months.  I need to calculate each part and it's sales for each month then I want to calculate it's yearly sales by adding up all the months.  Would I be able to make a subquery that calculates the monthly sales, use that as a reference in the from clause, and then make the query calculate the yearly sales for each part without having to save the first query as a separate query in the database?
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6256284
Yes, there are several ways to do so.  If this is going to be printed on a report, you dont need the second query, just set up a running sum to add all the months.  Also, you can use the query builder to help you build a sql statement.  If you add the sales in 2x and sum by month then years it should work.  I will try to put a SQL statement together, give me a few minutes please.
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6257030
Sorry, I havent had time to work with this.  Why dont you write  your SQL statements in VBA?  That would prevent you from having to save another query.  I can show you how to do that if you are interested.  VBA code also executes faster than regular queries.
0
 

Accepted Solution

by:
wordtool earned 30 total points
ID: 6258533
Use "union" between queries.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now