Count Number of times date selected

G

Guest

Hi

I have a Rehab Schedule Table, that lists the class name, date started, Max
class size, etc. RehabID is the PKey, connected to the Schedule Table. In
the schedule table, this is where all the data on each client is captured.
So... there would be the schedule table with client name, address, etc. Then
the subform has the class name, date, time, cost, etc. The Rehab ID is used
in the subform to choose which class this person is registering for.

What I am needing to do:

In the Rehab table, the data will be entered like this: Pilates, Sept 1
(StartDate) 8 (MaxClassSize). When I am in the schedule and select this
class, I would like access to count the number of times that this class
StartDate has already been selected.

If I can get it to count the number of occurances, then I can do an iif
statement stating "Register this class" or "Class full, Register next class".
But.... I can't get to this point until I figure out the best way for the
date count.

Thanks for any help
 
D

Duane Hookom

I think you can use a row source for your combo box that left joins the
Rehab Schedule table tot he Schedule table and groups by the values in the
Rehab Schedule and counts from the Schedule table. This will provide a count
as a column in your combo box.
 
G

Guest

Thanks for your response. Just to make sure I understood your assistance,
this is what I have:

In the ScheduleSubform, I have a combo box on the StartDate field. This
bound to StartDate from Rehab table and total is set to Group by. I also
have as a column to RehabID and ClassName, both from the Rehab table and
total set to Group by. Now, I have added another column, StartDate from the
Schedule table and the total set to Count.

