Sorting, Grouping and Calculating in a Query

[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!!

 

[Michel Walsh]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Michel Walsh]

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

 

[Guest]

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!