[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 567
  • Last Modified:

Parameter Query total for different sets of records

Currently, I have a query that needs to gather all records based on [AS of DATE] but it needs to be on a single report plotted by months.  Example:  All records  as of [01/01/2012] = 100, second parameter - all records as of [02/01/2012] = 150, third parameter - all records as of [03/01/2012] = 200 and so on until december.

Right now, I have created a query for each parameter to gather all records and then create another query to sum it up.  That's 24 queries which means more than likely, I am doing something wrong :-(

Is there any other way to accomplish this?
0
emacyam
Asked:
emacyam
  • 13
  • 10
  • 5
1 Solution
 
hnasrCommented:
Create a database with a table of  few records, 3 records for each parameter.
Show how do you want the final report.
Upload the database.
0
 
emacyamAuthor Commented:
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
0
 
aikimarkCommented:
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]

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
emacyamAuthor Commented:
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.
0
 
aikimarkCommented:
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
0
 
emacyamAuthor Commented:
Hi,

Attached is the 2003 version of the database.  THANK YOU!
Parameter-Test-Database-2003.mdb
0
 
hnasrCommented:
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.
0
 
aikimarkCommented:
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]);

Open in new window

0
 
emacyamAuthor Commented:
Thanks Aikimark.  I'll check it out as soon as I am home :-)
0
 
hnasrCommented:
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
0
 
emacyamAuthor Commented:
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.
0
 
aikimarkCommented:
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.
0
 
emacyamAuthor Commented:
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.
0
 
aikimarkCommented:
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

Open in new window

0
 
emacyamAuthor Commented:
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
0
 
aikimarkCommented:
there probably is.  I'm going to bed soon, so my right hemisphere will have a chance to mull this over.
0
 
emacyamAuthor Commented:
Thanks really - thank you :-)
0
 
hnasrCommented:
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
0
 
aikimarkCommented:
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.
0
 
aikimarkCommented:
In the attached database, I've created a lag table and a query (Query1).  The query will prompt you for a single date and show you the next thirteen months.

Note: you can change the Format() string to show the month name as well as the year: "mmmm yyyy"

Note: If you are going to show 24 months, it might be time to consider a tally/number table solution.  I've written an article on this: http:A_5410.html
Parameter-Test-Database-2003.mdb
0
 
emacyamAuthor Commented:
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).
0
 
emacyamAuthor Commented:
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!
0
 
emacyamAuthor Commented:
Hi,

I meant stop counting the 13 months of the As of Date if the input is >Date()
0
 
aikimarkCommented:
Sure.  Add a Where condition to each of the unioned queries that compares the DateAdd() expression with Date().
0
 
hnasrCommented:
'Thanks but I guess you overlooked my query "Sum of Records Parameter".  '
So 25 comments and no resolution for a simple problem.
This is partly, because you did not supply me with few records as input with the respective output.
Your excel sheet was missing the output part.
As I mentioned, you don't need such individual queries, and hence a design change is required.

I'll drop here, but available if required.
0
 
emacyamAuthor Commented:
Great! Thanks Aikimark....  I really appreciate the effort and the solution you offered.

Thank you too Hnasr for checking into the question.
0
 
emacyamAuthor Commented:
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 :)
0
 
aikimarkCommented:
Using a Tally table should greatly simplify the query.  You no longer need to have a separate unioned select for each month.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 13
  • 10
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now