# Thread: Formula Help (Access 2003)

1. ## Formula Help (Access 2003)

I am again at a loss. I have a database of student registrants for different classes (up to 300 different classes). I need a way to figure up how many chairs are left in a class once a student registers.
Each Class has a different number of maximum seats.

What I have now is, in the Directors Table of Classes, a field called Max Students to contain the max number allowed. ( I have entered a few example numbers)
In the Student Registered table I am trying to figure out what field I would need to use to help me come up with the formula that says:

This student in the Excel class that starts on the 05-21-07 just took one seat of 20 available and I want a way to show 19 left.
There is another class of Excel students starting on 05-25-07 and they also have 20 seats available and I want a way to show there are 19 left.
There is a student taking Diesel Engines on 05/05/07 and they also have 10 seats available and I want a way to show there are 9 left.

I've dug on this til my brain is all crazy today and I can't think anymore...
Would someone look at this and tell me what they think I should do to solve this scenario...I would prefer if the seats left formula field would be in the Student schedule form, so when I enter a new student in the Student there would be a calculated field to tell me that there are so many seats still available in that class as I enter that new student into a class.

Am attaching a shell of the database as the whole thing is larger then the 100k max limit.

Any help here is soooooo appreciated...

2. ## Re: Formula Help (Access 2003)

For a start the table tblClassesbyDirector has an entry that does not show the max no of students for that class.
RecordID DirectorID ClassName Max Student
245 7 MS EXCEL 2000 INTRODUCTION - ADVANCED

Surely you use the max no of students and subtracting a dcount of students for a particular class and date should be enough to work this out.

3. ## Re: Formula Help (Access 2003)

I only place a few entries into the Classes by Director to use for example, as I only have 3 example students registered. I do not know what the max no is, I'm making it up (the secretaries will do this), that is why I only entered a few examples instead of 300. I do not know how to use a DCount formula...I've heard of it, but never used it.
My question is if I use the Dcount function, (and I would like to do it in the Student Registration Form as students are entered into a particular class), but there are several class names that are the same, they just start on different dates...Like the MS EXCEL 2003 INTRODUCTION - ADVANCED class...it may be given once in the directors table, but students will be entering several of the same name classes on different dates...how would I write a formula to get MAX NO OF STUDENTS - PARTICULAR CLASS (ON A PARTICULAR DATE)?

Thanks for the feedback Pat

4. ## Re: Formula Help (Access 2003)

What i forgot to mention in the previous post is that each class has to have a start and end date defined in that table.

The DCount function can be used in an unbound text box and has the definition:
DCount(expr, domain, [criteria])

An example is:
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

5. ## Re: Formula Help (Access 2003)

Thank you for the example...I will try this...

QUOTE{What i forgot to mention in the previous post is that each class has to have a start and end date defined in that table.}
In the directors table? I have these fields in the student registration table

6. ## Re: Formula Help (Access 2003)

I have tried:
=DCount("[StudentLastName]","tblStudentRegistration","[StartDate] =[ClassName]"-[tblClassesByDirector]![MaxStudents]) and it's just giving me #NAME?
I've also tried:
=DCount("[StudentLastName]","tblStudentRegistration","[StartDate]"="EMT"-[tblClassesByDirector]![MaxStudents])

What am I doing wrong?

7. ## Re: Formula Help (Access 2003)

First of all you must define the period of each class in tblClassesbyDirector or another table that is dependant on this table.

Then what date (is it startdate or enddate from the tblStudentRegistration) is to be compoared to the dates in the tblClassesbyDirector table?

You may have to do this in VBA behind the form rather than by DCount.

8. ## Re: Formula Help (Access 2003)

I knew this was going to be a nightmare.
I have a report that shows how many students are in each class, it seems there would be an easier way to do this...If I could pull into the formula I already have in the report the max number of students from the other classes by director table, and use that to minus the total of students I already show in the report it seems it would be easier...
I can't get nothing to work today, and this is probably the most difficult thing I have ever had to work through...thanks for hanging with me, but any other examples, using MY fields and not the ones off the microsoft website (as I get confused by that, been there, read them, and that's why I'm here...didn't understand them, nor could i relate them to what i was doing)...would be appreciated soooo much...

9. ## Re: Formula Help (Access 2003)

I know how you feel re the frustrations of some problems that seem so complex.

What is the criteria of the report that determines the number of students from the other classes by director table? As i said previously you need dates that the class goes for, these dates should be defined in the classes by director table.

