Need help in setting up a DB construction

[ClownKiller]

Newbie in access is looking for someone willing to help me in creation of DB construction from scratch to finish as DB application with following:

DB application should be complete with switchboard so that users using it will not have to know a thing about access and databases at all for data entry, searching for records and so on.

an example to work with: security guard firm needs to keep records on their job on more places with various offenses and offenders and i have imagined following data to be input in DB:

1.) Offender (table about offender)


-ID
-PID (something like social security number formed from 13 numbers w/o dashes)
-SID (also as above just with 11 numbers)
-FirstName
-Lastame
-DoB
-Place
-Street
-StrAdrNr
-FiledCharges (yes/no)
-DateOfCharges (date when the charge was filled to local authority)

2.) Offense (table about offense)
-ID
-Date
-Time
-PlaceID
-StreetID
-StreetNr
-OffenderID
-TypeOfOffense
-SecurityGuardID

3.) SecurityGuard (table about guards)
-ID
-SgNumber (security guard's licence number)
-FirstName
-LastName
-SgCompanyID

4.) SgCompany (table about security guards companies)
-ID
-CompanyName

Here im in doubt as i do not know how to work things with places and street adresses as i would like to have all the places we cover in one combobox with chance to add new places if needed but also there need to be a table with all the street names in every of that places...

5.) Place (List of all places on the territory we cover)
-ID
-PlaceName

6.) Street (streets in places)
-ID
-StreetName

I think i include all parts i need for making a DB construction but im not sure if things are set right and how to relate them all together as i would then after this need to make a switchboard to entering data for events of capturing offender with following data on a single form:

about offense
date, time, place, adress, str.nr., type of offense, security guard

about offender
PID, SID, FirstName, LastName, DoB, place, adress, str.nr.

and if there is filled charge upon him or not and if yes then date of charge

after setting up a form for data entry i would also need one form for a searching through DB with multiple questions as list all offenders on some date or in date range, search by offender PID, SID, name, last name or place and adress offense ocured and so on ...

i know that this is a complete DB application and asks for a quite of job to make one like that and i hope if done right it may be good for putting it in tutorial part of this forum as well as doing me personal a good way to handle this problem and improve my knowledge about access.

[jzwp11]

I will focus mainly on your table structure since that is critical to a successful application. Forms, switchboards etc. have to wait until the tables are set up properly.

The general rule is to put like data in 1 table, so I generally would put all people in 1 table. I would then use a field to distinguish the people by their role (i.e. offender or guard). Others would go with 2 separate tables, but I'll leave that to you. I also use more descriptive field names for the primary key (pk) field in my table rather than just "ID". This will help you see how related tables are to be joined. The following table assumes that a person can only have 1 role. So if you have a situation where a guard is also an offender (or a guard becomes an offender), then the structure would not be correct. If you do have a person who has many roles, let me know I can provide the appropriate structure.

tblPeople
-pkPeopleID primary key, autonumber
-fkRoleID foreign key to tblRole
-FirstName
-Lastame
-DoB
-fkCompanyID foreign key to tblCompany (will be used for guards but left blank for offenders unless you record where they are employed)

tblCompany
-pkCompanyID primary key, autonumber

tblRole (2 records at present: Guard, Offender)
-pkRoleID primary key, autonumber
-txtRole

