I am trying to develop an access based database for a small company. I have made few tables, some of them are the "2011/2012 Total Production in $" and ""2011/2012 Total Production in CY." All four tables have a column "Actual Production"
Now, using query tool, i have to achieve two objectives.
I have accomplished first task which was simple; Make a query, get relevant fields, Summary Max & Min and finally distrubute in month wise. I.e Jan Max 5000, Min 2000...Feb Max 6000 Min 1000
Now what i desire is to display the date of max or min production for the month. So if April had MAX 181,218.00 in its month, I want it to display the date when it occurred (i,e April 10th 2012)
I am a beginner with Access, so please be as simple as possible.
Sample data gleaned from comment:
Month Sum of Prod Min Prod in $ Max Prod in $
------------- ------------- ------------- -------------
January 2011 $1,184,096.98 $20,486.40 $171,470.40
February 2011 $1,558,072.20 $44,962.20 $116,359.20
March 2011 $1,744,442.19 $19,200.00 $141,065.10
April 2011 $1,698,608.63 $27,500.70 $181,218.00
May 2011 $1,826,915.38 $37,996.00 $130,066.00
June 2011 $2,317,890.71 $42,645.00 $144,323.30
The above data were few of the fields gnerated by Query.
What I am looking for is
Month Date of Min Prod Min Prod in $ Date of Max Prod Max Prod in $
------------- ------------- ------------- ------------- -------------
January 2011 Jan 15 $20,486.40 Jan 10 $171,470.40
February 2011 Feb 20 $44,962.20 Feb 27 $116,359.20
March 2011 March 10 $19,200.00 March 1 $141,065.10
and so forth.
Is it possible to use a query to generate this result?
thanks!
Relation to the comment.
Relevant fields in one of the tables are.
Date of Activity Actual Production
------------- ------------- -------
1/3/2012 $20,486.40
1/4/2012 $44,962.20
1/5/2012 $19,200.00
I got lost in the details of your question. So I'll show you sample data and queries to get something like what I hope you want from that data.
Here is the contents of tblAbbas.
activity_date actual_production
1/3/2012 $20,486.40
1/4/2012 $44,962.20
1/5/2012 $19,200.00
2/1/2012 $3.00
2/2/2012 $2.00
2/3/2012 $1.00
Here is the SQL for a query named qryMonthStart. The purpose of this query is to determine the first day of the month which includes the activity_date.
SELECT
DateSerial(Year(activity_date),Month(activity_date),1)
AS month_start,
activity_date,
actual_production
FROM tblAbbas;
The query below uses qryMonthStart as its data source and gives me this result set.
month_year SumOf_production min_prod_date MinOf_production max_prod_date MaxOf_production
January 2012 $84,648.60 1/5/2012 $19,200.00 1/4/2012 $44,962.20
February 2012 $6.00 2/3/2012 $1.00 2/1/2012 $3.00
And the query SQL ...
SELECT
Format(grpby.month_start,"mmmm yyyy") AS month_year,
grpby.SumOf_production,
qmin.activity_date AS min_prod_date,
grpby.MinOf_production,
qmax.activity_date AS max_prod_date,
grpby.MaxOf_production
FROM
(
(
SELECT
month_start,
Sum(actual_production) AS SumOf_production,
Min(actual_production) AS MinOf_production,
Max(actual_production) AS MaxOf_production
FROM qryMonthStart
GROUP BY month_start
) AS grpby
INNER JOIN qryMonthStart AS qmin
ON
(grpby.MinOf_production = qmin.actual_production)
AND (grpby.month_start = qmin.month_start)
)
INNER JOIN qryMonthStart AS qmax
ON
(grpby.MaxOf_production = qmax.actual_production)
AND (grpby.month_start = qmax.month_start)
ORDER BY grpby.month_start;
Beware that query will fail ("data type mismatch in criteria expression") if you have Null for activity_date. The simplest way to prevent that is to clean out the Nulls then prohibit them in the activity_date column (set the Required property to Yes in table design view). If you decide you must allow Nulls in activity_date, you've got more work ahead.
Also note that query will give you multiple rows for the same month_year if the actual_production values in more than one of the daily records for that month matches the monthly minimum (MinOf_production). And the same situation will apply for the monthly maximum (MaxOf_production).