building a db

[ccg]

Hi, I am desparate!! I work for a high school and need a database to record student community service hours over their high school careers. I have been trying to do this on and off since the summer. I promised I would have it done by the end of winter break but........I may have to go back to a spreadsheet if I can't get this right. That has proved to be a royal pain in the past few years.

I have set up tables but getting the relationships right is a problem. I dont know if it is possible but I would like to set it up so that I have a student form with 4 subforms for each school year. Each subform (4 tabs) will have the title of the school year (i.e. 05-06). We don't allow them to get credit if they go over on their hours for a given year. In other words, they can't do all their service hours in say, freshman year. They have to do 10 as freshmen, 20 as sophomores, 30 as juniors and 40 as seniors.

Each year I can refresh from the school db (for new students, etc.) but I can't go in and create a report in that program as we don't have the capability.

I am trying to teach myself but every tutorial I come across doesn't give reasons why you do something so I can figure out how to convert that info to help me. I have been researching this for a long time and looking at sample dbs.

Can anyone help? I am going to try to attach a copy of the mess I have made so far, Hopefully

Thanks in advance.

 

[Fuga]

Well it´s hard to know exactly what data you need to view, how you wish to enter it, what it looks like from the beginning e t c.

However, from what I understand, you need two tables to store the actual data.

1. personal info about the student. This table chould contain a uniqe ID to identiy the student. You can do this with an autonumber field for instance.

2. comunity service. Here you have the information about the service. Like number of hours, type of work et c. And the datefield. This table also contains the number field linking the student to the service. However in this table you allow more than one entry for the number. Thus creating a one-to-many relation between student and comunity service.

To view the data the way you describe it, you create queries. If necessary, one for each year. there may be a way to restrict the data in the actual suborm to show only year 1, year 2 etc, but it will be easier to put in conditions in the query. Use wizards. To extract the year from the date, you use the year() function. To find the first/last year, sort the query ascending/descending on the date field and select top 1 (up by the summation sign in design view).

Of course, there is no law against having a field in the service table for year 1, 2 etc, and then use this field for where-clause.

Having done your queries, use the wizards to create a form/subform. You will be given the option to link the master form (which you base on table 1) to the child form (base on table 2). Link the related fields.

Hope this helps.


Fuga.

 

[bonekrusher]

I bet if you post what you have, someone will finish it.

Bones

 

[ccg]

thanks

Thanks for your input, it helps. I may be gun shy and making it harder than it should be.

I tried to attach the db but it failed everytime. Not sure why.

I was wondering if I could build an expression for each school year that states if: 9th grade, then = 10 hours for service completion, 10th grade, then = 20 hours for service completion, etc.

Thanks for your help, I am confusing myself the more I research it.

I am going to try again to attach it.

 

[bonekrusher]

you need to zip it and then post it

Bones

 

[ccg]

service database

Here is the database I started.

Don't laugh!

 

[Sergeant]

ccq, If this is actual student data, I recommend that you remove the attachment and scramble the data or make some bogus student data before reposting it.