2ndPost Help with qry criteria PLEASE!!

[Nydia]

My original post was on 05/20/04 @ 7:11am

i am trying to pull information form this database (i
didn't create). i need to find out how many people were
served from Jan. 1, 2003 thru Dec. 31, 2003. This
database doesn't have an entry date all is has is a
termination date field.

what criteria would i put in the termination date field
that will bring up all clients that where open at any
given time between these dates.

in another dba that i have it has an entry and termiantion
date so my criteria for this would be

entrydate terminationdate
<12/31/03 >1/01/03 or is null


..

 

[ChrisJ]

Suggestion.
Take a row from your table at random.
Look at the data within it.
Can you tell if this record should be included in you
query result.
If you can't, then what you are trying to do is not
possible in a query.
If you can, convert the rule you applied to sql and see if
the same rule applies to other rows
If you have to make up a different rule for each row, then
what you are trying to do is not possible in a query.

 

[Guest]

i don't get it! All i want is to run a query that will
show how many people served in our program in 2003. We
don't have an entrydate field only a termination field, so
what criteria would i put in the termdate field in the
query.

 

[Michel Walsh]

Hi,


If you have just one field, then you HAVE TO define a client "BEEN
SERVED" as being so, by considering that "END OF TREATEMENT", so, a simple
criteria, and the following SQL statement would give a COUNT of how many
clients got their end of treatment, for 2003:


SELECT COUNT(*)
FROM myTable
WHERE termination >= #1-1-2003# AND termination <#1-1-2004#



That won't include those who started in end of 2003 and did not got their
end of treatment until 2004, since your data can't tell you that (having no
start of treatment). Maybe you got zillions of "treatments" and the mean
duration of each treatment is about one or two seconds, and that is why your
predecessor didn't think it was a good idea to get a start of treatment, I
don't know, and you are in a better position than me to know it, but with
the data you say you have, that is probably the best you can come... unless
no answer at all is considered a better answer.



As for as the NULL values, I don't know why there are there. Are they there
because they indicate that the service is NOT ended yet? if so, it seems
appropriate to NOT count them (like the formulation I posted do), because
they have to be counted for 2004 (or 2005 , or when they will "end" ), not
for 2003.





Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

i don't get it! All i want is to run a query that will
show how many people served in our program in 2003. We
don't have an entrydate field only a termination field, so
what criteria would i put in the termdate field in the
query.
...

The person who replied before is correct. If you have ONLY a
termination date in your table, IT IS ABSOLUTELY IMPOSSIBLE for any
computer program or human being to answer your question.

Example:

You have a record in the table with a termination date of 3/1/2004.

Was this person served during 2003?

If they started prior to January 1, 2003, the answer is yes. If they
started after January 1, 2003, the answer is no. If you don't know
WHEN they started, you have no way to tell!