Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2001-07-05
5
Medium Priority
?
494 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 90 total points
ID: 6258533
Use "union" between queries.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

916 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