total result of query less than 10K an display as 1 result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a report in Access that shows all Work Providers and
their Sales ledger that owe over £10K and then group the all the Work
Providers that owe less than £10K and show as 1 result
Sales Ledger
Work provider £36,000
Work Provider £67,890
Others Less than £10K £156475 (made up of totals for
all WP's less than £10K)

I have a query that shows me all Work providers an their totals but i can't
get a criteria to put the Others colum. Can any one suggest a way to do this?
 
Donna,

You can do the trick with a Union Query like:

SELECT Provider, Sales, 1 As MySorting
FROM MyQuery
WHERE Sales > 10000

UNION SELECT "Others Less Than £10K", Sum([Sales]), 2
FROM MyQuery
WHERE Sales <=10000
ORDER BY MySorting

where I have assumed your existing query to be named MyQuery, with
fields Provider and Sales; substitute the actual names.

The third field is just so you get the all others record last.

HTH,
Nikos
 
Sure. You need to create two queries. One to show the records >10000, and
a second one to show the sum of all those <10000. Then you can UNION these
two queries. The UNION statement simply slaps the results of the second
query onto the bottom of the first query. (Although, if sorting is an
issue, you'll have to include a sort field.)

Now, you don't actually have to create and save the separate queries, you
can do something like this:

SELECT WorkProvider, SalesLedger
FROM Table1
WHERE SalesLedger>10000
UNION
SELECT "Others Less than £10K" AS WorkProvider, Sum(SalesLedger) AS
SumOfSalesLedger
FROM Table1
WHERE SalesLedger<10000
GROUP BY "Others Less than £10K";

But creating them separately to begin with can be helpful for developing
them because once you add the UNION statement, you can't edit it in the
Query Builder anymore.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Donna.K. said:
I'm trying to create a report in Access that shows all Work Providers and
their Sales ledger that owe over £10K and then group the all the Work
Providers that owe less than £10K and show as 1 result
Sales Ledger
Work provider £36,000
Work Provider £67,890
Others Less than £10K £156475 (made up of totals for
all WP's less than £10K)

I have a query that shows me all Work providers an their totals but i can't
get a criteria to put the Others colum. Can any one suggest a way to do
Click to expand...
this?
 
Back
Top