DSUM on Query or report - Which would be faster

[btrini10]

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.

 

[btrini10]

Thanks for the clarifiaction, Lameid. However I would have a one to one relationship between the Case table and the Currency table, so wouldn't it be nomalized by including all felds in the case table that are solely depndant on the key filed of the Case Table. Wouldn't I create another table to eliminate many to many relationships or where I have fields on my table that may dependant on fields other than the key field?

 

[lameid]

The idea is that a charge is an entity/table.

So you have one column for charges and another column to describe the charge (my number 2). This way you will have a record for each column you have now for each case in the new table or a one to many relationship. Technically now, you are violating normalization by having multiple things of the samething as columns. By adding a table, it is easy to add new kinds of charges as well. With all the charges in a separate table, you can simply sum on the charge column to get your total charges. It is even easy to list them down the side of the page.

 

[btrini10]

Thanks for the explanation lameid.
OK, I now have a query with the following SQL.
SELECT QRY_MTH_YR_UNIT.FULL_DATE, QRY_MTH_YR_UNIT.MONTH_YEAR, QRY_MTH_YR_UNIT.[UNIT ID], QRY_COUNT_OPENED_CASES.CountOfCASE_REAL_ID AS CASES_OPENED, QRY_COUNT_CLOSED_CASES.CountOfCASE_REAL_ID AS CASES_CLOSED, Nz([SumOfEST_LOSS_TC_FUNDS])+Nz(QRY_NON_CASE_MTHYR!SumOfPRTCPNT_TC_FUNDS_LOSS)+Nz(QRYOPENED!SumOfEST_LOSS_OTHER)+Nz(QRYOPENED!SumOfEST_LOSS_TIAA)+Nz(QRY_NON_CASE_MTHYR!SumOfPRTCPNT_TIAA_LOSS)+Nz(QRYOPENED!SumOfRE_MEMO_AMOUNT) AS LOSS, NZ([SumOfLITIGATION_AMT_TC])+Nz(QRYOPENED!SumOfLITIGATION_AMT_TIAA)+Nz(QRYOPENED!SumOfLITIGATION_AMT_OTH)+Nz(QRYOPENED!SumOfRE_LITIGATION_AMT) AS LITIGATION, Nz(QRYOPENED!SumOfRECOVERY_TIAA)+Nz([SumOfRECOVERY_TC])+Nz(QRY_NON_CASE_MTHYR!SumOfPRTCPNT_TC_FUNDS_RECOVERY)+Nz(QRYOPENED!SumOfRECOVERY_OTH)+Nz(QRYOPENED!SumOfRE_RECOVERY_AMOUNT) AS RECOVERY
FROM (((QRY_MTH_YR_UNIT LEFT JOIN QRYOPENED ON (QRY_MTH_YR_UNIT.[UNIT ID] = QRYOPENED.UNIT) AND (QRY_MTH_YR_UNIT.MONTH_YEAR = QRYOPENED.MTH_YR)) LEFT JOIN QRY_NON_CASE_MTHYR ON (QRY_MTH_YR_UNIT.[UNIT ID] = QRY_NON_CASE_MTHYR.UNIT) AND (QRY_MTH_YR_UNIT.MONTH_YEAR = QRY_NON_CASE_MTHYR.MTH_YR)) LEFT JOIN QRY_COUNT_OPENED_CASES ON (QRY_MTH_YR_UNIT.MONTH_YEAR = QRY_COUNT_OPENED_CASES.MTH_YR) AND (QRY_MTH_YR_UNIT.[UNIT ID] = QRY_COUNT_OPENED_CASES.UNIT)) LEFT JOIN QRY_COUNT_CLOSED_CASES ON (QRY_MTH_YR_UNIT.MONTH_YEAR = QRY_COUNT_CLOSED_CASES.MTH_YR) AND (QRY_MTH_YR_UNIT.[UNIT ID] = QRY_COUNT_CLOSED_CASES.UNIT)
ORDER BY QRY_MTH_YR_UNIT.FULL_DATE, QRY_MTH_YR_UNIT.[UNIT ID];

As Duane suggested, I created a table of months.(tbl_Mth_YR).

I then created a query using using tbl_MTH_YR and my tbl_UNIT to get a record for each of my three units for each month. (QRY_MTH_YR_UNIT)

I created queries to calculate the count of cases OPENED and CLOSED ( I was having difficulty getting an accurate count when Grouping, so I created the count seperately. A COUNT DISTINCT would have been ideal).

I then created a query on OPENED_DATE to calculate totals for all my currency fields.

Joining my queries together I am able to get a record for each month for each unit showing all my totals and count. My issue now is how do I get this into the report format that I want. I could probably use a Dlookup for each value on my report, but I think this might be way too time consuming. I read Duanes FAQ on creating multiple columns in a crosstab but am not sure how to do that on this query. I think if I could swap the row heading s with the column headings it might work. Then I would have three columns for each month, giving 36 columns for a year and my rows would be OPENED, CLOSED, LOSSES, LITIGATION, RECOVERY.
Any ideas would be welcomed.
P.S. My SQL knowlege is extremely weak.

 

[dhookom]

I can't picture what you data looks like now. I would first do my best to get it in a very normalized structure using union queries. Then I would use the months table to be able to assign values to month ranges. From there I would create a crosstab query. If you need multiple values in the crosstab, use my FAQ in the queries.

Duane
Hook'D on Access
MS Access MVP

 

[btrini10]

Hey Duane, thanks for the response. Below is an example of what the query now looks like.

MTH_YR UNIT OPENED CLOSED LOSSES RECOVERIES LITIGATION
APR08 1 2 1 1000 500 600
APR08 2
APR08 3 5 2 5000 100
MAY08 1
MAY08 2 1 3 100 50 20
MAY08 3 10 8 2300 1200 1500

I would like to get thiws into a report that looks like the report example I gave above, where there is section for each one of the three units.
Thanks

 

[lameid]

btrini10,

Duane's point is we recommended table changes to you and you gave us a query that is based on queries which obscures the underlying table structure now.

What is the table structure?

 

[dhookom]

Based on your earlier posting of what you want displayed in your report, you could create a Main Report based on Unit only with a multiple column subreport based on your recent query output where each month creates a new column.

I would suggest you don't format you months using APR and MAY as these will never sort the way you want them to. It is much better to derive values like 200804, 200805,...

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

An alternative:

Look at my 24 Apr 09 14:07 post... Instead of joining your queries, union them.

Also I agree with Duane in general never format a date in a query. However in this case you need a Year column and a Month colum. The month column will be used as your Row heading in a crosstab. You sort it correctly via the columns property.

I would go this route as columns always seem to eventually do something undesirable for me.