database tables structure help

[capdownlondon]

ok - i've made a couple of databases for my workplace (a secure unit for 11-14 year olds), and they have really helped out. I hadn't really used acccess before but thought i would give it a go. anyhow, these databases took me hours and hours, because i was learning how to use access whilst making them. the databases work and help, but they are a horrible mess and could have been done a much better way i'm sure.

to the point....
i want to make a new database that does something that shouldn't be too difficult, but was wondering if some people could give me a few tips on how to structure this.

at the unit if the kids have behaved badly we give them a sanction (red card). we record these red cards in a table(by hand) and they are given a code depending on what they are for.
it looks something like this:

anyhow i would like to create a database that will store this information.
in addition i would like to be able to generate graphs showing how many red cards ben recieved for being rude each week, compared to his total number of red cards he recieved each week etc and other graphs aswell, but you get the idea of what i want to do.

--- what i as is if anyone could please suggest a way i structure the tables that this information goes into ---

any other advice would be helpful too.

thanks

Adam

 

[The_Doc_Man]

first and foremost, study DATABASE NORMALIZATION. You can ask Access Help, Wikipedia(.org), or Google for those keywords.

When converting an Excel-like application to an Access application, you invariably start with flat file structuring. Access has the ability to exceed this but it takes a mindset change (or as some managers like to say, a "paradigm shift" - not that all of them really understand "paradigm" much less a shift of one...)

Once you see what we are talking about, you will understand that your desired design is quite reasonable to achieve but it won't look ANYTHING like what you have shown us.

 

[capdownlondon]

i will try and have another look at normalisation, i had a look a while back and didn't get too far. thats kinda why i was hoping someone might be able to make a suggestion on how to structure this. then maybe it will help me with the normalisation. see i was never sure if i was doing the right thing with normalisation, and how far to take it etc.

but as i say iwill have another look at it, but any other advice would be much appreciated aswell.

thanks

 

[capdownlondon]

ok i'm really confused by normalization, but i am very tired so i may try reading about it again another time.

but...

if i created a table with fields

child date card code


then used the right queries, would that be alright for what i want to do? orwould that cause problems? also would i need an ID field in with that too for anything?

 

[cpremo]

In general terms, you want to reduce the "manually entered" data as much as possible. So, for any field that can be "standardized into a seperate table, do so. For example, if you are a military organization you would want to create a table for all Ranks (let's say tblRanks) with two field (RankID and RankName). The first field is an autogenerated field with no duplicates, the second is a Text filed with no duplicates. The Key field is the RankName. Then in your "Personnel" table (tblPersonnel), you have a field called RankID that is a number field. In your Relationship screen (Tools-Relationships) you join the two tables by the RankID fields (drag from the tblRanks to the tblPersonnel table) and set the join to Enforce Referential Integrity and Cascade update Related Fields. In this instance you don't need to check the "Cascade Delete Related Records". However, if you have a sub table to the tblPersonnel table (lets say tblTrainingRecords) you would want to check the "Cascade Delete Related Records" so the sub record is delete when the master Personnel record is delete.