MS Access: copy data?

June 21, 2010 7:46 AM

I can't get my Access database to do what I thought I could. Lots of details inside, again.

This is much like the last question.

Students are only ever enrolled in one course (=program). If they graduate, they do not come back to do a different one.

Each course has 3 cost components:

1. Enrollment fee. Identical between courses. Could change between program start dates, so students starting in 2010 pay less than those in 2011.
2. Tuition fee. Different between courses. Could change between program start dates.
3. Kit fee. Different between courses (0 for some). Could change between program start dates.

Obviously, when I change costs for a program, students who are already enrolled shouldn't have their costs changed. I don't need to have the historical costs, but if they are there it is fine as well.

So it seems like my options are "figure out some way to automatically copy the cost information when data is entered" (some by form, some imported via excel spreadsheets -- I can probably force everyone to use one of the two options, in which case I'd prefer excel) or "do the right lookups for the different costs" -- but I can't figure out how to get either of these to work properly. I can redo tables to make either option work correctly.

All my forms and reports look nice, but are still lacking that minor thing called "accurate cost data".

posted by jeather to Computers & Internet (5 answers total)

Best answer: You don't want to embed price information in the course table, if the course will be (other than prices) unchanging between years.

You want to have another table that links price and course and year of enrollment. That way, you just enter "Bobby Jones started in COURSE LM100 in 2010" and your report calls up something like (I'm speaking SQL here):

SELECT price FROM courseprice WHERE course='LM100' AND year='2010'

and you drop that value into your report in the "cost" field.
posted by Tomorrowful at 8:03 AM on June 21, 2010

(You'd probably have three columns in your cost table, actually, assuming that all three costs change on the same schedule.)
posted by Tomorrowful at 8:04 AM on June 21, 2010

Best answer: If all your costs don't change at convenient annual times, you could look at having an eftv_from and eftv_to date against each cost, and then store the enrolment date of each student, using that to identify which cost was live at the time.
posted by Simon_ at 8:12 AM on June 21, 2010

Yeah, you just need to keep a separate table for the prices at various times. Essentially there's a one to many relationship between courses and costs.
posted by delmoi at 8:20 AM on June 21, 2010

Response by poster: I could have costs per program start date (up to 4x an academic year, which I encode mm/yy -- but could be searched the same way), and that seems like a lot of duplication of information.

I would have a student table with student ID, program start date and course ID (and other stuff irrelevant to this particular question), a course table with course ID and course name, and then a price table with course ID, start date, and kit and tuition fees? I'd separate out an enrollment fee table because it's standard for probably 15 courses a year -- I could, at the very least, bring it down to the 4 start dates.

The thing is, although any of these *can* change, the reality is that only the kit will change every 2 or so years, everything else perhaps every 5-10 years. I'm not sure if this changes the solution -- I can, after all, just keep putting in new course fees each starting time, but is there any reasonably simply way to search for the latest date that is before the program start date?

If start date is 09/2010, or 02/2011, etc, and program is ABC, and the price has a date 08/2010, and 08/2009, can I get it to bring up 08/2010? I can ensure that price changes are done in a month that is before the start date.
posted by jeather at 8:24 AM on June 21, 2010