Total Daily Sales Queries By Model/Total

[edisonl]

Hi,

1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.

2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)

3) I start with daily (Lets don't be too overly ambitious).

4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.

5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?

PS: Please forgive my ignorance

Thanks (In advance) & God Bless.

 

[jzwp22]

To do what you are describing, it would probably be best to use a single form with the calendar control as well as an option group where the user can select the type of sales report (daily, monthly, quarterly or yearly). You would then execute some code that will decide which query to run depending on what the user selected. The query for the daily total is fairly straightforward. Something similar to this should do the trick. The [date1] and [date2] represent the values from your calendar control.

daily sales:
SELECT tblSales.dteSale, tblSales.ItemID, Sum(tblSales.UnitsSold) AS SumOfUnitsSold
FROM tblSales
WHERE tblSales.dteSale between [date1] and [date2]
GROUP BY tblSales.ItemID, tblSales.dteSale

For the monthly, quarterly and yearly sales you would have to use the following queries respectively:

for monthly sales:
SELECT tblSales.ItemID, Sum(tblSales.UnitsSold) AS SumOfUnitsSold, datepart("m",tblSales.dteSale) & " " & datepart("yyyy",tblSales.dteSale) AS MonthandYear
FROM tblSales
WHERE tblSales.dteSale between [StartDate] and [EndDate]
GROUP BY tblSales.ItemID, datepart("m",tblSales.dteSale) & " " & datepart("yyyy",tblSales.dteSale);



for quarterly sales

SELECT tblSales.ItemID, Sum(tblSales.UnitsSold) AS SumOfUnitsSold, datepart("q",tblSales.dteSale) & " " & datepart("yyyy",tblSales.dteSale) as QTYandYear
FROM tblSales
Where tblSales.dteSale between [StartDate] and [EndDate]
GROUP BY tblSales.ItemID,datepart("q",tblSales.dteSale) & " " & datepart("yyyy",tblSales.dteSale)

for yearly sales

SELECT tblSales.ItemID, Sum(tblSales.UnitsSold) AS SumOfUnitsSold, datepart("yyyy",tblSales.dteSale) AS SalesByYear
FROM tblSales
WHERE tblSales.dteSale between [StartDate] and [EndDate]
GROUP BY tblSales.ItemID, datepart("yyyy",tblSales.dteSale);

 

[edisonl]

Ok

Hi,

Right, will go test it out & see if it works.

Thanks & God Bless