This will be loaded with tons of syntax problems because I don't have access installed here AND I don't know it well - obviously.
We have a database at work with our bacterial test records. Water systems bring in samples, we run them and send them results. Some are required tests (compliance), others are not (special purpose - a new line or a boil order).
At end of each month we bill them for the tests, printing out an invoice for each one, along with a sheet listing their compliance and special purpose tests.
There are 5 different types of tests - 1-3 are compliance, 4 is special purpose, 5 is quantitray special purpose.
They are currently sorted by PWSID number and name.
I have created a report (along with it's query), which is sorted by system name and date. Open it, it asks for beginning and ending date, and shows all the compliance tests for each system - fine so far, but then I try to add the special purpose tests. These used to be all lumped under a fake system name. They were printed out and manually sorted. Now they will be entered just as any other test (we've added the 4 and 5 options to the drop down list).
The invoice blanks were also printed, filled in by hand, and the compliance and special purpose sheets put with them.
I've always said the damn program should do it all, so at last I've said screw it - I'm gonna make it work.
So what I need to do is count all the 1,2,3 tests, display that number HERE, then multiply iy by 15 and display that THERE as money. I have that, using either COUNT or SUM of TYPE field(with what looks like an IF statement using "1" or "2" or "3") as expressions in text boxes. Works fine. I could do it for 4 and 5, but how do I total them? I need to add them all up for the total bill.
Reading online, it seemed to me that I was supposed to do the math in the query, so I looked into that.
This is the wall I've been pounding on today.
I'm in design view of the query (DO NOT talk to me about SQL. Don't even try), and I put Count(Type) into the first line. It makes it 'Expr: Count[(Type)]' (something like that - as expected), I select the correct table in the second line. In a blank line I put "1" or "2" or "3" to have it just select the compliance tests.
But when I try to save the query, it complains about an 'Extra )'. There isn't an extra one, and if I remove either one it complains one is missing. (teeth grinding)
OK, so I try SUM, entering it like I learned online (which works fine in text box), and it complains about there being a comma in it.
It is something like this: Sum("Type" "1" or "2" or "3",1,0). The 1 and 0 might be reversed here, and maybe s missing. I enter it (copy/paste) just as it works fine in the text box expression.
Oh, I thought that problem might be that 'Type' is a text field, instead of numeric. It won't let me change it because it is in a relationship, and access seems to be a romantic program and won't let me find a way to severe the relationship. Plus I don't know if I even should. PLUS I tried it on a numeric field in same table and it made no difference anyway. I include this part just for the humor of it. NEED the humor working with this program.
Can someone set me on the correct path? I have PDFs of several Access guides, all of which seem to be written in some language unknown to me, though they CLAIM it is english.
And can someone tell me why, when I add ONE, TINY image to my report, the database jumps from 5.9 megs to 200 megs?
The link is to a copy of our database file (it's all public data). I don't remember if this one has the 'working' compliance stuff or not. The report is named rptMonthlyBillingTA as I recall.https://www.dropbox.com/s/25ysa4semk67xxq/BacT%20Customers.mdb
Thanks for any/all assistance!!