Solved: Query Problem

[icthus123]

Hi, I'm trying to create a fairly complex query. I need to list Schools and the amount of money allocated to each one based on two tables. This itself is easy enough.

However, there is also a table which is made up of invoices and the amounts on them. I need a total spent on each school to appear next to each school (the total being the total for the invoices spent on a school). Now this isn't really working and I'm not really sure how to go about doing it, particularly as some schools don't have any invoices and if the total is Null that school gets taken out of the list, which isn't what I want. If there's nothing in the invoices table for that school I just want it to be displayed as ?0.00.

Any help would be appreciated.

[OBP]

Can you post or email a zipped copy of the database?
It can have a little dummy detail which would help a lot.
One observation, check the Joins between the Tables, it probably says where records are equal in both tables, change it to "All Records in th Schools Table and only those matching in the other tables".

[icthus123]

Can you post or email a zipped copy of the database?
It can have a little dummy detail which would help a lot.
One observation, check the Joins between the Tables, it probably says where records are equal in both tables, change it to "All Records in th Schools Table and only those matching in the other tables".

Okay here it is! The query in question is Query3. Any ideas?

[geekgirlau]

Try the query "qselInvoice" in the attached.

If you look at the joins, I think you'll find that you don't need the "Local Authority" field in either tblVASIS/School or tblInvoice. If each school can only belong to one Local Authority, you can join your tables by the School Number alone. I think you are unnecessarily complicating things by joining both.

[icthus123]

Try the query "qselInvoice" in the attached.

If you look at the joins, I think you'll find that you don't need the "Local Authority" field in either tblVASIS/School or tblInvoice. If each school can only belong to one Local Authority, you can join your tables by the School Number alone. I think you are unnecessarily complicating things by joining both.

Thanks geekgirlau. However, I need to get a total of all the invoices for a particular school. How would I do this? :dunno

[geekgirlau]

SELECT tblSchools.[School Name], tblSchools.[School Number], Sum(tblInvoice.Total) AS SumOfTotal, Sum(tblInvoice.VAT) AS SumOfVAT
FROM tblSchools INNER JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
WHERE (((tblInvoice.Date) Between #4/1/2007# And #3/31/2008#))
GROUP BY tblSchools.[School Name], tblSchools.[School Number];

[icthus123]

SELECT tblSchools.[School Name], tblSchools.[School Number], Sum(tblInvoice.Total) AS SumOfTotal, Sum(tblInvoice.VAT) AS SumOfVAT
FROM tblSchools INNER JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
WHERE (((tblInvoice.Date) Between #4/1/2007# And #3/31/2008#))
GROUP BY tblSchools.[School Name], tblSchools.[School Number];

I'm still not getting all the schools in this (i.e. even the ones with no entries in the invoice table) even though I've set the join properties to "include all records from tblSchools, etc." Why is this?

[geekgirlau]

You don't need to change the join properties if you are totalling the invoices - you can't get an invoice total unless there is an invoice record. The query above is only designed to capture schools with an invoice between specific dates - if you don't need this restriction, remove the WHERE clause.

[icthus123]

You don't need to change the join properties if you are totalling the invoices - you can't get an invoice total unless there is an invoice record.
Okay... I need to produce a report which has an invoice total for each school on it though, even if the total's zero. Any ideas how I could do this then?

[geekgirlau]

You might be coming up with a problem with null values. Change the join type as you've mentioned (i.e. all records from tblSchools) and try the following:

SELECT tblSchools.[School Name], tblSchools.[School Number], Sum(nz([Total],0)) AS TotalInvoice, Sum(nz([VAT],0)) AS TotalVAT
FROM tblSchools LEFT JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
GROUP BY tblSchools.[School Name], tblSchools.[School Number];

[icthus123]

You might be coming up with a problem with null values. Change the join type as you've mentioned (i.e. all records from tblSchools) and try the following:

SELECT tblSchools.[School Name], tblSchools.[School Number], Sum(nz([Total],0)) AS TotalInvoice, Sum(nz([VAT],0)) AS TotalVAT
FROM tblSchools LEFT JOIN tblInvoice ON tblSchools.[School Number] = tblInvoice.School
GROUP BY tblSchools.[School Name], tblSchools.[School Number];

Right that's great! But when I try to incorporate all the other fields I need in my query it says the SQL statement contains ambiguous outer joins. How do I get round this?

[icthus123]

Okay I think I've almost sorted it now. My only problem is that I when I try to place criteria on the sum of Invoice, etc. I have the same problem of not returning the values which are null (even though I'm using that nz() function in the sum formula). How can I get round this?

[icthus123]

I think I'm close to getting this problem sorted now. I've uploaded another version of the database. The Query I have a problem with is Query 3, why aren't the nz() functions working? They worked okay until I added in the criteria that the date had to be between 01/04/2007 and 31/03/2008 but now that's there it's missing out all the null values again. How can I solve this? Any ideas?

[alimcpill]

Sorry only had a brief skim over this, I think your issue could be because even though you are 'Nz'-ing the numeric amounts from tblInvoice, you are not 'Nz'-ing the Date, so if the date is null (i.e. there is nothing matching in tblInvoice), the criteria will fail. Easiest way around this is by changing the criteria from
Between #01/04/2007# And #31/03/2008# to
Is Null Or (Between #01/04/2007# And #31/03/2008#)

(edit: this assumes you have flicked the query back to design view from SQL view)

[icthus123]

Sorry only had a brief skim over this, I think your issue could be because even though you are 'Nz'-ing the numeric amounts from tblInvoice, you are not 'Nz'-ing the Date, so if the date is null (i.e. there is nothing matching in tblInvoice), the criteria will fail. Easiest way around this is by changing the criteria from
Between #01/04/2007# And #31/03/2008# to
Is Null Or (Between #01/04/2007# And #31/03/2008#)
(edit: this assumes you have flicked the query back to design view from SQL view)

Thanks a lot!! That works fine now!! One more thing! Is there a way I can get it to display as ?0.00 instead of just 0?

[icthus123]

I solved that last problem, I just used, Format(x,"?#,##0.00"). Thanks to everyone for their help!