table entries vs. DSum

[Chrisopia]

So I have 17 departments, and each need to be summed up grouped by a number (Z1 number) - a unqiue number which changes with each business day.
I eventually need a report to display all 17 departments, whether a sale has gone through or not.

I am trying this from a different angle, but I need to know how to increase the optimisation of eventually producing a report.

My first idea was to create 17 queries, and use VBA to generate the report based on those, but then I had another idea -using DSum, but I've read it's ineffective and slows down processing.

So I want your opinion - do I do a DSum for all my fields, or an overall query which will save the data into a table for easy access?

For example - I could use an update query to do the math and update the table and base my report on that, or I fill the report using VBA and DSum

Any ideas?

 

[plog]

You're not making much sense---17 departments identified by a number that changes daily is illogical. How about you show us with data. Provide 2 sets: show us sample data from your tables and then ending data that should be produced from that sample starting data. Be sure to include table and field names.

 

[Chrisopia]

tblDept:
DeptNo | Dept
01 | Dept01
02 | Dept02
03 | Dept03 ...

tblOrder:
Z1Number | Price | Dept
1000 | £10 | 01
1000 | £10 | 01
1000 | £12 | 02
1001 | £100 | 03

Report: (Z1: 1000)
Dept01 £20
Dept02 £12
Dept03 £0
Total: £32

Report: (Z1: 1001)
Dept01 £0
Dept02 £0
Dept03 £100
Total: £100

tblZ1:
Z1no D01 D02 D03 Total
1000 £20 £12 £0 £32
1001 £ 0 £ 0 £100 £100

 

[namliam]

what you want to do is a simple crosstab query you can do straight from you tblDept and tblOrder.

 

[Chrisopia]

I did make the report using the query in a previous version of my db, but the report only shows the departments with sales on.

For audit purposes, the report needs to show £0 for the other departments.

 

[Chrisopia]

Right - I found a way around, but I want your opinion on the efficiency. I basically have queries, referring to other queries...

The first query lists all my Z1 numbers with InvoiceIDs:

SELECT InvoiceID, Z1Number
FROM tblInvoiceQuote
WHERE (Z1Number)=Z1());

Z1() is a function to retrieve the relevant Z1 number.
This is qryZ1Dept

SELECT qryZ1Dept.Z1Number, tblOrder.DepartmentID, Sum(tblOrder.Price) AS Total, Count(tblOrder.DepartmentID) AS Q
FROM qryZ1Dept INNER JOIN tblOrder ON qryZ1Dept.InvoiceID = tblOrder.InvoiceID
GROUP BY qryZ1Dept.Z1Number, tblOrder.DepartmentID;

This gives me a list of the totals- but only to the relevant sales.
So I plug this (qryEOD) into another query to trick it into filling in the info:

SELECT Nz([Z1Number],Z1()) AS Z1, tblDept.DeptNo, CCur(Nz([Total],0)) AS T, Nz([Q],0) AS Qty
FROM qryEOD RIGHT JOIN tblDept ON qryEOD.DepartmentID = tblDept.DeptNo;

Here, I use the RIGHT JOIN to display all info from tblDept, and only the relevant info from qryEOD.

I then use Nz to fill in the blanks, either 0 or the Z1 Number.

I can use this to then populate my report

 

[namliam]

as long as it works its good isnt it?