How do I create & pick up static data to calculate fees? 2

[RalphMorris]

I'm trying to cobble together a replacement database for a private school and need to establish a list of fees - Tuition, Piano(!) etc., which will change each year. They need to be picked up by a Pupils' Form (maybe using Check Boxes to identify which fees are relevant to that pupil) in order to calculate the total Invoice amount. I did it years ago in Approach & it took about 10 minutes. Ten days into Access & I seem to be getting further away from what must be an easy process - mustn't it??!!
I have been playing with the concept of:
Table 1: [Fees] Junior;Infant;Violin;Piano .....
Table 2: [Pupils] Name,Junior(Y/N);Infant(Y/N);Violin(Y/N);Piano(Y/N) ...
THEN IIf([Junior(Y/N)],Junior,0) and so on ...but I can't get a combination of anything which works. It seems to be because I haven't established a relationship - but I can't visualise one which will work and allow Pupils to be the super-ordinate Table (which I need to do for relationships, classes etc.)
And I think I've become fixated.
The answer's probably approached from a different direction - and now I can't see the wood for the trees !!

 

[RalphMorris]

OOpps - Sorry folks - I forgot to say that I'm using Access 2002 from OfficeXP, running under WindowsXP(Home).
Ta

 

[dhookom]

STOP! You are using data values as field names. What happens if they begin offering a new service? Are you going to add:
-new field in table
-new fields in queries
-new controls on forms
-new controls on reports
I hope not.
Take a look at the Northwind sample database and think of your pupils as customers and your services (fees) as products. Each pupil that incurs a fee should create one new record in a table of PupilFees.
Your current structure is destined to be a maintenance nightmare.

Duane
MS Access MVP

 

[willir]

RalphMorris

Read the following on relationships

http://www.abcdataworks.com/Developers/Index.html

Since...
- a pupil can play several instruments
- an instrument can be played by many pupils
...you have a many-to-many relationship.

As indicated by Duane, your design will lead to problems -- either can not get the required data, or it will take a lot of work.

Here is the "starter kit"

tblPupil
PupilID - primary key, autonumber or student number
LastName
FirstName
DOB
+ other required info on the pupil.


Brief discussion...
Will fees change from year to year? Do you need to be able to view fees paid last year plus the current year, etc? Will the fee vary with the scholastic level -- higher fee for advanced?

I would suggest you plan for this now. It will simplify things later on.

tblInstrument
InstrumentID or InstrumentCode - primary key
InstrumentName

(I am going to assume you will use Instrument code instead of ID for this example. And you have different rates for different levels.)

tblLevel
LevelCode


tblFee
FeeID - primary key (will simplify linkage later on.)
InstrumentCode - foreign key to Instruments
LevelCode - foreign key for Level
FeeYear - text field for year
FeeAmount - currancy

Note:
unique key = InstrumentCode + LevelCode + FeeYear to prevent duplicate entries


tblPupilFee
PupilID - foreign key to pupil table
FeeID - foreign key to fee table

primary key = PupilID + FeeID


Please note:
This is just a suggestion. There are other solutions. And I made certain assumptions which may have been incorrect. After reading the Paul Litwin document at Jeremy's web site, you should have a much better idea on your design.

Richard

 

[RalphMorris]

Just back from a few days away...

Thanks Duane/Richard - I had become fixated on using a single set of data as the Fees Table - and Approach (eons ago) had provided a way of doing it. It's obvious (when you're told!) that a Many to Many relationship is the solution.

Many thanks for your extra thoughts too - I had actually thought of a Table to enable the school to get at last year's fees - and simulate the impact on income of differential changes next year, but there's not much enthusiasm - and anyway this is/was only intended to fill a gap till they get a professional job done! If I'd pursued the thought I might even have realised what I needed to do.

Once again, many thanks ................ Eureka!!