Help Putting Together a Project

[dazed]

So I have a database project due next Monday at midnight. The requirements are
1. At least three data tables with relationships between the tables,
2. At least five queries that include selected fields from multiple tables,
3. At least a main form with a subform (with selection combo field and a calculated field in the main form to aggregate on a field in the subform,
4. At least two reports,
5. A navigational (or switchboard, or other custom) form as the first screen that ties all components together. All components must be well designed to show a high level of professionalism.

The system must be documented in a report, consisting of: A cover page with your name and the date of submission, followed by a page for the background information, and several pages of the screen prints of major elements (table, form, query, report, and switchboard pages).

I was thinking of doing a clinic/hospital's database. So far I've got the 3 tables as Patients, Employees, and Appointments (could always add more if needed). I'm not sure what to do for the queries. If i get the queries, the reports will obviously come as no problem since that's just the click of a button. I'm not sure about what to do with step 3. Maybe have each Dr. then a subform of their patients? Thanks to anyone who helps

[June7]

Doctors and patients have a many-to-many relationship. This requires a 'junction' table to associate patient and doctor. This could possibly be the Appointments table.

DateVisit
DoctorID
PatientID

So the form/subform arrangement could be:

1. main form bound to Employees and subform bound to Appointments with combobox to select patient

2. main form bound to Patients and subform bound to Appointments with combobox to select doctor

What calculation would be of value? Count of appointments?

[dazed]

Yea i was thinking count how many patients are seen each day or something like that

[dazed]

I'm trying to get a text box that shows how many appointments each doctor has. the code i have is

=Count([frmAppointments Subform].[Form]![txtAppt Number])

but it's giving me #error. I've tried Sum too. Another assignment I had which used this same thing had the code

=[frmGuestsWithReservationsSubform].[Form]![txtPeopleSum]

and that worked, so I tried to recreate that in the context of this database, but that didn't work either

[June7]

Without seeing the assignment, hard to know, but I am guessing that txtPeopleSum was a textbox in subform that used Sum() or Count() function.

Aggregate functions like Sum and Count must refer to fields of form or report RecordSource, not names of controls.

[dazed]

People in the old assignment was a textbox just like Appt Number is

Is there anyway I can show you my assignment to the extent that you can tell me exactly what's wrong?

[June7]

You can attach file to post. Follow instructions at bottom of my post.

[dazed]

Project.zip

Yes, I know it's extremely basic and ugly. I'm gonna go back over it once i have the functions worked out the make it better lol

BTW: thanks for helping. My teacher was chinese and his english was so broken that it wasn't almost impossible to learn anything from his class, so everything I learned about Access I taught myself (which wasn't too terribly hard). It's nice to have some kind of help though

[June7]

Options:

1. subform set for Datasheet view - open the form/subform in normal view
click in the subform then click the Totals button on ribbon
select Count below any of the fields
cannot have textbox on main form reference this calculation

2. subform set for Datasheet view - open in design view
textbox in subform footer section with calc: =Count(*)
textbox on main form with expression: =[frmAppointments Subform].[Form].[textboxname]

3. subform set for Continuous view (can arrange the controls to look like datasheet) - open in design view
textbox in subform footer section with calc: =Count(*)
textbox on main form not necessary

[dazed]

Thank you!
I went with the first option because it was the most simple, but does it satisfy the requirement "with selection combo field and a calculated field in the main form to aggregate on a field in the subform" since the Totals is in the subform, or do I need to do one of the text box options so the totals can be on the main form?

[June7]

The aggregate calc has nothing to do with a 'selection combo field'.

It does not technically put the value on the main form but unless it is needed on main form for use in some additional calcs, why would it need to? As long as the aggregate calc is viewable.

[dazed]

Yea but that requirement might be a reference to the other assignment (the one where I used that other code I posted and it worked) to make sure we know how to do that. I think it looks better with the totals in the subform personally

EDIT: I went ahead and went with option 2. I kept getting an error until i realized i had put in the wrong text box name for the =Count expression. Finally! Now to finish the last 2 steps lol. Thanks again