Query help for a census database

[thrusty]

I have two tables (Individuals and Census) - with a one to many
relationship...One individual may have many census codes.

Census codes define when an individual was (1)admitted, (2)off
grounds, (3)ongrounds or (4)discharged and has a date effective for
each code. For simplicity, I assigned each text-code a number.

What I would like to do is run a query that will pull out who was
either admitted or ongrounds given a date.

Here's what I'd like it to do:
Enter a date.
If the date entered is also when the code was a 1 or a 3, list that
person as being ongrounds.
For all others, go to the code that was entered prior to the given
date.
If that code is a 1 or a 3, then list that person as being
ongrounds for the given date.
Else, exclude all others, as the are not ongrounds on the given
date.

Any thoughts?
Thanks!

 

[thrusty]

Try this:

PARAMETERS [Enter date:] DATETIME;
SELECT FirstName, LastName, CensusCode
FROM Individuals INNER JOIN Census AS C1
ON C1.IndividualID = Individuals.IndividualID
WHERE CensusCode IN (1,3)
AND DateEffective =
     (SELECT MAX(DateEffective)
       FROM Census AS C2
       WHERE C2.IndividualID = C1.IndividualID
       AND DateEffective <= [Enter date:]);

Ken Sheridan
Stafford, England

I have two tables (Individuals and Census) - with a one to many
relationship...One individual may have many census codes.

Census codes define when an individual was (1)admitted, (2)off
grounds, (3)ongrounds or (4)discharged and has a date effective for
each code.  For simplicity, I assigned each text-code a number.

What I would like to do is run a query that will pull out who was
either admitted or ongrounds given a date.

Here's what I'd like it to do:
Enter a date.
If the date entered is also when the code was a 1 or a 3, list that
person as being ongrounds.
For all others, go to the code that was entered prior to the given
date.
    If that code is a 1 or a 3, then list that person as being
ongrounds for the given date.
    Else, exclude all others, as the are not ongrounds on the given
date.

Any thoughts?
Thanks!

No Joy.
Gives me the error - too complex to evaluate. Here's what I tried (6
and 7 are actually my ongrounds/admitted codes and all the other table
references match).

PARAMETERS [Enter date:] DATETIME;
SELECT CensusCode
FROM Individuals INNER JOIN Census AS C1
ON C1.IndividualID = Individuals.IndividualID
WHERE CensusCode IN (6,7)
AND DateEffective =
(SELECT MAX(DateEffective)
FROM Census AS C2
WHERE C2.IndividualID = C1.IndividualID
AND DateEffective <= [Enter date:]);

 

[thrusty]

Try this:

PARAMETERS [Enter date:] DATETIME;
SELECT FirstName, LastName, CensusCode
FROM Individuals INNER JOIN Census AS C1
ON C1.IndividualID = Individuals.IndividualID
WHERE CensusCode IN (1,3)
AND DateEffective =
     (SELECT MAX(DateEffective)
       FROM Census AS C2
       WHERE C2.IndividualID = C1.IndividualID
       AND DateEffective <= [Enter date:]);

Ken Sheridan
Stafford, England

- Show quoted text -

No Joy.
Gives me the error - too complex to evaluate.  Here's what I tried (6
and 7 are actually my ongrounds/admitted codes and all the other table
references match).

PARAMETERS [Enter date:] DATETIME;
SELECT CensusCode
FROM Individuals INNER JOIN Census AS C1
ON C1.IndividualID = Individuals.IndividualID
WHERE CensusCode IN (6,7)
AND DateEffective =
     (SELECT MAX(DateEffective)
       FROM Census AS C2
       WHERE C2.IndividualID = C1.IndividualID
       AND DateEffective <= [Enter date:]);- Hide quoted text -

- Show quoted text -

Thanks for your help.
Got it to work with this:

SELECT C1.CensusCode, Individuals.Last, Individuals.First
FROM Individuals INNER JOIN Census AS C1 ON Individuals.IndividualID =
C1.IndividualID
WHERE (((C1.CensusCode) In (6,7)) AND ((C1.DateEffective)=(SELECT MAX
(DateEffective)
FROM Census AS C2
WHERE C2.IndividualID = C1.IndividualID
AND DateEffective <= [Enter Date:])));

 

[thrusty]

On Nov 12, 6:47 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:

Try this:
PARAMETERS [Enter date:] DATETIME;
SELECT FirstName, LastName, CensusCode
FROM Individuals INNER JOIN Census AS C1
ON C1.IndividualID = Individuals.IndividualID
WHERE CensusCode IN (1,3)
AND DateEffective =
     (SELECT MAX(DateEffective)
       FROM Census AS C2
       WHERE C2.IndividualID = C1.IndividualID
       AND DateEffective <= [Enter date:]);
