Parameter Query total for different sets of records

Thanks for the reply.

Attached is an example of how the queries are currently setup. Please use the following As of date:

Date 1:  03/01/2012
Date 2:  04/01/2012
Date 3:  05/01/2012

Sample database has no forms.  Please run query "qry_Total_Records_Parameter".   The data from this query is what needs to be plotted in the report.  

Wondering if there is another way to approach the seperate queries to sum each of the "As of Date" parameter.

Thanks.
Parameter-Test-Database.accdb

are you summing or counting the values?

The approach I would take is to have a single starting data parameter and the counting/summing expressions use that date with an offset.  Your columns can use (Select ...) or a domain aggregate function, such as DCount() or DSum().

thirteen month count example:

Select Count(*) , 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",1, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",2, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",3, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",4, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",5, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",6, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",7, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",8, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",9, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",10, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",11, [parmStartDate])), 
(Select Count(*) From MyTable Where [As of Date] <= DateAdd("m",12, [parmStartDate]))
From MyTable
Where [As of Date] <= [parmStartDate]

Select allOpen in new window

Thanks for the reply Aikimark.

I am trying to sum the records from the [AS OF DATE] (one for each month) then put that 'sum' in a column so that it could be plotted later on a graph.

Example based on the db I attached:
All records as of 03/31/2012 (MARCH):  4
All records as of 04/20/2012 (APRIL): 10
All records as of 05/31/2012 (MAY): 17

Right now, I tried the code snippet you sent on the sample database that I attached previously and it seems that I am missing a step.  I used 05/31/2012 [As of Date] and 03/31/2012 for the [parmStartDate],  and I got "17" starting with Expr1002 up to Expr1012.  

*Also, there will be different "AS of Date" for each month BUT, it will always be at at the end of the month.  i.e. 03/31/2012, 04/30/2012, and 05/31/2012.  The As of Date or (start date) can be any of the months.

Again, much thanks for the help.

I only have Access2003 on my home system.  If you can post a copy of your database in that version, I'll take a look at it

Hi,

Attached is the 2003 version of the database.  THANK YOU!
Parameter-Test-Database-2003.mdb

Confused with date format, is it mm/dd or dd/mm?
Please list 5 records or few more if you feel necessary and the expected output.

It might be simpler than you think.  Please compare the output of this query with what you seek.

SELECT Year([Date Received])*100+Month([Date Received]) AS DRevdMonth, 
Count(tblMaster.Items) AS CountOfItems
FROM tblMaster
GROUP BY Year([Date Received])*100+Month([Date Received]);

Select allOpen in new window

Thanks Aikimark.  I'll check it out as soon as I am home :-)

Just to check if I understand, this is for one year, modification required if more years are involved.
Reduced to 1 query.
Parameter-Test-Database-2003-2.mdb

Hi AikiMark and HNasr,

Thank you both for your feedback.... Both your solutions offer a way to get the count of records for a certain month example January 2012, February 2012, and March 2012.

However, the "AS OF DATE" is not only based on a Count per month but some other calculations  - please see "AS OF DATE 1" query, RecordCountTest.

And also, the "AS of Date" is required to be a parameter wherein the users would need to put in the data.

Please run/view the "qry_Total_Records_Parameter" and use the following As of date:

As of Date 1:  03/01/2012
As of Date 2:  04/01/2012
As of Date 3:  05/01/2012

The result from this query is what needs to be plotted in the report.  Thank you.

Your queries are using a date difference as a 'record count'.  That is very confusing.  Can you tell us or show us what you need as output?  I can see the different criteria for pens versus erasers, but that really isn't helping me understand the nature of your problem in a manner that I can see a path to a solution for you.

There are only 25 rows in your table, maybe you can create an Excel spreadsheet with the data laid out in the configuration you need.

Hi Aikimark,

I appreciate your patience...
If you run the qry_Total_Records_Parameter using the sample dates, you will get the output that I need.  This is for a backlog report wherein the user would like to know how many items have been received and yet was not given out to employees based on the criteria in (RecordCountTest) .  

I have scaled down this database just to put in the sample dates and generate some data.  As you will see there are 0 records for March, 4 records for April and 14 records for May, when the qry_total_records_parameter is viewed.  Thanks.

I see you are summing the counts.

What I don't see are the data (in any table) that reflect the distribution of items.  I only see received date and the type of item.  Nothing about how many of such items were received on that date (so one is assumed).

