DSUM slowing down my report

[Geordie2008]

Hi all,

I have a normal query which cross tabs my data and then populates a report. The user wanted a "Total" to sum up the row table so I added a DSUM based on the underyling table which all of the queries are run off.

the DSUM works and gets the correct total.

The problem is that the report used to take 22 seconds to run and now (with the DSUM) it takes 90 seconds to run....

Is there any way to spped this up? I tried basing the DSUM on an aggregated query where the underlying query was a "grouped" version of the underyling table but this had no performance benefit.

Has anyone come accross this issue before where a DSUM slows a report down massively?

Thanks,
Mand.

 

[KenHigg]

I may be way off on this but, can you to a second query built off the cross tab query and put the dsum there? Or maybe sum it in the report?

 

[Geordie2008]

I would love to be able to do either of these things.....

I can sum columns easily in teh report, but not rows... Is it possible to do a row sum within a query?

I thought a dsum in the report was my only option, but I'd be a very happy chap to learn of other solutions(!!)

So you have any examples of the magic you talk about?!?!

I want to have a report showing the following:

Mandy

Thanks,
M

 

[Geordie2008]

whoops, posted before I'd finished typing:

Sales £1 £2 £2 £4 and then total column = £9

Thanks,

 

[KenHigg]

I'm thinking in the report for the sum text box you simply do something like:

=[MyCol1] + [MyCol2] + [MyCol3]

???

 

[KenHigg]

See if this helps (It's form but the logic should work in a report):

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=23047&d=1218539292

 

[Geordie2008]

Mmmmm.... my issue with doing that is that I have a manager with employees...

Looks like this:

____________________Sales1____Sales2_____Sales3____Sales4____Total_Sales
Manager1____Empl1_____1________4__________2________0___________7
____________Empl2_____1________1__________1________1___________4
____________Empl3_____2________4__________2________2___________10
Manage1Total__________4________9__________5________3___________21

When i use the solution you discuss it gives me the "Total_Sales" figure as 7 for all of the Emp's. Whereas the DSUM is giving me the correct numbers, (as Im Dsuming where [Manager1] = [manager 1] & [Emp1] = [emp1 ] etc.....

Hope that makes sense!
M

 

[Geordie2008]

Just looked at you example.... Im not sure it would work with my structure detailed above....?

I can see how I would get it to sum (in a report) with the conditions I need to specify...

Thanks for you help KenHigg, its always appreciated!
Mandy

 

[Geordie2008]

sorry - typo - that should read ** I cant see how I would get it to work**

 

[KenHigg]

So in Sales1 column for Manager1Total where you have 4 as the value can't you put the sum() function there? ( Not dsum() )

 

[Geordie2008]

Oh, the columns are absolutely fine, as you say, v easy "=sum" at the bottom and they work great. Its the row totals that are the issue, and its these that I currently am using dsums for....

So the total for emp1 = 7
emp2 = 4

etc...

 

[Geordie2008]

Im just nipping for lunch as its a friends birthday, I'll be back.

Cheers,
M

 

[stopher]

So in Sales1 column for Manager1Total where you have 4 as the value can't you put the sum() function there? ( Not dsum() )

I agree with Ken here. You shouldn't use DSUM at all.

Your SQL should look something like:

TRANSFORM Sum(Sales) AS SumOfSales
SELECT Year, [COLOR="Red"]Sum(Sales) AS TotalSales[/COLOR]
FROM tblSales
GROUP BY Manager, Employee
PIVOT SalesPeriod

The bit in red will sum your columns.

hth
Chris

 

[Geordie2008]

Mmmmm v interesting...... where do I put this code? Just in the text box on the report itself?

 

[stopher]

Mmmmm v interesting...... where do I put this code? Just in the text box on the report itself?

You mentioned at the beginning that you have a cross-tab query. As well as viewing the query using the query editor, you can also view it as SQL. Open the query in the editor then select View=>SQL View. You will then see the SQL.

You can do it in design view also if you prefer. Just add another column to your cross tab query with the following attributes:
Field: TotalSales: yourValueFieldName
Table: Your Table name
Total: SUM
Crosstab: Row Heading

Once you have the field in your query then you can add it to your report.

hth
Chris

 

[Pat Hartman]

If you build the crosstab with the wizard, the wizard automatically adds the column that stopher showed you how to add.

 

[Geordie2008]

its taken me a wee while to test this.... and make it work for my queries..... but this has made my Friday afternoon much more enjoyable.....

Absolutely ingenious!!

Thanks so much for advice with this!
Mandy

 

[Geordie2008]

My report is now down to 5 seconds to run! (was c.1min20 seconds)

I am SOOOO glad I emailed the forum.

Thanks again,
Have a great w.e!
Mandy

 

[stopher]

Well done! Always good to here when someone has been able to make that step forward. I know that buzz you get when a solution works. Enjoy the weekend.
Chris