If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
1 table of 1 whole year data
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200908/1 |
#2
|
|||
|
|||
1 table of 1 whole year data
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 "roystonteo via AccessMonster.com" u48590@uwe wrote in message news:9a4b680aebc26@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200908/1 |
#3
|
|||
|
|||
1 table of 1 whole year data
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 wrote: 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 |
#4
|
|||
|
|||
1 table of 1 whole year data
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? Damon Heron wrote: 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 -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
1 table of 1 whole year data
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 "roystonteo via AccessMonster.com" u48590@uwe wrote in message news:9a7a5457b3370@uwe... 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? Damon Heron wrote: 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 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|