check boxes for days of the week

[DucateAssignment via AccessMonster.com]

Hi and Thanks for the help

I have a form with several text, combo, and 8 check boxes. Each check box
represents a day of the week with the eighth box labeled daily. My problem
is I want to use one or more check boxes in combination to enter a new record
that can be queried. This database is for scheduling medication/treatment,
so I can create report indicating what day(s) of the week the patient will
arrive. If I select the Monday checkbox, I would like to query the Monday
list of medication/treatment. I would like to query combinations of Monday
and daily and print reports for people that meet these criteria. Can anyone
help?

Thanks in advance

 

[John Vinson]

Hi and Thanks for the help

I have a form with several text, combo, and 8 check boxes. Each check box
represents a day of the week with the eighth box labeled daily. My problem
is I want to use one or more check boxes in combination to enter a new record
that can be queried. This database is for scheduling medication/treatment,
so I can create report indicating what day(s) of the week the patient will
arrive. If I select the Monday checkbox, I would like to query the Monday
list of medication/treatment. I would like to query combinations of Monday
and daily and print reports for people that meet these criteria. Can anyone
help?

Thanks in advance

Please describe the structure of your tables. Hopefully you don't have
the TABLES structured with fields for the days of the week...!

The structure of the query will, of course, depend on the structure of
your tables; my inclination would be to construct the SQL string of a
query based on the selected boxes. If you can post the tablename(s)
and fieldnames of the schedule table, it should be easy to put
something together.

John W. Vinson[MVP]

 

[DucateAssignment via AccessMonster.com]

Hi John

Thanks for the reply. The table is named TblTreatmentInterval and consist of
eight check boxes. The names are each day of the week Monday through Sunday
and the last name is daily. I would like to use the form I created to enter
data regarding patients scheduled medication/treatment times and location.
This information would be forwarded to another department as a daily record.
If I could query each Boolean (yes), individually and in combination i.e.
(patients scheduled for Monday only and/or Monday and daily, Tuesday only
and/or daily and so on), as well as total the patients scheduled daily,
weekly, monthly and yearly. That would be optimum.

Thanks for your assistance

Hi and Thanks for the help

[quoted text clipped - 9 lines]

Thanks in advance

Please describe the structure of your tables. Hopefully you don't have
the TABLES structured with fields for the days of the week...!

The structure of the query will, of course, depend on the structure of
your tables; my inclination would be to construct the SQL string of a
query based on the selected boxes. If you can post the tablename(s)
and fieldnames of the schedule table, it should be easy to put
something together.

John W. Vinson[MVP]

 

[DucateAssignment via AccessMonster.com]

Hey John

I use the report to display the following date for scheduling i.e. Date()+1.
I would like to set the report to display the next calender day information.
If today were Wednesday, and I needed next Tuesday's data, can this be set-up
to display next Tuesday's information?

Any Ideas?

Hi John

Thanks for the reply. The table is named TblTreatmentInterval and consist of
eight check boxes. The names are each day of the week Monday through Sunday
and the last name is daily. I would like to use the form I created to enter
data regarding patients scheduled medication/treatment times and location.
This information would be forwarded to another department as a daily record.
If I could query each Boolean (yes), individually and in combination i.e.
(patients scheduled for Monday only and/or Monday and daily, Tuesday only
and/or daily and so on), as well as total the patients scheduled daily,
weekly, monthly and yearly. That would be optimum.

Thanks for your assistance
[quoted text clipped - 12 lines]

John W. Vinson[MVP]

 

[John Vinson]

Hey John

I use the report to display the following date for scheduling i.e. Date()+1.
I would like to set the report to display the next calender day information.
If today were Wednesday, and I needed next Tuesday's data, can this be set-up
to display next Tuesday's information?

Again, very easy with the normalized table. Very difficult with your
current spreadsheet.

John W. Vinson[MVP]

 

[John Vinson]

Hi John

Thanks for the reply. The table is named TblTreatmentInterval and consist of
eight check boxes.

Then your table design IS WRONG.

Basing table design on a form, rather than vice versa, is a very
common error, and you've fallen into it.

The names are each day of the week Monday through Sunday
and the last name is daily. I would like to use the form I created to enter
data regarding patients scheduled medication/treatment times and location.

It's quite possible to (with a little code) use a *form* that looks
like this; but your table should have a record for each treatment day.

This information would be forwarded to another department as a daily record.
If I could query each Boolean (yes), individually and in combination i.e.
(patients scheduled for Monday only and/or Monday and daily, Tuesday only
and/or daily and so on), as well as total the patients scheduled daily,
weekly, monthly and yearly. That would be optimum.

Very easily done using a Totals query if it contains fields such as:

PatientID; TreatmentDate; Location; <other fields as needed about that
day's treatment for that patient>

