Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
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…

670 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