Forms and queries not returning required data

[jodilees]

Query filter problem
Hi everyone

I have a database that I have created to track our work in progress. The primary table has the records of all of the jobs. I have created a form to control this table and I have a subform that tracks invoicing (more than one invoice per job).

I am trying to create a query that will allow me to have a summary of the information in the main table and show a sum of invoicing that is outstanding.

I have created the query and it is only working if there is any invoicing against the job. For examble Job 1389 Contract value $100,000 - invoiced so far $10,000 will show a balance of $90,000. This is currently working. I want it to show 0 in the invoicing if there is nothing against the job number but still show the job number and other fields that I select. Eg example above would be 0 in invoicing column and balance $100,000. At present as soon as I put any fields with anything to do with invoicing it will not show the entries in the main table that don't have any invoicing.

If I run a query on my sub form and show the sum for the fields it is not working either.

Sorry if this does not make sense, any feedback would be appreciated.

I am unsure of how to make the appropriate changes to make this work.

Kind Regards

Jodi

 

[dcx693]

I think this has to do with the way you are joining your main job table and your invoicing table to produce the query.

Typically, when you join tables in Access you get an "inner" join - a join that only returns records with equal values on both sides of the join. That's why, when you don't have an invoice record the job record doesn't show up. Likewise, when you have an invoice the job is returned.

What you need to do is change the join type by going to query design view and double-clicking the join line. You'll see three potential join choices and you've probably got the first one. Change it so that it selects all records from the job table and only those from the invoice table where there are matching values. This type of join is called a LEFT or RIGHT join.

 

[jodilees]

That works, but it is not calculating

thankyou for your help, that was very easy and probably very silly of me not to realise that was the problem.

However now it is not returning a value for a total.

I have the following fields included in my query, contract Value, Sum of invoicing, sum of variations and total.

I have put into the total column a statement that takes the total contract value, adds the sum of variation and then minus the sum of invoicing. It is working in the record that has both variation and invoicing values but not in the one that has no variation value. The total column is just blank. I wonder if I need to put something into the the query that tells it that if there is no value for either the sum of variation or invoicing to return a 0 value. And also how would I go about this as both the invoicing and variation may have + and - values put in them.

If you can help me any further I would really appreciate it.

Kind Regards

Jodi

I think this has to do with the way you are joining your main job table and your invoicing table to produce the query.

Typically, when you join tables in Access you get an "inner" join - a join that only returns records with equal values on both sides of the join. That's why, when you don't have an invoice record the job record doesn't show up. Likewise, when you have an invoice the job is returned.

What you need to do is change the join type by going to query design view and double-clicking the join line. You'll see three potential join choices and you've probably got the first one. Change it so that it selects all records from the job table and only those from the invoice table where there are matching values. This type of join is called a LEFT or RIGHT join.

 

[alcapps]

Don't know if this helps but I think your problem now is you are not giving a value to a null result. You should use the Nz() function. Example Nz(field name,0) which will return a 0 for the null value of sum of invoices. As mentioned before the left join will return a row but no value = null. now you must change the no value to a value of 0 so that the query can do it work.
your sum of invoicing should have a nz function and your final calculation field should have a nz function around sum of invoices too.
try that.