Ken Sheridan
Stafford, England
thrusty wrote:
I have two tables (Individuals and Census) - with a one to many
relationship...One individual may have many census codes.
Census codes define when an individual was (1)admitted, (2)off
grounds, (3)ongrounds or (4)discharged and has a date effective for
each code.  For simplicity, I assigned each text-code a number.
What I would like to do is run a query that will pull out who was
either admitted or ongrounds given a date.
Here's what I'd like it to do:
Enter a date.
If the date entered is also when the code was a 1 or a 3, list that
person as being ongrounds.
For all others, go to the code that was entered prior to the given
date.
    If that code is a 1 or a 3, then list that person as being
ongrounds for the given date.
    Else, exclude all others, as the are not ongrounds on the given
date.
Any thoughts?
Thanks!

No Joy.
Gives me the error - too complex to evaluate.  Here's what I tried (6
and 7 are actually my ongrounds/admitted codes and all the other table
references match).

PARAMETERS [Enter date:] DATETIME;
SELECT CensusCode
FROM Individuals INNER JOIN Census AS C1
ON C1.IndividualID = Individuals.IndividualID
WHERE CensusCode IN (6,7)
AND DateEffective =
     (SELECT MAX(DateEffective)
       FROM Census AS C2
       WHERE C2.IndividualID = C1.IndividualID
       AND DateEffective <= [Enter date:]);- Hide quoted text-

- Show quoted text -

Thanks for your help.
Got it to work with this:

SELECT C1.CensusCode, Individuals.Last, Individuals.First
FROM Individuals INNER JOIN Census AS C1 ON Individuals.IndividualID =
C1.IndividualID
WHERE (((C1.CensusCode) In (6,7)) AND ((C1.DateEffective)=(SELECT MAX
(DateEffective)
       FROM Census AS C2
       WHERE C2.IndividualID = C1.IndividualID
       AND DateEffective <= [Enter Date:])));- Hide quoted text-

- Show quoted text -

Jumped the gun...
It works in every case except one: If you enter a date that is before
they were admitted it lists them.
What would I adjust to make it disregard anyone who has a Code 7 and
their DateEffective is before the date you are running the census for?

 

[thrusty]

Your two sentences seem to me to be contradictory.

By:  "If you enter a date that is before they were admitted"  I take it to
mean the date entered as the [Enter Date:] parameter is earlier than the
earliest date for an individual in the Census table.

By:  "has a Code 7 and their DateEffective is before the date you are running
the census for"  I take it to mean that an individual has a value in the
DatEffective column which is earlier than the date entered as the [Enter Date:
] parameter and a value of 7 in the CensusCode column.

Unless I'm misinterpreting them these two statements don't appear to be
compatible.  Perhaps it would help if you posted some sample rows from Census
for an individual and a parameter date where the individual is being
incorrectly returned.

Ken Sheridan
Stafford, England

On Nov 12, 6:47 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:

[quoted text clipped - 75 lines]

- Show quoted text -

Jumped the gun...
It works in every case except one: If you enter a date that is before
they were admitted it lists them.
What would I adjust to make it disregard anyone who has a Code 7 and
their DateEffective is before the date you are running the census for?

Ken, I replyed directly to you....I appreciate your help.
The query works great for when I want to find out who is ongrounds for
present day.
This is because you check for the max (or most recent) DateEffective
code. If it's a 6 or 7 they are here.
Problem is when you try to see who was here during a past date.

One client had only one code which was admitted on 1/1/2000...Then I
ran the query for 12/31/1999 -- it returned that client because it
found his most recent code and it was a 7.
I added a second code to test, which was OffGrounds. Then the query
did not return this person.

Problem: if you want to know who was here during a past date, it won't
necessarily be their most recent code that needs checked.

I need to enter a date.
If that date is when the person's code was a 6 or 7, return them.
To find everyone else, check the DateEffective of the code that was
entered prior to the date you are running the query for.
If that code is a 6 or 7, return them.

 

[thrusty]

I'm afraid I can't reproduce the behaviour you are experiencing.  I don't get
a row returned for an individual in the circumstances as you describe them.
Moreover it doesn't make any sense for an individual to be returned whose
only Date Effective value is later than the date entered as the parameter..
The subquery includes a criterion 'DateEffective <= [Enter date:]'  so if an
individual's only Date Effective value is later than the parameter, as in
your example where  1 Jan 2000 is later than 31 December 1999 the subquery
would not return a row, i.e. it would return NULL.  The outer query includes
a criterion of 'DateEffective = <the value returned by the subquery>', so if
the subquery is returning NULL this expression will evaluate to NULL and no
row will be returned for the individual in question by the outer query.

