Showing null values (1 Viewer)

[Chrisopia]

I have a list of departments, 1-17, where each needs a SUM of their price for each end of day.

At first I was going to make 17 queries, and place each into a new sub-report, but there must be a way to list all 17, even if they haven't had a sale put through.

I've tried linking using "show all values in tblDept and only those that match in tblOrder" - but I cam across a very obvious issue.

The items are grouped by Z1 Number, a unique number for the end of day sales. If there is no department linked to a Z1 number, then it won't show it.
For example, if there were no sales in dept01, then there is no record under tblOrder to show a Z1 number for dept01 - so there is nothing to link to in the report.

I was then thinking of creating false data at the end of day so the Z1 number mentioned each department at least once, but that would get messy and not 'normal'

I'm thinking of a type of loop to generate the report so a 17 row report is generated, but I have no idea where to begin.

 

[maw230]

The items are grouped by Z1 Number, a unique number for the end of day sales. If there is no department linked to a Z1 number, then it won't show it.
For example, if there were no sales in dept01, then there is no record under tblOrder to show a Z1 number for dept01 - so there is nothing to link to in the report.

If all of the Z1 numbers are listed in tblDept and you do a left join to tblOrder on Z1 number then you should show all of the Z1's from tblDept, but there will only be sales listed for those that exist in tblOrder.

If you then need to get the name of the department I hope you can derive that from the z1 number somehow? You need a way to directly get sales by any level of your "department hierarchy".

 

[Chrisopia]

I think you misunderstood my db structure... let me see...
[tblOrder] ---<[tblDept]
tblOrder: OrderID*, Z1Number*, DepartmentID (FK), QTY, Price
tblDept: DeptNo*, DeptName

In reality, my table is more like:
[tblInvoice]---<[tblOrder]---<[tblDetails]
[tblOrder]---<[tblDept]

I simplified it for ease of questioning.

 

[maw230]

I think you misunderstood my db structure... let me see...
[tblOrder] ---<[tblDept]
tblOrder: OrderID*, Z1Number*, DepartmentID (FK), QTY, Price
tblDept: DeptNo*, DeptName

In reality, my table is more like:
[tblInvoice]---<[tblOrder]---<[tblDetails]
[tblOrder]---<[tblDept]

I simplified it for ease of questioning.

How are you trying to link tblOrder to tblDept? I don't see any matching fields unless DeptNo = DepartmentID.

 

[Chrisopia]

thats right.

 

[maw230]

thats right.

Did you figure this out yet? Can you not join tblOrders to tblDept to get what you need?