Although I see the query output, it doesn't mean anything to me.  I want you to explain this.  Trying to infer what you need from these queries is not productive.  

Also, you have mentioned that there will be many more columns than what we are seeing.  So that is another reason to not trust my eyes to extrapolate the three month's worth of data in tblMaster.

Given the query output, you might tell me what the 4 and 14 numbers for April and May represent.

Month Name	Total Records
January  	0
February	0
March     	0
April        	4
May         	14
June        	0
July          	0
August    	0
September	0
October 	0
November	0
December	0

Select allOpen in new window

Hi Aikimark,

PER DATABASE:
RecordCountTest field:
(((tblMaster.Items)="Pens") AND (([As of Date 1]-[Date Received])>60)) OR (((tblMaster.Items)="Erasers") AND (([As of Date 1]-[Date Received])>90))
Based on the records on the db, if the user enter (03/01/2012), THERE are 0 (Zero) Records that falls into this criteria for MARCH.

(((tblMaster.Items)="Pens") AND (([As of Date 2]-[Date Received])>60)) OR (((tblMaster.Items)="Erasers") AND (([As of Date 2]-[Date Received])>90))
Based on the records on the db, if the user enter  (04/01/2012), THERE ARE 4 (Four) Records that falls into this criteria for APRIL.


(((tblMaster.Items)="Pens") AND (([As of Date 3]-[Date Received])>60)) OR (((tblMaster.Items)="Erasers") AND (([As of Date 3]-[Date Received])>90))
Based on the records on the db, if the user enter (05/01/2012), THERE ARE 14 Records that falls into this criteria for MAY.

If the user continues to enter another date (as of date) parameter:
(((tblMaster.Items)="Pens") AND (([As of Date 4]-[Date Received])>60)) OR (((tblMaster.Items)="Erasers") AND (([As of Date 4]-[Date Received])>90))
Based on the records on the db, if the user enter (as of date 4 - 06/01/2012), THERE ARE 20 Records that falls into this criteria for JUNE.

Please see attached excel file.

Actually my original question was:

Right now, I have created a query for each parameter (one for each month - January-December, please see queries As of Date 1, As of Date 2, As of Date 3) to gather all records and then create another query to sum it up (Sum of Date 1, Sum of Date 2, Sum of Date 3).  That's 24 queries (right now only 6 are showing on the sample database).  Then another query to "SUM all Records Parameter", then another query to plot the data for the report (qry_Total_Records_Parameter) where all I need is Month and Total Records column...

Is there any other way to accomplish this?

Thanks a bunch....
Result-TblMaster.xls

there probably is.  I'm going to bed soon, so my right hemisphere will have a chance to mull this over.

Thanks really - thank you :-)

Try this, run Query1
Enter 4/1/2012, 5/1/2012, 6/1/2012

You may need to improve the design, but all depends on understanding the problem.
Parameter-Test-Database-3.accdb

You have data entry errors in your tblMaster table.  For ID in (5,10,15,20, 25) you have misspelled "Erasers".

I understand that the 60 day and 90 day  values are minimum intervals before an item can be included for counting.  The term I'm using is Lag time.

I still think you're missing the data that will remove items from counting.  You are asking half the question.

Hi Hnasr,

Thanks but I guess you overlooked my query "Sum of Records Parameter".  

Your Query 1 is the same setup.  I was hoping for a solution that doesn't need those other 3 queries (As of Date 1, As of Date 2, As of Date 3).

Hi Aikimark,

I like that lagtime table :-)  And you are correct, I am asking half the question hoping I can figure out the rest once I have an idea how to approach the issue of creating numeries queries for each of the "As of Date".

Is there a way to "zero" out the rest of the 13 months of the "As of Date" entered is >=Date()?

THANK YOU!

Hi,

I meant stop counting the 13 months of the As of Date if the input is >Date()

Sure.  Add a Where condition to each of the unioned queries that compares the DateAdd() expression with Date().

'Thanks but I guess you overlooked my query "Sum of Records Parameter".  '

I'll drop here, but available if required.

Great! Thanks Aikimark....  I really appreciate the effort and the solution you offered.

Thank you too Hnasr for checking into the question.

THANK YOU...!

I like the idea of the lag time table and I could tailor the solution you offered to the other reports that I have.  I will also checkout the tally/number table solution if I need to show 24 months.  I appreciate your help and expertise in this matter :)

Using a Tally table should greatly simplify the query.  You no longer need to have a separate unioned select for each month.