Forcing Query to return zero for missing records

[jonesrge]

Basically I have a database that is pulling in raw data from a transactional website that I download from the back end.

I have one user file with user details, and I have multiple files (each month, Jan, Feb, March, etc.) with deposit information.

Now I tied all these tables together based on a USER_ID file which exists in all files. I then extract name address etc. from the details file and also the deposit figure from each month file. So the out put looks like so....

USER_ID EMAIL JANDEPOSIT FEBDEPOSIT etc, etc,
x x x x
x x x x
x x x x

Now this all works fine, the problem is that each month not all users deposit money. Therefore the query only pulls records that have deposits in ALL the monthly deposit files. Now I want the query to pull the record regardless if there is some months where the user has no deposit, and force it to put a zero for that month.

Is this possible?

Apologies if i am not making this clear enough, please ask any more questions that might make it more crystal.

Thanks.

 

[Freshman]

Have you played around with the Nz() function?
You can use it to set a value as default in the event of a null

 

[MSAccessRookie]

Basically I have a database that is pulling in raw data from a transactional website that I download from the back end.

I have one user file with user details, and I have multiple files (each month, Jan, Feb, March, etc.) with deposit information.

Now I tied all these tables together based on a USER_ID file which exists in all files. I then extract name address etc. from the details file and also the deposit figure from each month file. So the out put looks like so....

USER_ID EMAIL JANDEPOSIT FEBDEPOSIT etc, etc,
x x x x
x x x x
x x x x

Now this all works fine, the problem is that each month not all users deposit money. Therefore the query only pulls records that have deposits in ALL the monthly deposit files. Now I want the query to pull the record regardless if there is some months where the user has no deposit, and force it to put a zero for that month.

Is this possible?

Apologies if i am not making this clear enough, please ask any more questions that might make it more crystal.

Thanks.

Sounds like a Query that has a LEFT JOIN of the Users Table and the Deposits Table based on User ID would be helpful here. Using such a Query would result in any User ID without a deposit having NULL Values in the Deposit Information. You could then use a Function like Nz() (suggested by Freshman above), to convert the NULL Deposit Value to 0, which should resolve your problem.