I have a database that keeps track of cases assigned to my department. Each case may have a dollar amount associated with it. There are three units within my department and cases are assigned to each one. I have created a 12 month dashboard report that allows the user to specify the month at which to end the report and the report should then, for each of the prior 12 months, calculate the number of cases opened, closed and in progress as well as the dollar value totals for each unit. This is obviously done for each of the 12 months on the report. I therefore have approx. 324 text boxes the majority of which are calculting either a DSUM or DCOUNT. With my database unsplit, the report takes about 30-40 mins to run. I have now split my database with the back end on a server and the front end on my local machine. I split the database because there will be multiple users. I now have no idea how long the report will take because I do not have the patience to wait for it to be done. My DSUM and DCOUNT are all based on a query...based on other posts on here I will now change that to tables. Does anyone know if I have the calculations done in a query and then use the query as the report's source, will the report be faster. Or any other suggestions would be very helpful. Please ask for clarifiaction on anything here that is confusing. Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
DSUM on Query or report - Which would be faster
- Thread starter btrini10
- Start date
In general you should avoid dsum and dcount and use queries instead.
Maybe if you post some dsum and dcount examples and the SQL for the query that they are running off of as well as the SQL statment for the query that is the recordsource of the report/form or whatever they are run on.... If you are doing it all in a query now, just post the one big SQL statement. This may lead to other questions or suggestions about database structure.
Maybe if you post some dsum and dcount examples and the SQL for the query that they are running off of as well as the SQL statment for the query that is the recordsource of the report/form or whatever they are run on.... If you are doing it all in a query now, just post the one big SQL statement. This may lead to other questions or suggestions about database structure.
I agree totally with lameid. It looks to me like you could increase performance tremendously with a crosstab query or other solution.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #4
=DSum("Nz([CASE_ONLY_QUERY]![EST_LOSS_TC]) + Nz([CASE_ONLY_QUERY]![EST_LOSS]) + Nz([CASE_ONLY_QUERY]![EST_LOSS_OTHER])","[CASE_ONLY_QUERY]","[CASE_ONLY_QUERY]![UNIT]=1 AND ([CASE_ONLY_QUERY]![DATE_OPENED] Between (DateSerial(Year(Forms![Report_Form]![DASHBOARD_DATE]),-11+Month(Forms![Report_Form]![DASHBOARD_DATE]),1)) AND DateSerial(Year(Forms![Report_Form]![DASHBOARD_DATE]),-10+Month(Forms![Report_Form]![DASHBOARD_DATE]),0))"
Guys, above is one of the calculations being done. I will be changing the reference to tables instead of queries. So what I have is one of these for each of 12 months.
Thanks for your all suggestions. I guess one option is to do this in a query?
Guys, above is one of the calculations being done. I will be changing the reference to tables instead of queries. So what I have is one of these for each of 12 months.
Thanks for your all suggestions. I guess one option is to do this in a query?
If you want some tips, you will need to provide some information about your tables, data, and what you want to report out.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
Ok the first thing I did to convert this to SQL was to arrange it so I could read it easier. Secondly I stole the appropriate parts out of the dsum and put them in a Select SQL query. Note in the query I had to put the Sum in the select clause. I also changed some "!" to ".". This is a matter of prefered syntax. Ideally you always use the dot for Table_Name.Field_Name syntax.
Looking at this though, I would assume you have a series of DSum functions currently and they are intended to show the sum for Each month? Try this:
Code:
=DSum("[Red]Nz([CASE_ONLY_QUERY]![EST_LOSS_TC]) + Nz([CASE_ONLY_QUERY]![EST_LOSS]) + Nz([CASE_ONLY_QUERY]![EST_LOSS_OTHER])[/Red]",
"[Green][CASE_ONLY_QUERY][/Green]",
"[Blue][CASE_ONLY_QUERY]![UNIT]=1 AND
([CASE_ONLY_QUERY]![DATE_OPENED] Between (DateSerial(Year(Forms![Report_Form]![DASHBOARD_DATE]),-11+Month(Forms![Report_Form]![DASHBOARD_DATE]),1)) AND
DateSerial(Year(Forms![Report_Form]![DASHBOARD_DATE]),-10+Month(Forms![Report_Form]![DASHBOARD_DATE]),0))[/Blue]"
Code:
Select Sum([Red]Nz([CASE_ONLY_QUERY].[EST_LOSS_TC]) + Nz([CASE_ONLY_QUERY].[EST_LOSS]) + Nz([CASE_ONLY_QUERY].[EST_LOSS_OTHER])[/Red])
From [Green][CASE_ONLY_QUERY][/Green]
Where [Blue][CASE_ONLY_QUERY].[UNIT]=1 AND
([CASE_ONLY_QUERY].[DATE_OPENED] Between (DateSerial(Year(Forms![Report_Form]![DASHBOARD_DATE]),-11 + Month(Forms![Report_Form]![DASHBOARD_DATE]),1)) AND
DateSerial(Year(Forms![Report_Form]![DASHBOARD_DATE]),-10 + Month(Forms![Report_Form]![DASHBOARD_DATE]),0))[/Blue]Looking at this though, I would assume you have a series of DSum functions currently and they are intended to show the sum for Each month? Try this:
Code:
Select Cdate(Format([CASE_ONLY_QUERY].[DATE_OPENED], mm/1/yyyy")) as Month_Opened, Sum([Red]Nz([CASE_ONLY_QUERY].[EST_LOSS_TC]) + Nz([CASE_ONLY_QUERY].[EST_LOSS]) + Nz([CASE_ONLY_QUERY].[EST_LOSS_OTHER])[/Red])
From [Green][CASE_ONLY_QUERY][/Green]
Where [Blue][CASE_ONLY_QUERY].[UNIT]=1
Group By Cdate(Format([CASE_ONLY_QUERY].[DATE_OPENED], mm/1/yyyy"))- Thread starter
- #7
Thanks for the suggestion lameid. I think I understand the SQL. I also need to report a count of cases opened, closed and in progress. Would I then remove the where clause and instead group on all my criteria, e.g. UNIT, opened Date, Closed Date? Also keep in mind I would then need to report the numbers depending on what months a user wishes to see.
Dhookom, my Case table contains a unique Case_ID field and fields for the unit, loss, recovery etc. I am trying to report on cases cases open from the prior month, cases opened, closed and those going into the following month. I have a form on which the user selects the month at which they wish to end the rolling 12 month period. The report should then generte the above values for all 12 periods.
Hope this helps.
I have attached a pdf of copy of what I would like the report to look like.
Dhookom, my Case table contains a unique Case_ID field and fields for the unit, loss, recovery etc. I am trying to report on cases cases open from the prior month, cases opened, closed and those going into the following month. I have a form on which the user selects the month at which they wish to end the rolling 12 month period. The report should then generte the above values for all 12 periods.
Hope this helps.
I have attached a pdf of copy of what I would like the report to look like.
Your write on for the grouping.
For your criteria, you can still base it off the form, see the criteria in my first SQL example. You can just take out the Unit criteria and add the grouping to that query.
Also you linked to an attachment on your computer, we can't see that. You'd have to put it on the internet somewhere. But once you get the query, it should be relatively easy to get it to look right in a report.
For your criteria, you can still base it off the form, see the criteria in my first SQL example. You can just take out the Unit criteria and add the grouping to that query.
Also you linked to an attachment on your computer, we can't see that. You'd have to put it on the internet somewhere. But once you get the query, it should be relatively easy to get it to look right in a report.
You stated "Case table contains a unique Case_ID field and fields for the unit, loss, recovery etc" but I don't see any date field. IMO, you haven't described your table structure, fields, data types, and?or your reporting requirements to the extent that my old brain can be of much help.
It really helps us if you refer to your field names like [Close Date] and use their real names. Table names should be referenced similarly but make sure you specify if they are tables or queries.
Duane
Hook'D on Access
MS Access MVP
It really helps us if you refer to your field names like [Close Date] and use their real names. Table names should be referenced similarly but make sure you specify if they are tables or queries.
Duane
Hook'D on Access
MS Access MVP
btrini10,
I don't think I read your last post close enough and I have to agree with Duane that it would be easier to help you if you posted your table structure and described the results.
As to whether you want to group by criteria fields or not is really determined on whether or not you want to show it as part of the detail of the query or if you just want to only use it as criteria.
I don't think I read your last post close enough and I have to agree with Duane that it would be easier to help you if you posted your table structure and described the results.
As to whether you want to group by criteria fields or not is really determined on whether or not you want to show it as part of the detail of the query or if you just want to only use it as criteria.
- Thread starter
- #11
Duane,lameid, I apologize for being so vague.
OK, My PK is [CASE_FILE_ID] which is text and it is made up of the year in which the case is enterd and a counter that numbers each case incrementally. Once the year changes, the counter resets to 001, e.g. 2009-001.
I have a [DATE_OPENED],[DATE_CLOSED] which are both Date/Time format.
I have a numeric field which identifies the Unit to which the case belongs and there are currently three units; 1, 2 and 3.
I have 9 Currency fields that need to be Summed; [EST_LOSS1],[EST_LOSS2],[EST_LOSS3],[LITIGATION1],[LITIGATION2],[LITIGATION3],[RECOVERY1],[RECOVERY2],[RECOVERY3].
I can use my table instead of the query to calculate all my values.
On the form that generates the report, I have two text boxes that allow the user to select the month and year at which to end the report. The year box is free form, but defaults to the current year. The month box is actually a drop down that lists each month and this too defaults to the current month. This would be the dashboard date in my DSUM statement.
Below is a sample of what my table should look like, with the user selecting the last month of the report.
This format is repeated for each of the three units and then at the top of the report I calculate totals.
For most of the values, I am currently using either a DSUM or DCOUNT on the report.
Hope this all makes sense and let me know if you guys have any further questions.
YTD08 Jan-08 FEB-09 Mar-09
In Progress 15 10 5 4
Opened 50 6 8
Closed 55 11 9
IN Prog EOM 10 5 4
Losses 1,000 20,000 15,000
Recovery 1,000 10,000 5,000
Litigation 0 5,000 2,000
OK, My PK is [CASE_FILE_ID] which is text and it is made up of the year in which the case is enterd and a counter that numbers each case incrementally. Once the year changes, the counter resets to 001, e.g. 2009-001.
I have a [DATE_OPENED],[DATE_CLOSED] which are both Date/Time format.
I have a numeric field which identifies the Unit to which the case belongs and there are currently three units; 1, 2 and 3.
I have 9 Currency fields that need to be Summed; [EST_LOSS1],[EST_LOSS2],[EST_LOSS3],[LITIGATION1],[LITIGATION2],[LITIGATION3],[RECOVERY1],[RECOVERY2],[RECOVERY3].
I can use my table instead of the query to calculate all my values.
On the form that generates the report, I have two text boxes that allow the user to select the month and year at which to end the report. The year box is free form, but defaults to the current year. The month box is actually a drop down that lists each month and this too defaults to the current month. This would be the dashboard date in my DSUM statement.
Below is a sample of what my table should look like, with the user selecting the last month of the report.
This format is repeated for each of the three units and then at the top of the report I calculate totals.
For most of the values, I am currently using either a DSUM or DCOUNT on the report.
Hope this all makes sense and let me know if you guys have any further questions.
YTD08 Jan-08 FEB-09 Mar-09
In Progress 15 10 5 4
Opened 50 6 8
Closed 55 11 9
IN Prog EOM 10 5 4
Losses 1,000 20,000 15,000
Recovery 1,000 10,000 5,000
Litigation 0 5,000 2,000
Preferably for your reporting requirement, you would have a separate table with status and date related to this so you could have rows for each.
That's a little involved for me to spend the time on to bang out the whole thing.
Conceptially, I would make a query that groups on the Year of Date Opened and the month of Date opened formated as mmm (use the format function). Count on whatever is appropriate. Change this to a crosstab query with your month as your row heading and the count as the value. For month specify the column headings in order...
"Jan", "Feb", "Mar" etc.
That way it will always have all 12 months in the result.
Use a report wizard. One by one change the month header lables to text boxes...
="Jan-" & Right(YourYearField,2)
Now you have a report that just works for opened.
Make the same query for Closed.
Make a Union Query for those two queries and make that the recordsource of your report.
Remember copy paste and search and replace are your friend (I use wordpad all the time to modify similar queries).
From here I think you have the concept.
Based on your data you would be able to report much easier if you were to swap rows and columns on your report.
That's a little involved for me to spend the time on to bang out the whole thing.
Conceptially, I would make a query that groups on the Year of Date Opened and the month of Date opened formated as mmm (use the format function). Count on whatever is appropriate. Change this to a crosstab query with your month as your row heading and the count as the value. For month specify the column headings in order...
"Jan", "Feb", "Mar" etc.
That way it will always have all 12 months in the result.
Use a report wizard. One by one change the month header lables to text boxes...
="Jan-" & Right(YourYearField,2)
Now you have a report that just works for opened.
Make the same query for Closed.
Make a Union Query for those two queries and make that the recordsource of your report.
Remember copy paste and search and replace are your friend (I use wordpad all the time to modify similar queries).
From here I think you have the concept.
Based on your data you would be able to report much easier if you were to swap rows and columns on your report.
Did I miss your table name?
I don't care for your table structure that stores data values in field names (your 9 currency fields). This is not normalized and may need to be normalized with a union query to get the data into a flexible reporting structure.
Can we assume that if a Case is opened in Jan and closed in Mar, it gets InProgress for all three months?
Can we assume that In Prog EOM for Jan are those that were open on Jan 31?
How do you allocate the 9 currency fields? Which month gets the value for a case that opens in Jan and closes in Mar?
I would probably create a table of months:
tblMonths
===============
MthEnd DateTime
Add values for 1/31/2008 - 12/31/2015 or whatever
This will allow you to easily query the number of cases opened, closed, and in progress during any give month:
I have FAQs in the query and report forums on creating monthly crosstab reports and creating crosstabs with multiple values.
Duane
Hook'D on Access
MS Access MVP
I don't care for your table structure that stores data values in field names (your 9 currency fields). This is not normalized and may need to be normalized with a union query to get the data into a flexible reporting structure.
Can we assume that if a Case is opened in Jan and closed in Mar, it gets InProgress for all three months?
Can we assume that In Prog EOM for Jan are those that were open on Jan 31?
How do you allocate the 9 currency fields? Which month gets the value for a case that opens in Jan and closes in Mar?
I would probably create a table of months:
tblMonths
===============
MthEnd DateTime
Add values for 1/31/2008 - 12/31/2015 or whatever
This will allow you to easily query the number of cases opened, closed, and in progress during any give month:
Code:
SELECT tblMonths.MthEnd, Count(tblCases.CASE_FILE_ID) AS Cases,
Sum(Abs(Format([MthEnd],"yyyymm")=Format([DATE_OPENED],"yyyymm"))) AS Opened,
Sum(Abs(Format([MthEnd],"yyyymm")=Format([DATE_CLOSED],"yyyymm"))) AS Closed
FROM tblCases, tblMonths
WHERE (((Format([MthEnd],"yyyymm")) Between Format([DATE_OPENED],"yyyymm") And Format([DATE_CLOSED],"yyyymm")))
GROUP BY tblMonths.MthEnd
ORDER BY Format([MthEnd],"yyyymm");I have FAQs in the query and report forums on creating monthly crosstab reports and creating crosstabs with multiple values.
Duane
Hook'D on Access
MS Access MVP
Duane,
I like your query. Wish I had thought of it. Although I might make a change to accomodate cases currently open, not closed...
I like your query. Wish I had thought of it. Although I might make a change to accomodate cases currently open, not closed...
Code:
WHERE (((Format([MthEnd],"yyyymm")) Between Format([DATE_OPENED],"yyyymm") And Format([Red]NZ([/red][DATE_CLOSED][red],Date())[/red],"yyyymm")))Thanks lameid. I just wanted to provide a push in what I consider a much better direction for the report and its record source. I hadn't given much thought to not closed cases. I had created a couple hundred records in Excel and they all had closed dates.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
Come to think of it tblMonths only needs 1 field, a date with the first day of each month as the list... I was asleep at the wheel on that one. (I have done a similar thing more than once in the past). Interesting though, the Select clause is optimal. That I haven't done before :-D
Although while your at it you might as well have your tblMonth table have the First and last day of the month and the number of days in the month. Each often comes in handy.
Code:
SELECT tblMonths.MthEnd, Count(tblCases.CASE_FILE_ID) AS Cases,
Sum(Abs(Format([MthBegin],"yyyymm")=Format([DATE_OPENED],"yyyymm"))) AS Opened,
Sum(Abs(Format([MthBegin],"yyyymm")=Format([DATE_CLOSED],"yyyymm"))) AS Closed
FROM tblCases, tblMonths
WHERE [MthBegin] Between [DATE_OPENED] And NZ([DATE_CLOSED],Date())
GROUP BY tblMonths.MthEnd
ORDER BY Format([MthEnd],"yyyymm");Although while your at it you might as well have your tblMonth table have the First and last day of the month and the number of days in the month. Each often comes in handy.
I used a single field in tblMonths of MthEnd because it looked like there was a need for some kind of balance at the end of the month.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #18
Thank you so much for all your input here.
Duane: 1) The table name is Cases_table. 2) The EOM value will always be the same as the following BOM value and the EOM is calculate based on BOM+Opened-Closed. 3) All currency fields are allocated to the month in which the case is opened.
I think one field in the tblmths to identify the month should be fine since all we really wnat to capture is activity for any particular month.
Duane, you mentioned normalizing the currency fields to get a more flexible reporting structure. Do you mind elaborating on this a bit so I can better understand what you mean? I will also check out your FAQ.
Hey Lameid, I am not sure I will need to maintain cases opened that have not been closed since this can be calculated either on my report or query based on the BOM+OPENED-CLOSED formula. Would you agree and is it best to do this in the query or report?
Thanks again to both of you.
Duane: 1) The table name is Cases_table. 2) The EOM value will always be the same as the following BOM value and the EOM is calculate based on BOM+Opened-Closed. 3) All currency fields are allocated to the month in which the case is opened.
I think one field in the tblmths to identify the month should be fine since all we really wnat to capture is activity for any particular month.
Duane, you mentioned normalizing the currency fields to get a more flexible reporting structure. Do you mind elaborating on this a bit so I can better understand what you mean? I will also check out your FAQ.
Hey Lameid, I am not sure I will need to maintain cases opened that have not been closed since this can be calculated either on my report or query based on the BOM+OPENED-CLOSED formula. Would you agree and is it best to do this in the query or report?
Thanks again to both of you.
If you are talking about my revision to Duane's SQL, then you have to include the records in via the criteria in order to do math on them. In general I think it is faster to do calculations in a query. Sometimes a query will not run because it is "too complex" at which point you are forced to move calculations to the report.
If you mean something else, please post the SQL you are using.
To normalize:
You need to create another table for your currency fields.
You need three fields plus a unique identifer for the Primary Key (most likely an autonumber).
1) Key that matches the Primary Key of Cases_table; CASE_FILE_ID. This is not unique in this table it relates this table to the case table.
2) Field that indicates the type of charge / currency.
3) Currency field itself.
You need to create another table for your currency fields.
You need three fields plus a unique identifer for the Primary Key (most likely an autonumber).
1) Key that matches the Primary Key of Cases_table; CASE_FILE_ID. This is not unique in this table it relates this table to the case table.
2) Field that indicates the type of charge / currency.
3) Currency field itself.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question