Solved

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

Posted on 2001-07-05
5
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

624 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