Creating a query from mutiple tables and including formulas

[mac24601]

I have created 3 tables in Access that I need to summarize together based on a department field. The tables are related to payroll: Productive hours/pay, Overtime hours/pay and Non-Productive hours/pay. All of these tables have the same fields and I have successfully combined them into one query. The complete list of departments is only in the Productive table. I have successfully created the relationships in the query to show all of the departments in the Productive table by editing the join properties.

Here's the problem: Instead of just showing Productive hours, I need to take the hours field from that table and subtract the hours field from the Overtime table. I also need to sum the total pay from all 3 tables. If the department is present in all 3 tables, I have no problem. The formulas work correctly. If the field is not present in the Overtime or Non-Productive fields, the totals do not calculate and a blank cell in returned.

I'm sure there is a simple answer for this, but my Access experience is limited and, at this point, I can only put so much in a search engine before it gets overwhelming. Thank you for your help in advance!

 

[alansidman]

Access does not recognize Null fields in a record and therefore you need to tell it what to do if it encounters a null field. You need to use the NZ function. Here is a link to how to employ it. You will in all liklihood need to assign a zero.

MS Access: Nz Function

 

[mac24601]

Thank you! It worked perfectly.