Solved

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

Posted on 2001-07-05
5
419 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

749 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