Query for above average orders? 
Author Message
 Query for above average orders?

I need a little assistance if anyone can offer it.

I need to create a query that will look at all of our orders
for a given time period (let's say six months) and then
run a report that will tell me all orders in the last six
months that were approximately 50% or higher above
the average orders.

I already have a client name and number table and an
order history table that I can use to compile the new
query (I think).

Ideally, what I'd like is for the new query to create a new averages
table when I run it, then a report that will run the new query
and read the info from the newly created averages table to
determine orders above 50% of the average. This way as new
orders are entered, I can just run the report which will pull
data from the averages table, and that averages table will
have current data from all current orders.

I've got a query that shows me customer number, name,
order number, order amount...but I'm a little stuck on how to
get the query to create a new averages table and a report
that shows above average orders for a six month period.

Any ideas/thoughts?

Thanks in advance.

SS



Mon, 15 Apr 2002 03:00:00 GMT  
 Query for above average orders?
SELECT Orders.*
FROM Orders
WHERE Orders.OrderTotal >
(SELECT Avg(OrderTotal) * 1.5
FROM Orders As O2 WHERE O2.OrderDate > (Date() - 180))
AND Orders.OrderDate > (Date() - 180)

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:

> I need a little assistance if anyone can offer it.

> I need to create a query that will look at all of our orders
> for a given time period (let's say six months) and then
> run a report that will tell me all orders in the last six
> months that were approximately 50% or higher above
> the average orders.

> I already have a client name and number table and an
> order history table that I can use to compile the new
> query (I think).

> Ideally, what I'd like is for the new query to create a new averages
> table when I run it, then a report that will run the new query
> and read the info from the newly created averages table to
> determine orders above 50% of the average. This way as new
> orders are entered, I can just run the report which will pull
> data from the averages table, and that averages table will
> have current data from all current orders.

> I've got a query that shows me customer number, name,
> order number, order amount...but I'm a little stuck on how to
> get the query to create a new averages table and a report
> that shows above average orders for a six month period.

> Any ideas/thoughts?

> Thanks in advance.

> SS



Mon, 15 Apr 2002 03:00:00 GMT  
 Query for above average orders?


Quote:
>SELECT Orders.*
>FROM Orders
>WHERE Orders.OrderTotal >
>(SELECT Avg(OrderTotal) * 1.5
>FROM Orders As O2 WHERE O2.OrderDate > (Date() - 180))
>AND Orders.OrderDate > (Date() - 180)

Thanks. So, you think it would be easier to write the query in
SQL language than to use the query designer in Access 97
for this?

SS



Wed, 17 Apr 2002 03:00:00 GMT  
 Query for above average orders?
You can do it on the query designer grid, but you'll have to write the
subquery in the little box on the Criteria line under OrderTotal.  Might as
well use SQL view.  One of these days, MSFT will give Access a decent SQL
editor.

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:


> >SELECT Orders.*
> >FROM Orders
> >WHERE Orders.OrderTotal >
> >(SELECT Avg(OrderTotal) * 1.5
> >FROM Orders As O2 WHERE O2.OrderDate > (Date() - 180))
> >AND Orders.OrderDate > (Date() - 180)

> Thanks. So, you think it would be easier to write the query in
> SQL language than to use the query designer in Access 97
> for this?

> SS



Fri, 19 Apr 2002 03:00:00 GMT  
 Query for above average orders?


Quote:
>You can do it on the query designer grid, but you'll have to write the
>subquery in the little box on the Criteria line under OrderTotal.  Might as
>well use SQL view.  One of these days, MSFT will give Access a decent SQL
>editor.

Yes, you're right, it would be helpful to have a better editor than
the weak notepad-like SQL editor that Access 97 has.

Thanks for the assistance, it pointed me in the right track to
get the report we wanted together. Appreciate the help.

BTW, did you write an Access 97 book also, or just the Access
2000 title? And is Access 2000 much different than Access 97 (I
haven't seen the Office 2000 suite yet)?

SS



Fri, 19 Apr 2002 03:00:00 GMT  
 Query for above average orders?
Yup, there's a "Running Microsoft Access 97" available, too.  I've written
all of the "Running Access" books.

Here's a quick summary of the new stuff in Access 2000:

* Tables / Queries
-Row-Level Locking
-Subdatasheets
-Improved Find / Replace
-Name AutoCorrect
-Unicode Support
-Print Relationships

* Forms / Reports
-Conditional Formatting - can now control formatting of controls on a
continuous form or subform on a row-by-row basis
-Group / Ungroup Controls
-Form Recordset Property
Can assign a Recordset Object from code
-New Form Dirty Event
-Report Snapshot Files
Was an add-on to Access 97, now built-in

* Data Access Pages (Brand new feature)
-Programmable Web Pages
-Can Bind Directly to Access Data Source
-Pages Bound to Single Tables Updatable
-Can Add Programming with VB Script
-HTML Page Stored External To Access

DAP limitations:
If Data Moves, Connection is Broken
Requires Client to Have Office 2000
All Data Access Done From CLIENT
OK for IntraNet
Deadly Slow on Internet Modem Connection
Bottom Line: Useful only for IntraNet applications with Office installed
everywhere.

* Visual Basic
-Visual Basic Editor (VBE) Now Common to All Office Applications
-Default Data Access Library is ADO!
-Enhanced Debug Facilities
-Can Set Form/Report Properties From VBE

* SQL Server Projects (Brand new feature)
-New ADP / ADE File Suffix
-New Microsoft Data Engine (MSDE)
-Desktop version of SQL Server 7
-Management via Access Project UI
-Tables and Queries (Views, Stored Procedures) Stored in SQL Server
-Uses ADO Exclusively

Project limitations:
SQL Server Only
No JET Tables or Queries (except from Code)
ADO Filter not as powerful as JET
Must learn SQL Server SQL
ADO Catalog manipulation not as powerful as JET
Updatability of Views is limited

* Miscellaneous
-Better Y2K Support
-Option to force 4-digit year in the UI everywhere
-Euro Format
-Organize Objects into Groups
-Compact on Close option
-"Novice" toolbars
-Convert back to A97 format

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:

> BTW, did you write an Access 97 book also, or just the Access
> 2000 title? And is Access 2000 much different than Access 97 (I
> haven't seen the Office 2000 suite yet)?

> SS



Sat, 20 Apr 2002 03:00:00 GMT  
 Query for above average orders?
Hi John,

What are the "specs" of an SQL editor? If I have time, I may try to build
one (ActiveX or add-in or otherwise), but I have no idea, except for my own
small perception, of the desired functionalities, UI, ... an heavy user may
wish...

Vanderghast, Access MVP.


Quote:
> You can do it on the query designer grid, but you'll have to write the
> subquery in the little box on the Criteria line under OrderTotal.  Might
as
> well use SQL view.  One of these days, MSFT will give Access a decent SQL
> editor.

> --
> John Viescas
> author, "Running Microsoft Access 2000"
> http://www.viescas.com/





Sun, 21 Apr 2002 03:00:00 GMT  
 Query for above average orders?
Michel-

Even getting as good as the SQL pane in SQL Server 7 or an A2K project would
be an improvement.  Ideally, it would allow basic find / replace
functionality.  A "syntax check" button like you find in the Enterprise
Manager query analyzer.

--
John Viescas
author, "Running Microsoft Access 2000"
http://www.viescas.com/

Quote:
> Hi John,

> What are the "specs" of an SQL editor? If I have time, I may try to build
> one (ActiveX or add-in or otherwise), but I have no idea, except for my
own
> small perception, of the desired functionalities, UI, ... an heavy user
may
> wish...

> Vanderghast, Access MVP.



> > You can do it on the query designer grid, but you'll have to write the
> > subquery in the little box on the Criteria line under OrderTotal.  Might
> as
> > well use SQL view.  One of these days, MSFT will give Access a decent
SQL
> > editor.

> > --
> > John Viescas
> > author, "Running Microsoft Access 2000"
> > http://www.viescas.com/





Sun, 21 Apr 2002 03:00:00 GMT  
 Query for above average orders?
Hi,

Thanks. That seems some job, mainly the syntax check, but if I ever have
some spare time, I may try...

Vanderghast, Access MVP.


Quote:
> Michel-

> Even getting as good as the SQL pane in SQL Server 7 or an A2K project
would
> be an improvement.  Ideally, it would allow basic find / replace
> functionality.  A "syntax check" button like you find in the Enterprise
> Manager query analyzer.

> --
> John Viescas
> author, "Running Microsoft Access 2000"
> http://www.viescas.com/


> > Hi John,

> > What are the "specs" of an SQL editor? If I have time, I may try to
build
> > one (ActiveX or add-in or otherwise), but I have no idea, except for my
> own
> > small perception, of the desired functionalities, UI, ... an heavy user
> may
> > wish...

> > Vanderghast, Access MVP.



> > > You can do it on the query designer grid, but you'll have to write the
> > > subquery in the little box on the Criteria line under OrderTotal.
Might
> > as
> > > well use SQL view.  One of these days, MSFT will give Access a decent
> SQL
> > > editor.

> > > --
> > > John Viescas
> > > author, "Running Microsoft Access 2000"
> > > http://www.viescas.com/





Mon, 22 Apr 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Query to find the average spent per purchase order

2. Average above zero 3rd try reply

3. Average above zero 3rd try

4. Average above zero 2nd try

5. Average above zero

6. Order Frequency / Average days between dates - is this possible

7. Order average per month

8. Excel formula to calc actual average orders year 1

9. Help! - Average of rank order

10. Help with ordering query results in year order please


 
Powered by phpBB® Forum Software © phpBB Group