Sum data query 4

[JCAA]

I am trying to sum some different data. I have a column named [Price] and a column named [MonthDate] and a third with [Customer] I want to be able to sort the data out so that it sums it by different criteria for different fields. How will I be able to Sum one field for only Jan ’03 Price another with All of 2003, a third with Jan-Feb ’04 and so on. I am just not sure how I can sum a field and get the corresponding sum of Price paid by Customer for specified months. I want criteria per field not the whole query. I am new at this as you can tell...

Thanks!

 

[Golom]

You need to work with the SUM aggregate function and the GROUP BY clause. Here are a couple of simple examples

Sums by Month
[tt]
Select Format(DateField,"yyyy-mm"), Sum(Amount) As TotalAmount
From tbl
Group By Format(DateField,"yyyy-mm")
[/tt]


Sums by Quarter
[tt]
Select Year(DateField) As [Year],
DatePart("q", DateField) As Quarter,
Sum(Amount) As TotalAmount
From tbl
Group By Year(DateField), DatePart("q", DateField)
[/tt]

 

[dhookom]

JCAA,
Your question was a bit confusing since you didn't provide:
-Data type of MonthDate
-any sample records
-sample of how you would expect the data to display in a result

Golom does a good job of reading minds but I am not sure if his answer hit the mark this time. If not, please provide more specific requirements.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LNBruno]

You'll need to write separate queries for each condition unless you set up a code module where you can pass the date ranges (the following assumes your date field is a string):

'--one month:
SELECT CUSTOMER, SUM(Price)
FROM tblYourTable
WHERE MonthDate = 'Jan03'
GROUP BY CUSTOMER
ORDER BY CUSTOMER;

'--three months:
SELECT CUSTOMER, SUM(Price)
FROM tblYourTable
WHERE MonthDate = 'Jan03' OR MonthDate = 'Feb03' OR MonthDate = 'Mar03'
GROUP BY CUSTOMER
ORDER BY CUSTOMER;

'--all of 03:
SELECT CUSTOMER, SUM(Price)
FROM tblYourTable
WHERE MID(MonthDate,4) = '03'
GROUP BY CUSTOMER
ORDER BY CUSTOMER;

< M!ke >

 

[Golom]

Thanks Duane ...
That's why my answer was pretty generic. My crystal ball seems particularly cloudy today.

 

[dhookom]

Actually there are ways to sum different time ranges within a single totals query. I would prefer that JCAA would clarify his original question prior to providing some WAG that might be wasting everyone's time.
However since Golom can provide great generic advice...taking M!ke's reply

SELECT CUSTOMER, 
SUM(Abs(MonthDate = 'Jan03') * Price) as Jan03, 
SUM(Abs(MonthDate = 'Jan03' OR MonthDate = 'Feb03' OR MonthDate = 'Mar03') * Price) as Jan_Mar03, 
SUM(Abs(MID(MonthDate,4) = '03') * Price) as All03 
FROM tblYourTable
GROUP BY CUSTOMER
ORDER BY CUSTOMER;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LNBruno]

Great, Duane! Glad I posted 'cause I learned something immediately useful! Enjoy the star!

< M!ke >

 

[JCAA]

This is as for as I have got with summing it by month. I am not sure why but I am getting 1 value on each line (looks almost like stairs) when I really do want to have the values on the same month if it is the same customer not one line with Jan03, one with Feb03 etc. I am not sure this make any sense but it is as good as I can explain it. I am pretty sure that I need to create a crosstab query with my data but I am not sure how to do that with my data.


Thank you all so much for the help I do appreciate it!


SELECT [BBB - Historical Invoice File].Customer, Format([Date into Access],"yyyy/mm") AS MonthDate, Sum(Abs([MonthDate]='2003/01')*[Unit Price]) AS Jan03, Sum(Abs([MonthDate]='2003/02')*[Unit Price]) AS Feb03, Sum(Abs([MonthDate]='2003/03')*[Unit Price]) AS Mar03, Sum(Abs([MonthDate]='2003/04')*[Unit Price]) AS Apr03, Sum(Abs([MonthDate]='2003/05')*[Unit Price]) AS May03, Sum(Abs([MonthDate]='2003/06')*[Unit Price]) AS Jun03, Sum(Abs([MonthDate]='2003/07')*[Unit Price]) AS Jul03, Sum(Abs([MonthDate]='2003/08')*[Unit Price]) AS Aug03, Sum(Abs([MonthDate]='2003/09')*[Unit Price]) AS Sep03, Sum(Abs([MonthDate]='2003/10')*[Unit Price]) AS Oct03, Sum(Abs([MonthDate]='2003/11')*[Unit Price]) AS Nov03, Sum(Abs([MonthDate]='2003/12')*[Unit Price]) AS Dec03
FROM [BBB - Historical Invoice File] LEFT JOIN [XXX - Customer Master File (USE THIS ONE)] ON [BBB - Historical Invoice File].Customer = [XXX - Customer Master File (USE THIS ONE)].Customer
GROUP BY [BBB - Historical Invoice File].Customer, Format([Date into Access],"yyyy/mm")
ORDER BY [BBB - Historical Invoice File].Customer;

 

[dhookom]

TO get one line per customer, you would need to remove the column [red]Format([Date into Access],"yyyy/mm") AS MonthDate[/red] from the column list and group by.

It looks like you could use a crosstab query that has a Row Heading of Customer, a Column Heading of Format([Date into Access],"yyyy/mm"), and a value of [Unit Price] with Sum().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.