with one record per treatment.

What's the date of Monday on your form? January 8, 2006? January 15,
2006? July 7, 1997? Is there some indication of WHICH week in your
table or on the form?

I'd suggest having a textbox on the Form, txtWeekStart, defaulting to
the Monday at the beginning of the week (I don't know if you are
entering data for the past week, current week, or next week); you
could have a command button on the form (or use some other suitable
form event) to step through the controls. If you name the controls
chk1, chk2, ..., chk7 instead of Monday, Tuesday..., Sunday you could
use code like:

Private Sub cmdLoadTable_Click()
Dim iNum As Integer ' control counter
Dim db As DAO.Database ' define current database variable
Dim rs As DAO.Recordset ' pointer to your table
Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset) ' open table
For iNum = 1 to 7
If Me.Controls("chk" & iNum) Or Me!chkAll Then ' is a box checked?
rs.AddNew ' add a record to the table
rs!PatientID = Me.PatientID ' get the patient
rs!Location = Me.cboLocation ' get the location from a combo
rs!TreatmentDate = DateAdd("d", iNum - 1, Me.txtWeekStart)
rs.Update
End If
Next iNum
End Sub

This should also have error checking, check for missing or invalid
data, etc. but it should be a start.

John W. Vinson[MVP]

 

[DucateAssignment via AccessMonster.com]

Hey John

I apologize I am lost. The purpose of this database is to show current and
future scheduled medical/treatment appointments starting Monday, Jan. 8, 2007
if possible. I was looking for a way to create a report everyday to show
tomorrows appointments. On Friday, I need to show Saturday’s, Sunday’s and
Monday’s appointments and total the data. At the end of each month, I would
like to total the patients scheduled and total patients seen for the week,
month, and year if possible. I have approximately 6400 names, and id numbers,
the locations and times for medication/treatment would be the data entry(edit)
portion. If a patient needs medication/treatment once per day or per week, I
would like to track that and print a report for future appointments. If a
patient is scheduled for once or twice per week on certain days i.e. every
Tuesday and Thursday, I would like to track and print this data. Some
patients need treatment up to four times per day. I have four separated
combo boxes to select the designated times. In this situation, I want to
generate that patient’s name four times on a report as many days per week as
the Doctor ordered. When a patient’s treatment/medication is discontinued, I
would like that scheduling to stop on the designated date unless it is
renewed.

Thanks again

 

[John Vinson]

Hey John

I apologize I am lost. The purpose of this database is to show current and
future scheduled medical/treatment appointments starting Monday, Jan. 8, 2007
if possible.

Well... surely starting at the date that the patient starts treatment.
My suggested table would have a Date/Time field in which you could
insert any date you wish; it's easy to set a default value to insert
today's date, last Monday, next Monday, whatever date you wish. Surely
if you're using the database next October you won't want it to start
on January 8 for a new appointment!

I was looking for a way to create a report everyday to show
tomorrows appointments. On Friday, I need to show Saturday’s, Sunday’s and
Monday’s appointments and total the data.

Again... if you have a Table with a record for each appointment, this
report is very easy to create. Just use a Query selecting tomorrow's
date, or the weekend. Use a criterion on the date field of

Date() AND <= IIf(Weekday(Date()) = 6, Date() + 3, Date() + 1)

At the end of each month, I would
like to total the patients scheduled and total patients seen for the week,
month, and year if possible.

Again, easy with the appropriate criteria.

I have approximately 6400 names, and id numbers,
the locations and times for medication/treatment would be the data entry(edit)
portion.

Fine; you would have a Form based on the patients table, with a
Subform based on this schedule table. Are there (I presume) various
medications or treatments that might be given?

If a patient needs medication/treatment once per day or per week, I
would like to track that and print a report for future appointments. If a
patient is scheduled for once or twice per week on certain days i.e. every
Tuesday and Thursday, I would like to track and print this data. Some
patients need treatment up to four times per day. I have four separated
combo boxes to select the designated times. In this situation, I want to
generate that patient’s name four times on a report as many days per week as
the Doctor ordered. When a patient’s treatment/medication is discontinued, I
would like that scheduling to stop on the designated date unless it is
renewed.

Fine. The table structure requested can handle that; you'll just enter
four records (or three, or eight, or however many treatments are
needed) into the table for that patient's schedule.

Are you still focusing on the form with eight checkboxes as THE DATA
STRUCTURE? If so, stop: the form with eight checkboxes is an
*incorrect* data structure. It might be a good start for a data entry
tool, but... build the correct data structure first, and only THEN
design a form to enter data into the tables! It's like building a
house: lay the foundations (the tables) first, add the roof trim and
window treatments (the forms) later.

John W. Vinson[MVP]