Can I add multiple names to one ID (Autonumber)?

[jewll]

Hi Everyone,

I'm fairly new to Access databases, so I apologise in advance if my approach is not the best method... I'm surre (and hope) I'll be corrected if I'm wrong.

I work in University accommodation housing 3000 students and we conduct meetings with the residents when they are to be disciplined.. for various things such as smoking in their room, poor conduct, or cause they've broken a window decided playing football in the kitchen using a window as a goal.



They can then be given a fine or community service or a warning.

I've created a database that logs a these meetings and it works fine when one person is called in, it logs the individual (Name, ID Number, Accommodation, Flat & Room No) and I can transfer the details to a new community service record or fine record and it all links to the auto generate ID number (known as the Case Number).

Though in cases where there is more than one person involved and each are given their own "discipline" I haven't got the knowledge to know how to keep it linked to the same 'Case Number'.

Is there any work around on this?

Can I create a case, add more than one person to the meeting record who will be present?

Then following that meeting for example Alan gets 10 hours community service, John gets a £40 fine, and Dave gets a £40 fine and 5 hours community service? Though all 3 are still linked to the same case number from the initial meeting?

If I've explained this poorly or posted in the wrong area then please let me know (I'm completely new to forums in general!)

Thanks in advance for any words of advice,
Joe

[jewll]

Ps, It may also be worth mentioning that the details of the residents are not kept on this database, so they have to be manually input by the user as there is no table storing the data

Thanks,
Joe

[enquiries]

Hi Jewll, - just putting out my two cents, although still a novice here I've almost completed my first big database (have had a lot of help from this forum) . I'm not sure I understand what you meant by "transfer the details to a new community service record". How many tables do you have in your database? It will be worth it to have a look at creating a simple ERD (entity relationship model) for your tables and creating relationships. You would have to start storing the details of the residents as well. What it sounds like you need are 3 tables:
Residents Details -> ID Number, First Name, Last Name, Accomodation, Flat
Discipline Details -> DisciplineID, ID Number, Case Number, Discipline Action
Discipline Records -> Case Number, ID Number, Offense Committed


Primary keys are in bold. Primary key and foreign keys are bold and italic.There's a good tutorial below on ERD and what you call normalisation, a good design will help you achieve what you want the database to do and will allow you to exploit the functions available in access accurately.

Hope that helps

[enquiries]

Forgot to put the link to the tutorials
Head over here to: rogersaccesslibrary.com

Trust me, it's worth it to go through the tutorials

[jewll]

Hey There,

Thank you for the help, it has cleared it up a bit more for me as I can visualise what I need to do now!

To be honest, now the discipline section will be able to populate how it needs to, the other parts for community service and fines will all fall into place.. it was this first part that I was struggling with!

I'll check out the tutorials now

Thank you very much!

[enquiries]

Hi There,

Great, the tutorials will explain it in more detail - when you implement the 3 tables in access, you'll have to create relationships (one to many relationships) and tick enforce referential integrity which will be how you will be able to create one case with more than one person in it and with different disciplinary actions for each person.

Good luck And let me know how it goes.