Then, I created an iif statement to read:
=IIf([Therapy]="Rehab" And [StartDate]<[MaxClassSize],"Register This
Class","Register Next Class")

I am getting the following error message: #Name?

According to the help files, this is because the name of the field does not
match. I added the field MaxClassSize to my form as it was not originally on
it.... this did not seem to help.

thanks
 
D

Duane Hookom

I'm not sure where the IIf(...) comes from. My suggestion was to count the
number of startdates or whatever you need to count to compare to
MaxClassSize. You can either choose to not display records where the count
is >= MaxClassSize or you can use code in the after update to check the
count versus the MaxClassSize to warn the user.

--
Duane Hookom
MS Access MVP


Beaner200 said:
Thanks for your response. Just to make sure I understood your assistance,
this is what I have:

In the ScheduleSubform, I have a combo box on the StartDate field. This
bound to StartDate from Rehab table and total is set to Group by. I also
have as a column to RehabID and ClassName, both from the Rehab table and
total set to Group by. Now, I have added another column, StartDate from
the
Schedule table and the total set to Count.

Then, I created an iif statement to read:
=IIf([Therapy]="Rehab" And [StartDate]<[MaxClassSize],"Register This
Class","Register Next Class")

I am getting the following error message: #Name?

According to the help files, this is because the name of the field does
not
match. I added the field MaxClassSize to my form as it was not originally
on
it.... this did not seem to help.

thanks
Duane Hookom said:
I think you can use a row source for your combo box that left joins the
Rehab Schedule table tot he Schedule table and groups by the values in
the
Rehab Schedule and counts from the Schedule table. This will provide a
count
as a column in your combo box.
 
G

Guest

Thanks Duane for all your help. This is my first time using code and feeling
a bit intimidated by this project. I have spent the last few weeks reading
up on it, but am having difficulties on exactly what the code should like.
If you could provide more assistance, that would be much appreciated.


Duane Hookom said:
I'm not sure where the IIf(...) comes from. My suggestion was to count the
number of startdates or whatever you need to count to compare to
MaxClassSize. You can either choose to not display records where the count
is >= MaxClassSize or you can use code in the after update to check the
count versus the MaxClassSize to warn the user.

--
Duane Hookom
MS Access MVP


Beaner200 said:
Thanks for your response. Just to make sure I understood your assistance,
this is what I have:

In the ScheduleSubform, I have a combo box on the StartDate field. This
bound to StartDate from Rehab table and total is set to Group by. I also
have as a column to RehabID and ClassName, both from the Rehab table and
total set to Group by. Now, I have added another column, StartDate from
the
Schedule table and the total set to Count.

Then, I created an iif statement to read:
=IIf([Therapy]="Rehab" And [StartDate]<[MaxClassSize],"Register This
Class","Register Next Class")

I am getting the following error message: #Name?

According to the help files, this is because the name of the field does
not
match. I added the field MaxClassSize to my form as it was not originally
on
it.... this did not seem to help.

thanks
Duane Hookom said:
I think you can use a row source for your combo box that left joins the
Rehab Schedule table tot he Schedule table and groups by the values in
the
Rehab Schedule and counts from the Schedule table. This will provide a
count
as a column in your combo box.

--
Duane Hookom
MS Access MVP


Hi

I have a Rehab Schedule Table, that lists the class name, date started,
Max
class size, etc. RehabID is the PKey, connected to the Schedule Table.
In
the schedule table, this is where all the data on each client is
captured.
So... there would be the schedule table with client name, address, etc.
Then
the subform has the class name, date, time, cost, etc. The Rehab ID is
used
in the subform to choose which class this person is registering for.

What I am needing to do:

In the Rehab table, the data will be entered like this: Pilates, Sept
1
(StartDate) 8 (MaxClassSize). When I am in the schedule and select
this
class, I would like access to count the number of times that this class
StartDate has already been selected.

If I can get it to count the number of occurances, then I can do an iif
statement stating "Register this class" or "Class full, Register next
class".
But.... I can't get to this point until I figure out the best way for
the
date count.

Thanks for any help
 
D

Duane Hookom

Consider the Northwind database and the Products and OrderDetails tables. I
could create a combo box to select a product from the products table with
counts of previously sold products and the units in stock field:

SELECT [Order Details].ProductID, Products.ProductName,
Count([Order Details].ProductID) AS CountOfProductID,
Products.UnitsInStock
FROM Products LEFT JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID
GROUP BY [Order Details].ProductID, Products.ProductName,
Products.UnitsInStock;

In the after update event of the combo box, you could check the values of
columns to see if the product was already at its limit.

--
Duane Hookom
MS Access MVP


Beaner200 said:
Thanks Duane for all your help. This is my first time using code and
feeling
a bit intimidated by this project. I have spent the last few weeks
reading
up on it, but am having difficulties on exactly what the code should like.
If you could provide more assistance, that would be much appreciated.


Duane Hookom said:
I'm not sure where the IIf(...) comes from. My suggestion was to count
the
number of startdates or whatever you need to count to compare to
MaxClassSize. You can either choose to not display records where the
count
is >= MaxClassSize or you can use code in the after update to check the
count versus the MaxClassSize to warn the user.

--
Duane Hookom
MS Access MVP


Beaner200 said:
Thanks for your response. Just to make sure I understood your
assistance,
this is what I have:

In the ScheduleSubform, I have a combo box on the StartDate field.
This
bound to StartDate from Rehab table and total is set to Group by. I
also
have as a column to RehabID and ClassName, both from the Rehab table
and
total set to Group by. Now, I have added another column, StartDate
from
the
Schedule table and the total set to Count.

Then, I created an iif statement to read:
=IIf([Therapy]="Rehab" And [StartDate]<[MaxClassSize],"Register This
Class","Register Next Class")

I am getting the following error message: #Name?

According to the help files, this is because the name of the field does
not
match. I added the field MaxClassSize to my form as it was not
originally
on
it.... this did not seem to help.

thanks
:

I think you can use a row source for your combo box that left joins
the
Rehab Schedule table tot he Schedule table and groups by the values in
the
Rehab Schedule and counts from the Schedule table. This will provide a
count
as a column in your combo box.

--
Duane Hookom
MS Access MVP


Hi

I have a Rehab Schedule Table, that lists the class name, date
started,
Max
class size, etc. RehabID is the PKey, connected to the Schedule
Table.
In
the schedule table, this is where all the data on each client is
captured.
So... there would be the schedule table with client name, address,
etc.
Then
the subform has the class name, date, time, cost, etc. The Rehab ID
is
used
in the subform to choose which class this person is registering for.

What I am needing to do:

In the Rehab table, the data will be entered like this: Pilates,
Sept
1
(StartDate) 8 (MaxClassSize). When I am in the schedule and select
this
class, I would like access to count the number of times that this
class
StartDate has already been selected.

If I can get it to count the number of occurances, then I can do an
iif
statement stating "Register this class" or "Class full, Register
next
class".
But.... I can't get to this point until I figure out the best way
for
the
date count.

Thanks for any help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top