10. ## Re: Formula Help (Access 2003)

[QUOTE] As i said previously you need dates that the class goes for, these dates should be defined in the classes by director table. [QUOTE]

But wouldn't that be duplicating data? There are nearly 300 classes listed, and by Director of course...however each of those classes are given on DIFFERENT dates all YEAR LONG....so that would be listing the 300 classes again and again and again every time there would be a new occurance of it being offered....

The report field simply counts up the =count[studentlastname] and it is based on the tblDirectors and tblRegisteredStudents query. It is grouped by DirectorID from the tbleDirectors and ClassName and the formula =count[studentlastname] is in the classname footer:

The SQL view of the Record Source is:

SELECT [tblRegisteredStudents].[ClassName], [tblRegisteredStudents].[DirectorID] AS [tblRegisteredStudents_DirectorID], [tblDirectors].[Director], [tblRegisteredStudents].[StudentFirstName], [tblRegisteredStudents].[StudentLastName], [tblRegisteredStudents].[StartDate], [tblRegisteredStudents].[EndDate], [tblDirectors].[DirectorID] AS [tblDirectors_DirectorID] FROM ([tblDirectors] INNER JOIN [tblRegisteredStudents] ON [tblDirectors].[DirectorID] =[tblRegisteredStudents].[DirectorID])

the problem seems to arise when I try to do a formula that subtracts a count of a text field [studentlastname] from a number field [maxstudents]. student last name coming from the tblRegisteredStudents and maxstudents coming from the ClassesByDirector...

?UGH I am sorry. I wish I could send this whole thing up there instead of bits and pieces...

11. ## Re: Formula Help (Access 2003)

Q. Does Director 1 have more than 1 class EMT in the year?

If so, I believe what you need is a classes table that defines when the classes are run, eg this would hold the director, class name (yuk, i hate that, i prefer to have a number to define this) and start and end dates of each class.

12. ## Re: Formula Help (Access 2003)

I will look at your advice tomorrow, I just think that is going to take a whole redesign of the database...i'm soooo tired tonight. this is not my only project, and i'm brain dead right now....patt, thank you for all your help...i will work on this a little along tomorrow, but tonight i'm so done...

13. ## Re: Formula Help (Access 2003)

Nanette,

Pat has already hinted at changes in the design of the database. In fact, before actually starting to build a database, you should carefully think out the data design. While doing this, you should keep the basic principles of relational database design in mind. Many books about Access contain an introduction on data design, and Microsoft has a nice free download: Understanding Relational Database Design.
You should only start creating the actual tables and the quesries, forms and reports based on them *after* having a clear idea of the data design.

14. ## Re: Formula Help (Access 2003)

This database was created after sitting down with a lead secretary and getting her input. She loved it. It did everything she wanted it to do. Then it was passed down to the secretaries who would actually be inputting data, and they had this last request. It was not an initial request, and came after the database was created, signed sealed, delivered and approved. Without having to completely redesign this whole thing, I thought there may be a way to instill the seats left result in a field "somewhere", without having to refer them to the report I created that shows them the result...I was looking for more convenient for them...something visible on the form as they enter a student into a class, that says...oh another student? You have 4 seats left...without looking that up in the report.

I've designed a few databases in my time. I've taught databases for 12 years. I know there is so much to learn about this program, and consistantly try to learn it...I am a Master Certified Instructor...yet sometimes out of the blue...here comes a question I can't solve...I do not know it all...I admit that...if I did I'd been bored years ago teaching this application.

I know the design is not perfect for the last request on this database. I was not asked to implement this into the databases original design...only now, and without disseminating the whole database and starting from scratch I thought there could be a way. It is okay. I can go with what I have. My hat is off to all of you as you are so much smarter in this app then I am, and have tried to help me through this difficult scenario...for that I really do thank you.

15. ## Re: Formula Help (Access 2003)

Nannette,

As Pat (an abbreviation of Patrick, not Patricia, by the way) has indicated, there is no way to calculate what you want if you don't have information about when a class takes place. This information should either be in tblClassesByDirector or in a separate table.
Also, the table that lists the students' personal information such as name and address should be separate from the table that lists which students attend which classes and when, otherwise you'd have to repeat all that personal information for each class a student attends.
And, as Pat already noted, you should use a number ID for classes to specify attendance, not the class name.

So I fear you cannot get the info you want out of the database as it is currently set up. That does not mean you'll have to throw away what you've got, but if you need the extra info, you will need to change its design.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•