I'm really at a loss to explain how an individual can be returned in the
circumstances you describe.  I need to break it before I can fix it.  If you
can post the IndividualID, CensusCode and DateEffective values for all rows
from the census table for the individual who is being incorrectly returned,
along with the date being entered as the parameter which causes the
individual to be incorrectly returned, then I can feed these into the tables
I've set up and test it.

Ken Sheridan
Stafford, England

[quoted text clipped - 29 lines]

Ken, I replyed directly to you....I appreciate your help.
The query works great for when I want to find out who is ongrounds for
present day.
This is because you check for the max (or most recent) DateEffective
code.  If it's a 6 or 7 they are here.
Problem is when you try to see who was here during a past date.

One client had only one code which was admitted on 1/1/2000...Then I
ran the query for 12/31/1999 -- it returned that client because it
found his most recent code and it was a 7.
I added a second code to test, which was OffGrounds.  Then the query
did not return this person.

Problem: if you want to know who was here during a past date, it won't
necessarily be their most recent code that needs checked.

I need to enter a date.
If that date is when the person's code was a 6 or 7, return them.
To find everyone else, check the DateEffective of the code that was
entered prior to the date you are running the query for.
If that code is a 6 or 7, return them.

I added a new individual who has only one code - Admitted on
04/01/2000
Ran the query for 03/31/2000 - Individual is not returned.
Ran the query for 05/01/1999 - Individual is returned.
Ran the query for 04/01/1999 - Individual is not returned
Added a second code (04/02/2000 offgrounds) and ran for 05/01/1999 -
Individual is not returned.
Added a third code (04/05/2000 ongrounds) and ran for 05/01/1999 -
Individual is returned.

It may be comparing only the month digits and not the entire month,
day, and year because I did not declare it as a DATETIME - not
sure...?
Of course, I only see this behavior when the last code entered was a 6
or 7 (Ongrounds or Admitted).

The query errored with "too complex" when I tried to specifiy the
variable type with: PARAMETERS [Enter Date:] DATETIME;
So I removed that line and it worked...not sure if that is the
problem. Below is the code i'm using.
I can send you a copy of the database if you'd like - it's a new
project with test data right now.

SELECT C1.CensusCode, Individuals.Last, Individuals.First
FROM Individuals INNER JOIN Census AS C1 ON Individuals.IndividualID =
C1.IndividualID
WHERE (((C1.CensusCode) In (6,7)) AND ((C1.DateEffective)=(SELECT MAX
(DateEffective)
FROM Census AS C2
WHERE C2.IndividualID = C1.IndividualID
AND DateEffective <= [Enter Date:])));

 

[thrusty]

The fact that you cannot declare the parameter as  DATETIME is the clue, I
think.  It rather sounds like the DateEffective column in the table is a text
data type, not a date/time data type.  As a string comparison "04/31/2000" is
greater  than "03/31/2000", but less than "05/01/1999" whereas, as dates, the
opposite is true in the case of the latter.  If a text column is used for
dates then the ISO standard for date notation of YYYY-MM-DD should be used as
this sorts correctly, but as Access provides a date/time data type it makes
far more sense to use that.

Another cause of this sort of problem can be if a date/time data type is used
for the column in the table, but it is returned in a query using the Format
function, e.g.

SELECT Format(MyDate,"mm/dd/yyyy") AS MyDateFormatted

The Format function returns a string expression, so again any comparisonson
the values returned by the query would be on the text values not dates.
People sometimes do this to remove any times of day from a date/time value,
but the correct way to do that would be DateValue(MyDate), which returns a
date/time data value with a zero time of day element.

Do not confuse the use of the Format function in this way with setting the
Format property of a date/time data type.  That simply determines how the
date/time value is displayed, not its data type.

If you have used a text data type for the column it should convert without
any trouble to a date/time data type provided that all the values can be
legitimately interpreted as dates.  Then be sure to declare the parameter in
the query as DATETIME; it avoids it being mistakenly interpreted as an
arithmetical expression.

Ken Sheridan
Stafford, England

I'm afraid I can't reproduce the behaviour you are experiencing.  I don't get
a row returned for an individual in the circumstances as you describe them.

[quoted text clipped - 53 lines]

- Show quoted text -

I added a new individual who has only one code - Admitted on
04/01/2000
Ran the query for 03/31/2000 - Individual is not returned.
Ran the query for 05/01/1999 - Individual is returned.
Ran the query for 04/01/1999 - Individual is not returned
Added a second code (04/02/2000 offgrounds) and ran for 05/01/1999 -
Individual is not returned.
Added a third code (04/05/2000 ongrounds) and ran for 05/01/1999 -
Individual is returned.