It sounds like you can have many ID numbers associated with a person (i.e. PID and SID for offenders and SgNumber:security guard's licence number). This describes a one-to-many relationship

tblPeopleNumbers
-pkPeopleNoID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-IDNo
-fkNumberTypeID foreign key to tblNumberTypes

tblNumberTypes (table will hold 3 records: SID, PID, License)
-pkNumberTypeID primary key, autonumber
-txtNumberType


I do not think that the following two fields should be in the offender table (or tblPeople) since they probably relate to a particular offense the offender committed.
-FiledCharges (yes/no)
-DateOfCharges (date when the charge was filled to local authority)

Additionally, I can understand the street number and street name in the offender table as indicating the offender's address, but what is the significance of the place field? Is the offender associated with a place or are you actually refering to the place where the offense occurred?

Here im in doubt as i do not know how to work things with places and street adresses as i would like to have all the places we cover in one combobox with chance to add new places if needed but also there need to be a table with all the street names in every of that places...

From the above, it sounds like you have many streets in a place (one place to many streets relationship). Can the same street name occur in many places?

Option 1: where a street name can occur in many places
tblPlaces
-pkPlaceID primary key, autonumber
-txtPlaceName

tblStreets
-pkStreetID primary key, autonumber
-txtStreetName

tblPlaceStreets
-pkPlaceStreetID primary key, autonumber
-fkPlaceID foreign key to tblPlaces
-fkStreetID foreign key to tblStreets

Option 2: If a street name is exclusive to a place, then this simplier structure would be appropriate


tblPlaces
-pkPlaceID primary key, autonumber
-txtPlaceName

tblStreets
-pkStreetID primary key, autonumber
-txtStreetName
-fkPlaceID foreign key to tblPlaces


I assume that you would have a list of offenses, and those offenses can occur many times, so I think it would be better to have a table that holds a list of those offenses.

tblOffenses
-pkOffenseID primary key, autonumber
-txtOffenseName
-fkOffenseTypeID foreign key to tblOffenseTypes


tblOffenseTypes (I assume this would include types such as misdemeanor, felony etc.)
-pkOffenseTypeID primary key, autonumber
-txtOffenseTypeName

Maybe I watch too many cop shows, but generally there is an incident and during that incident many offenses may occur involving many people. I think it would be better to capture the info with that premise in mind. Since you are more familiar with your particular application, you may not see it the same way. You'll have to make the call

Now you need a table that describes a particular incident at which an offense or offenses can occurred. (BTW, The words "Date" and "Time" are reserved words in Access, so it would be better to have different field names.) You might also consider using 1 field and record both date and time in the same field (I'll leave that to you).

tblIncidents
-pkIncidentID primary key, autonumber
-dteIncidentEvent (date/time field)
-streetNumber
-fkStreetID foreign key to tblStreets if Option 2 from above is used OR fkkPlaceStreetID is Option 1 is used

During an incident many people (offenders & guards) will be involved (one to many relationship)

tblIncidentPeople
-pkIncidentPeopleID primary key, autonumber
-fkIncidentID foreign key to tblIncidents
-fkPeopleID foreign key to tblPeople


Each offender involved in the incident can commit many offenses (one-to-many relationship) and each would have to be evaluated as to whether or not to file charges

tblIncidentPeopleOffenses (you may opt for a different table name)
-pkIncPeoOffensesID primary key, autonumber
-fkIncidentPeopleID foreign key to tblIncidentPeople
-fkOffenseID foreign key to tblOffenses
-FiledCharges (yes/no)
-DateOfCharges (date when the charge was filled to local authority)

The guards involved in an incident would also be included in tblIncidentPeople, but they would not have any records in tblIncidentPeopleOffenses. Do you have a requirement that a particular guard must be associated with a particular offender & the particular offense that was committed or will just having a guard associated with the incident be sufficient? Your response might impact the structure I proposed.

[ClownKiller]

.....BIG for your effort on writing all of these, im still in phase trying to figure out how did u made these structure of DB and how this would be related and i think im always making wrong approach when trying to create DB in access as im used to create appz from design view and then working on objects on that designed form and connecting them with code and other. That's why im always making mistakes in access.

..... I will try to make that king of DB tables and see how will these work .... as for the streets, more places can have same names so the opt 1 would be better as u described .....

I believe that u see the connection on how u divided my data in tables but im not sure that i see things that way

Anyway, im very thankful to this whole tought of yours and im gonna try it that way and when i set it up i will see what next and post here example of that DB ....

**Offenses - one offender can in my need do only ONE offense and guard cannot be offender in no way
**Place - is the place where offender live and where offense has occured .... that's why i need place in the offender table as well as street adress for him as well as place and street on the place event happend

[jzwp11]

Offenses - one offender can in my need do only ONE offense

This should simplify the design a little.

I made a few too many assumptions, but I hope you can follow my logic and adapt it to your application.

Please post back with any questions.

[ClownKiller]

*jzwp11 did you think about following tables in relationship as under attachments .... or my relations are not set right or i do not understand a thing about all that tables .... even worse i see no way to form an entry form for that kind of multiple tables ..... lost case

[jzwp11]

I received your PM and reviewed your database and the relationships appear to be set up correctly with 1 exception. You can link the fkPlaceStreetID in tblPeople to the pkPlaceStreetID in tblPlaceStreet. I made that adjustment in the attached DB.