1 table of 1 whole year data

[roystonteo via AccessMonster.com]

I have 1 table with 1 whole year of data.
date, partno, qty, price

Scenario is that the 1 part might have sold several times in each month.

How can i sum all the monthly records and form a table like this:
date partno qty totalprice
Jan-09 #1213 1000 200
Feb-09 #1232 1000 200
Mar-09 #1242 1000 200

 

[Damon Heron]

Let me qualify this by stating I am not a query guru, so there may be more
efficient and elegant ways of getting your answer in one query - I used two.
Query 1: SELECT Table1.DateSold, Table1.PartNo, Table1.TPrice, Table1.qty,
Sum([Tprice]*[qty]) AS SumP FROM Table1
GROUP BY Table1.DateSold, Table1.PartNo, Table1.TPrice, Table1.qty,
Table1.MyID;
**** substitute your names in the above and save****
Query 2: SELECT Query1.PartNo, Sum(Query1.SumP) AS SumOfSumP,
DatePart("m",[datesold]) AS Mo, Sum(Query1.qty) AS SumOfqty
FROM Query1
GROUP BY Query1.PartNo, DatePart("m",[datesold])
ORDER BY DatePart("m",[datesold]);

You could use the format function to make the date look nice - mine just
have the month number.

Damon

 

[John Spencer]

SELECT Format([DateField],"mmm-yy") as MoYr
, PartNo, Sum(Qty) as TotalQty, Sum(Price) as TotalPrice
FROM [SomeTable]
GROUP BY Format([DateField],"mmm-yy") as MoYr
, PartNo, Format([DateField],"yy-mm")
ORDER BY Format([DateField],"yy-mm"), PartNo

If you have multiple years of data you might want to add a where clause
to that.
SELECT Format([DateField],"mmm-yy") as MoYr
, PartNo, Sum(Qty) as TotalQty, Sum(Price) as TotalPrice
FROM [SomeTable]
WHERE [DateField] Between #2008-01-01# and #2008-12-31#
GROUP BY Format([DateField],"mmm-yy") as MoYr
, PartNo, Format([DateField],"yy-mm")
ORDER BY Format([DateField],"yy-mm"), PartNo

If you don't know how to use SQL view to build a query, post back and
ask how to do this in the query design view (the grid).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[roystonteo via AccessMonster.com]

Hi Damon,

Ok, i got it, the Mo become 1,2,3 and so on. I believe 1 stands for January
and 2 stands for Feburary.
But then i just include the field customer_name. When i try to add customer
name into the query, there are some errors.
Can i group them in customer name, part no ?
then shows
Customer name, part no, currency, total price, monthly quantity, uom

What is your opinion?

Let me qualify this by stating I am not a query guru, so there may be more
efficient and elegant ways of getting your answer in one query - I used two.
Query 1: SELECT Table1.DateSold, Table1.PartNo, Table1.TPrice, Table1.qty,
Sum([Tprice]*[qty]) AS SumP FROM Table1
GROUP BY Table1.DateSold, Table1.PartNo, Table1.TPrice, Table1.qty,
Table1.MyID;
**** substitute your names in the above and save****
Query 2: SELECT Query1.PartNo, Sum(Query1.SumP) AS SumOfSumP,
DatePart("m",[datesold]) AS Mo, Sum(Query1.qty) AS SumOfqty
FROM Query1
GROUP BY Query1.PartNo, DatePart("m",[datesold])
ORDER BY DatePart("m",[datesold]);

You could use the format function to make the date look nice - mine just
have the month number.

Damon

I have 1 table with 1 whole year of data.
date, partno, qty, price

[quoted text clipped - 6 lines]

Feb-09 #1232 1000 200
Mar-09 #1242 1000 200

 

[Damon Heron]

From what I understand, you have two conflicting goals: to show totals by
partno and month, and to show totals by customer. If two different
customers buy the same partno in the same month, you can't have the total $
of the partno and the separate total$ by each customer. This could probably
be most easily handled with a report.
Change Query1 slightly to include customer name:
SELECT MonthName(Month([datesold])) AS Mo, Table1.PartNo, Table1.TPrice,
Table1.qty, Sum([Tprice]*[qty]) AS SumP, Table1.CustomerName
FROM Table1
GROUP BY Month([datesold]), Table1.PartNo, Table1.TPrice, Table1.qty,
Table1.CustomerName;

Then use the report wizard to create a report based on Query1. Add sums in
the report wizard for qty and SumP. Group by customer. This will create a
report that shows all sales of each partno and totals by customer grouped by
month. If you want a report that shows the total sales by partno per month
without the customer, use the query to create a second report.

Damon

Hi Damon,

Ok, i got it, the Mo become 1,2,3 and so on. I believe 1 stands for
January
and 2 stands for Feburary.
But then i just include the field customer_name. When i try to add
customer
name into the query, there are some errors.
Can i group them in customer name, part no ?
then shows
Customer name, part no, currency, total price, monthly quantity, uom

What is your opinion?

Let me qualify this by stating I am not a query guru, so there may be more
efficient and elegant ways of getting your answer in one query - I used
two.
Query 1: SELECT Table1.DateSold, Table1.PartNo, Table1.TPrice, Table1.qty,
Sum([Tprice]*[qty]) AS SumP FROM Table1
GROUP BY Table1.DateSold, Table1.PartNo, Table1.TPrice, Table1.qty,
Table1.MyID;
**** substitute your names in the above and save****
Query 2: SELECT Query1.PartNo, Sum(Query1.SumP) AS SumOfSumP,
DatePart("m",[datesold]) AS Mo, Sum(Query1.qty) AS SumOfqty
FROM Query1
GROUP BY Query1.PartNo, DatePart("m",[datesold])
ORDER BY DatePart("m",[datesold]);

You could use the format function to make the date look nice - mine just
have the month number.

Damon

I have 1 table with 1 whole year of data.
date, partno, qty, price

[quoted text clipped - 6 lines]

Feb-09 #1232 1000 200
Mar-09 #1242 1000 200