It may be comparing only the month digits and not the entire month,
day, and year because I did not declare it as a DATETIME - not
sure...?
Of course, I only see this behavior when the last code entered was a 6
or 7 (Ongrounds or Admitted).

The query errored with "too complex" when I tried to specifiy the
variable type with: PARAMETERS [Enter Date:] DATETIME;
So I removed that line and it worked...not sure if that is the
problem.  Below is the code i'm using.
I can send you a copy of the database if you'd like - it's a new
project with test data right now.

SELECT C1.CensusCode, Individuals.Last, Individuals.First
FROM Individuals INNER JOIN Census AS C1 ON Individuals.IndividualID =
C1.IndividualID
WHERE (((C1.CensusCode) In (6,7)) AND ((C1.DateEffective)=(SELECT MAX
(DateEffective)
      FROM Census AS C2
      WHERE C2.IndividualID = C1.IndividualID
      AND DateEffective <= [Enter Date:])));

Issue was the DateEffective field in the table was text...I set the
input mask up and forgot to specify date/time...instead it was text.
Code works perfectly...thank you so much. Can't believe I made such a
newbie mistake.

My next endevor is average daily census for a date range...care to
take a shot?

I would need to know the census (total number of people on grounds)
for each day in the date range; then add them together and then divide
that number by the total number of days in the date range.
Average daily census is typically run for a month or year....
I really have no clue how to implement a loop with multiple variables
in SQL or VB. It would basically run the query you already wrote for
each day in the date range, store the count and add them all, then
divide by total number of days in date range.


Example:
Average daily census for 11/01/2009 to 11/03/2009
On 11/01/2009 census was 50
On 11/02/2009 census was 51
On 11/03/2009 census was 51
Total: 50+51+51 = 152
Number of days in date range: 3
Return Average daily census: 152 / 3 = 50.66

 

[thrusty]

One thought.  The queries will only take account of days where at leastone
person was on grounds.  Any days with zero people on grounds will simply be
ignored, both by the qryCensusNumberOnGroundsPerDay query, and consequently
by the final averaging query.  Is that OK?

Ken Sheridan
Stafford, England

That is fine - there will always be at least 1 person on grounds - or
else we'd be closed.
As long as the sum of the all the daily ongrounds totals is divided by
the total number of days you're running the census for, it will work
out great.
I will try the code out monday. Thanks for all your help.

 

[thrusty]

It will divide the sum by the number of days within the range with at least
one person on grounds.  If the total range is 30 days, say and there happened
to be 2 days within it where the query computes on the basis of the codes
that nobody is on grounds, then it would divide by 28.  It would be quite
easy to amend the queries to divide by the 30 days regardless if you'd prefer
that.

Ken Sheridan
Stafford, England

One thought.  The queries will only take account of days where at least one
person was on grounds.  Any days with zero people on grounds will simply be

[quoted text clipped - 6 lines]

That is fine - there will always be at least 1 person on grounds - or
else we'd be closed.
As long as the sum of the all the daily ongrounds totals is divided by
the total number of days you're running the census for, it will work
out great.
I will try the code out monday.  Thanks for all your help.

I see what you mean. It will always need to be divided by the number
of days in the range regardless of whether there was someone was here
or not.
I'd like to be able to run a report that displays each day's census in
the date range - at the bottom of the report I'd just use a text box
to display the avg census.
Thanks for your help on this!

 

[thrusty]

It will divide the sum by the number of days within the range with at least
one person on grounds.  If the total range is 30 days, say and there happened
to be 2 days within it where the query computes on the basis of the codes
that nobody is on grounds, then it would divide by 28.  It would be quite
easy to amend the queries to divide by the 30 days regardless if you'd prefer
that.

Ken Sheridan
Stafford, England

On Nov 19, 6:56 pm, "KenSheridan via AccessMonster.com" <u51882@uwe>
wrote:
One thought.  The queries will only take account of days where at least one
person was on grounds.  Any days with zero people on grounds will simply be
[quoted text clipped - 6 lines]
--
Message posted viahttp://www.accessmonster.com
That is fine - there will always be at least 1 person on grounds - or
else we'd be closed.
As long as the sum of the all the daily ongrounds totals is divided by
the total number of days you're running the census for, it will work
out great.
I will try the code out monday.  Thanks for all your help.

I see what you mean.  It will always need to be divided by the number
of days in the range regardless of whether there was someone was here
or not.
I'd like to be able to run a report that displays each day's census in
the date range - at the bottom of the report I'd just use a text box
to display the avg census.
Thanks for your help on this!- Hide quoted text -

- Show quoted text -

Nevermind, I thought about that and the way you have it currently is
the best method. It would give a more accurate result since we are
starting this database from scratch and if someone would enter in a
huge date range it would not effect the outcome.
It works great - thanks.