Returning 0 values

[dimbodoyle]

Hi anyone!
I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.

I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.

Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?

Thanks in advance

 

[amator]

try this

Hi
If I understand right what you mean try this:
Iif(IsNull(Query.[column]),0,Query.[column]) AS [column]

This should change value "nothing" to 0.

 

[neileg]

Hi
If I understand right what you mean try this:
Iif(IsNull(Query.[column]),0,Query.[column]) AS [column]

This should change value "nothing" to 0.

It's easier to use the Nz() function, but I dont think this answers the question.

If you want a return for all depots you need to get a list of all depots and their ID numbers. I presume you have this in a table in your database or you can get it with a query. Bring this table or query into your table. Join this to your existing table(s) in the query with a LEFT join. In Access this is a type 2 join, it will return all the records from your depot list and any from the other table(s) that match. This will give you a line for every depot even if there are no transactions for it.