Need a query within a range of dates

[Guest]

Ok, I'm not sure if this is really complicated or simple...Here it is.
We have a database of attendance. The Form is really simple Name, then we
have several fields with the date, Jan 01. Jan 08, Jan 15, etc... if they
attend the weekly class, they get a check to indicate attendance.
What I want to do now is only bring up those who have attended at least once
within the last 6 weeks. Anyone who has not attended within the last 6 weeks
we want to mark as InActive. Our attendance rooster is getting huge! and we
want to narrow it down.

Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When discussing queries we don't care what the form looks like, it has
no bearing on constructing the query. You should tell use about any
tables/queries involved in your problem.

Could you show the definition of your table(s) and some sample data?
Having "several fields with the date" indicates a possible non-Normal
table, which indicates a very complex query to solve your problem.
Normalized tables usually require simpler queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlCT3IechKqOuFEgEQLOWACfdF+8qfZKSgQJlerlFc0YQ5SAcCUAnj23
lbvSvsZCTN45gH7Yl4ILiR3t
=HXgn
-----END PGP SIGNATURE-----

 

[Guest]

Ok, well... field 1 is a yes/no field with no default value. field 2 is
deisinged the same way, field 3, the same, and so on. What I would like to do
is create a query that takes for example fields 5 through 10, if there is any
value of "yes" any of those fields (5 through 10), it would come up in my
query.

 

[Guest]

Firstly - your database design is not good. As you suggest the form would
have to be amended regularly and would just get rediculously large as your
simple database is not normalised.

You should have one table for student - and another table for attendance.
With a one to many relationship between Student and Attendance.

The ATTENDANCE table could have an auto number for the primary key and
another (eg: called attendnace_date) that is date formatted.

You then have a form with the student details (from the student table) and a
subform of the attendance dates (from the attendance table).

You then just enter a new record on the subform (ATTENDANCE table) for when
the student attends and enter the appropriate date [which could have a
default of todays date eg: date()].

To then identify those students that haven't attended in the last 6 weeks is
a very simple query.

Ie: in the criteria field of the QBE GRID for attendance_date type:

<date() - 42 or is null

Or to view those who have attended just type in attendance_date criteria
field:

date() - 42

Hope this helps.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yup, not Normalized. I can give you a solution, but it would be far
better if you changed your tables design to conform w/ relational
database design standards. Read a book on database design for more
info. I usually recommend Hernandez's _Database Design for Mere
Mortals_.

For Yes/No columns you should always set a default, usually No
(False=0), otherwise, you'll have NULLs in the column. Having NULLs in
a numeric column (Yes/No is a numeric data type of -1 [True] and 0
[False]) can complicate calculations.

So, having said all that, a try at your solution:

SELECT <column list>
FROM <table name>
WHERE (Nz(col5,0) + Nz(col6,0)+ Nz(col7,0) + Nz(col8,0) + Nz(col9,0) +
Nz(col10,0))<0

The Nz() function checks if the column has a NULL value; if it does, it
substitutes zero in the calculation. If we add all the columns together
and get a value less than zero we know that one of the columns has a Yes
(-1) value.

Place the query in a QueryDef's SQL View. Then put the list of column
names you want in the SELECT clause and the table name in the FROM
clause. Substitute your actual column names for "col?" in the WHERE
clause.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlCxZYechKqOuFEgEQK+qQCaA9F1lEKhTSINEfVQYuwCNCSvtfAAoLvG
mWLpjx5fSI2JCZQC+6z3RuIv
=YgmF
-----END PGP SIGNATURE-----

 

[Guest]

Thanks...
I had a feeling my table was not correct. That was the way I was advised to
do it by someone else who knew Access better than I...trust you gut I guess..

I did want to clarify one quick thing if I could. So the subform
(ATTENDANCE table) if would not be a yes/no field but a date/time field? And
it would then be a list of dates, for example, 1/5/05, 1/6/05,
1/8/05...(maybe not in attendance on 1/7/05)...

Thanks!

Firstly - your database design is not good. As you suggest the form would
have to be amended regularly and would just get rediculously large as your
simple database is not normalised.

You should have one table for student - and another table for attendance.
With a one to many relationship between Student and Attendance.

The ATTENDANCE table could have an auto number for the primary key and
another (eg: called attendnace_date) that is date formatted.

You then have a form with the student details (from the student table) and a
subform of the attendance dates (from the attendance table).

You then just enter a new record on the subform (ATTENDANCE table) for when
the student attends and enter the appropriate date [which could have a
default of todays date eg: date()].

To then identify those students that haven't attended in the last 6 weeks is
a very simple query.

Ie: in the criteria field of the QBE GRID for attendance_date type:

<date() - 42 or is null

Or to view those who have attended just type in attendance_date criteria
field:

date() - 42

Hope this helps.

Ok, I'm not sure if this is really complicated or simple...Here it is.
We have a database of attendance. The Form is really simple Name, then we
have several fields with the date, Jan 01. Jan 08, Jan 15, etc... if they
attend the weekly class, they get a check to indicate attendance.
What I want to do now is only bring up those who have attended at least once
within the last 6 weeks. Anyone who has not attended within the last 6 weeks
we want to mark as InActive. Our attendance rooster is getting huge! and we
want to narrow it down.

Thanks

 

[Guest]

I had a bad feelig about this database, someone else is more knowledgeagle
about Access suggested that I design it this way. I was never able to
understand why. I think I am going to work on redesigning it. Thanks for
the book suggestion...I am always looking into understanding Access...

Blessings & Thanks!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yup, not Normalized. I can give you a solution, but it would be far
better if you changed your tables design to conform w/ relational
database design standards. Read a book on database design for more
info. I usually recommend Hernandez's _Database Design for Mere
Mortals_.

For Yes/No columns you should always set a default, usually No
(False=0), otherwise, you'll have NULLs in the column. Having NULLs in
a numeric column (Yes/No is a numeric data type of -1 [True] and 0
[False]) can complicate calculations.

So, having said all that, a try at your solution:

SELECT <column list>
FROM <table name>
WHERE (Nz(col5,0) + Nz(col6,0)+ Nz(col7,0) + Nz(col8,0) + Nz(col9,0) +
Nz(col10,0))<0

The Nz() function checks if the column has a NULL value; if it does, it
substitutes zero in the calculation. If we add all the columns together
and get a value less than zero we know that one of the columns has a Yes
(-1) value.

Place the query in a QueryDef's SQL View. Then put the list of column
names you want in the SELECT clause and the table name in the FROM
clause. Substitute your actual column names for "col?" in the WHERE
clause.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlCxZYechKqOuFEgEQK+qQCaA9F1lEKhTSINEfVQYuwCNCSvtfAAoLvG
mWLpjx5fSI2JCZQC+6z3RuIv
=YgmF
-----END PGP SIGNATURE-----

Ok, well... field 1 is a yes/no field with no default value. field 2 is
deisinged the same way, field 3, the same, and so on. What I would like to do
is create a query that takes for example fields 5 through 10, if there is any
value of "yes" any of those fields (5 through 10), it would come up in my
query.


: