Sorting, Grouping and Calculating in a Query

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

Guest

I want to pull out customer name & addresses based on purchase amounts for
over a certain amount (say $1000) over a certain time period. My fields are
all available in one table, but I also have many of the fields in other
tables as well. Fields include customer no, name, address (blah,blah)
invoice#, date and paid (total for a specific invoice). I can calculate the
time period with criteria either in the invoice number or date fields.

I'm able to get the totals (using the sum function) by customer number when
I use just those two fields, but when I add additional fields to the query
(like date or invoice) it separates them out again and I end up with multiple
lines per customer. How can I generate something that filters by date and
total purchases by customer and still gives me only one line per customer?
Thanks!!
 
SELECT customerNo, LAST(customerName), LAST(customerAddress), SUM(amount)
FROM myTable
WHERE accoundDate BETWEEN thisDate AND thatDate
GROUP BY customerNo
HAVING SUM(amount) >= 1000



Hoping it may help,
Vanderghast, Access MVP
 
Michael, am I doing this from a regular select query? Or are the Notes in
CAPS significant to another format?

It seems like I'm kind of doing what you suggest, but I end up with the
results not grouping into one line per customer after I add other fields or
criteria.
 
That's in SQL view, isn't it. I'll try that and see if it works
better...that's all new to me. I'm an Access baby.
 
Yes that is from the SQL view. ALL CAPS words are reserved-syntax words, to
be typed as they are; other are yours to substitute.

You can switch into graphical view, once it is typed in SQL, to see how you
could have done it, graphically.

A point to remember is that the result is such that it should be as if the
WHERE clause is applied BEFORE any grouping/summation; while the HAVING
clause is applied AFTER the grouping/summation process.


Hoping it may help,
Vanderghast, Access MVP
 
Michael, I'd not used the SQL view more than a time or two but your
instructions worked perfectly and now I can tweak it to get the exact data
fields, date ranges and other stuff I need in the report! Thanks SOOOO much!
 
Back
Top