referential intergrity

[Jamesy_007]

hey, guys

My name is James in the last few months I've been using Access as a database software .. presently am a data clerk at my place of employment [Prison Guard] but unbeknownst to my superiors am self taught with access and excel meaning never did any school training. I've built a database mainly primitive meaning with no coding in the background...of 250 names and counting

However am still trying to figure out using [Referential Integrity] how track repeat offenders

1. Meaning persons who came in several times for various offences exg. Robbery, Murder, GTA... etc??? 1 to many relationship


2. And how to do this using Dates...of Entry into said prison exg you have several dates connected to your name connected to various offences


I've sort of figured out no.1 but still struggling with no.2


Basically am trying to track repeat offenders with the dates, crimes connected when they came in

[Micron]

tblOffender (OffenderID autonumber, first & last names, DOB, etc.)
tblOffense (OffenseID autonumber, short form name, description, etc.)
tblOffJunction (OffJuncID, OffenderIDfk as long [autonum from offender], OffenseIDfk as long [autonum from offense], date of entry, etc.)

with this, an offender is only entered into the offenders table one time. Same for offenses you'd deal with. The junction table would repeat the offender id and offense id as many times as necessary. A main form with offender data and a subform with offense data linked by offender fields would be the usual way of representing the data. With this schema you should be able to easily report on #offenses by type, #offenses by a person - just about anything you have a field for.

[davegri]

See if this will get you started:
Jamesy-davegri-v01.zip

[Jamesy_007]

Hi micron
Thanks very much for your help. to be honest i taught i understood the junction table theory and sort of figured it out i guess the real or should i say other issue was on the said offenders table which will have some basic info like name DOB etc. that table on the left under Home tab Records has a subdatasheet function on the left of the table. its here am trying to click on offenders name and view his other offences and the dates. What happens is the table with offenderID number and OffenseID number and the dates shows up . but in the offenseID table is here where the one to many concept is understood same subdatasheet view the offenders bio records will show exg say I click shoplifting all the guys or records of offenders under shoplifting will show. I HOPE you're understanding what am trying to say
is there a way to do the same in reverse offenders tableview i.e click your record row and the subdatasheet will show up all your offenses and dates. NOTE in queries the many to many displays perfectly

[Jamesy_007]

Hey, davegri
this looks definitely like what am trying to set-up and accomplish for Reports and staff meetings THANKS for this tip, After creating a form view do you insert offenseJun table but how do you get the drop dwn list if I've​ already assign numbers to various offense

[orange]

I recommend that you work through 1ZYXLabs or 2ClassInfoSystem of the tutorials from RogersAccessLibrary. By working through the tutorial(s) you will experience the process of database design including Normalization. A tutorial will take about 30-45 minutes for someone with some general database experience.
Getting your tables and relationships designed and tested to meet your business requirements is critical to a successful database application.
You must work through the process to get real value from the tutorials. And you will learn a process that can be used with any database.
There are a number of articles you may find helpful in the Database Planning and Design link in my signature.

[davegri]

Hey, davegri
this looks definitely like what am trying to set-up and accomplish for Reports and staff meetings THANKS for this tip, After creating a form view do you insert offenseJun table but how do you get the drop dwn list if I've​ already assign numbers to various offense

The problem you are running into is because you are working directly with the tables to enter data. You should never do that, because your foreign keys will not be inserted properly. If you use my example DB, you would not run into that problem. This also addresses the problems you mention in post #4.

[davegri]

Here is an updated version that is identical to the first, except that it will allow you to add a new offense to to the offense table if you type in an unknown offense in the dropdown.

Jamesy-davegri-v02.zip

[Jamesy_007]

Thanks for your Tip in Access its helping me a lot. At this stage in my life and getting a new excitement towards learning and am embracing the Challenge, want understand something is there a base code in programming I can learn to link oneMain Form/Table to various forms bound in Tabsforms subform Display or do I write a code for each form in Tab displayed and link it back to the Main Form/table

Also
built another database on the model you showed me put in code everything wrks pefect except for one thing the add new [record button]running on macros in the background is not completely going to a new record don't knw WHY

[Jamesy_007]

Thanks for the info and encouragement

[davegri]

built another database on the model you showed me put in code everything wrks pefect except for one thing the add new [record button]running on macros in the background is not completely going to a new record don't knw WHY

You've provided no clues. Can you post the DB here?

[Jamesy_007]

This is part of the project want tblInmtes form to be a Main form and create a Tabs subdata forms; that other forms are linked in other words one person [ID] is linked where other forms are data entries to their files also HOPE you'll are understanding me because you guys are really advanced to my computer skills level

[davegri]

Not sure what you're looking for, but here's an inmate form with 3 tabs.

Jamesy 007 DataProject-davegri-v01.zip

[CarlettoFed]

I believe there are problems in database design.
In the tblCaseAjourn table the periods Dates_1, etc ... are fixed or could a Dates_6 field be added?
Because if this were the case you need to normalize the database:

tblCaseAjourn

Inm_pd
Case_pd
CaseNo
Notes

tblCaseAjournDates

CaseAjID
At your place

also for each date Case_pd always remains the same or can it change?
Because if the Case_pd field from the tblCaseAjourn table changes it must be moved to the tblCaseAjournDates table.
The same consideration applies to CaseNo.

Furthermore, you must learn to correctly set the properties of the various fields in the tables, especially:

Field size, for Text-type fields
Request
Indexed, especially Duplicates not allowed to avoid duplication

Furthermore, I believe that the relationships are not exactly correct because, not knowing how the whole process is managed, I believe that some tables should not be related to tblInmates but to tblCaseAjourn.
For this last reason you should explain the whole process in detail for example:

the subject enters for a reason
during this period he may have exit permits
the detention period ends
come back for another reason
during this period he may have exit permits
the detention period ends

I apologize if what I wrote could be difficult to understand as being Italian and not knowing English well I resorted to the Google translator.

[Tuckejam]

Hey Jamsey_007

Check out Udemy.com, there are a lot of cheap training courses on that website that are really good.

all of the courses show that they're $149.99 discounted to $12.99 so it seems sketchy at first

MICROSOFT ACCESS complete beginner to advanced (Kyle Pew Instructor)
its 14 hours of video that was (i thought) really well done, it gave me a solid footing to make the jump from Excel to access.