Count Restricted by Date

[cgjames]

Hello all,



I am a therapist, not a programmer, but my agency cannot afford a programmer to fix our database disaster, so I have been commissioned. This thing is truly nuclear. There are around 80 tables with a good 50-60 fields per table on average. Roughly 200 queries and 50 reports. Many of these won't open or are broken or lead nowhere. There are no modules defined, but most forms have some VB in them. The entire database does not have a single relationship defined. It's freakin me out just looking at it. The guy that built it has been charging our agency hundreds of dollars an hour and taking 8-10 hours at a time to report on what was in the database. I came into the agency and informed them that Access can automate reports, and that they were being scammed. It sucks, because we provide free therapy to sexual abuse victims that have no money, and he was stealing from us. I'm hoping to leave all of the tables in tact and just design reports on top of the nuclear waste pile that is this database. Everything seems to function and interrelate through queries, so it may be possible! If anyone knows any volunteer database programmers, let me know!

In the meantime, here's my question. I want to count the number of times a boolean item was checked "yes" between two dates. These dates will be entered by the user whenever the report is opened. Each record in this table has date, client id, and around 30 boolean items that I need to report on. What is the most efficient way to do that?

[pbaldy]

Off the top of my head:

SELECT Abs(Sum(Field1)) AS Field1Count
FROM TableName
WHERE DateField Between Forms!FormName.txtFromDate AND Forms!FormName.txtToDate

That would have you using a form for the user to enter the dates. You can add other fields.

[cgjames]

Awesome, got it. Thanks!

[TheShabz]

Still need a db guy? I only charge a fraction of "hundreds of dollars an hour"