Database Assistance Needed

[eolson30]

Hi everyone, Im looking for some assistance. I work in a field that houses prison Inmates. i have been tasked to design a database to help organize all info for each Inmate. I am very new to access, but i have toyed around with some sample databases, and I think i am getting the hang of it. I know just messing with the sample databases wont make me the best at it, but i dont have the time and money for classes. so i ask everyone out there for assistance. My company uses MS Access 2003. I have MS Access 2007 at home. I downloaded the Student Desktop Dtatbase from MS. and i love it is perfect for my company but i need to make some changes. is there anyone out there that can help me. i need the database to work for 2003, and some things removed and somethings added. pretty much keep the style os the database but our own fields, forms, subforms, reports, and different buttons. if there is anyone that can help please let me know. you can post here or email me i reply very quickly. Thank you aagain.

 

[spikepl]

I doubt that anyone would be willing to commit to such an open-ended task for free. If you want someone to do the work for you then say so, and be prepared to pay.

If you want help or inspiration when stuck with some specific issue, then just get cracking and post the issue or issues when you get there. This is what AWF is for, and sooner or later you'll get assistance.

 

[jdraw]

I agree with spikepl and I'd like to emphasize his point - if you're going to do the job with assistance from this forum, then get on with it. If you want someone to do it for you, then hire someone, or have the people who need the database pay for its development.

This statement design a database to help organize all info for each Inmate doesn't tell us much. You need some detail.

Here is some info to give you "a slight push" to get started. Now, it's up to you. We're here to answer questions; so post as questions arise.
Good luck.

There is a free data model related to prisoners and prisons at
http://www.databaseanswers.org/data_models/prisons_and_prisoners/index.htm
but it is unlikely to meet your needs. It may give you some ideas.

There is a general approach to database design ( alist of things to do) here
http://www.databaseanswers.org/approach2db_design.htm

There are free video tutorials describing database concepts on the internet.
These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

 

[eolson30]

I am willing to do the work, i have to learn somehow. here are the tables im using and the fields in each table.
tblInmate
DOC# - primary key
Last Name
First Name
Date of Birth
Age
Height
Weight
Race
Classification
Institution Job
Date of Hire
Date removed

tblWarning
Date
Offense
Disposition
CR/IR
Staff Name

tblMedical
Medical Restriction
Start Date
Date finished
Staff Name

tblProperty
Property
Date Received
Staff Name

The tables above are the tables in using. The questions I have are below.
1. What does the query do with the tables. And what is the best way to use the query
2. I need a search form. Not a search for on a form but a separate search. Examle: if I tye in the last name of an Inmate a separate pop-up form will show up with that inmates records on it.
3. The inmate details form will have 3 subforms, Warnings, Medical, Property. These subforms will have tables in them for multiple records. How can I print the main form with the subforms, using a report.
4. I would like to have a form that lists all inmates, and you can scroll though the list and when you find an inmate you can click on the DOC# and the form with the inmates details will pop up.
Sorry if I asked so many questions at once. Im using the “Students Desktop” database from the MS website, as a starting point for the database im looking to do. And I need the database in Access 2003. So I had to save it as the 2003 version. So none of the macros, or VBA are working . please if there is any thing you can help with that would be awesome.

 

[spikepl]

Your tables seem to be missing primary keys and thus relations. Did you look at any of the tutorials provided by jdraw? Do that first.

The structure of data is the alpha and omega of any sensible database. But sometimes just getting going with what you have will quickly reveal any shortcomings in your data structure.

You ask "what does the query do with the tables" - Queries are a rather fundamental element of a database, and if yoo ask this question, then I'd suggest you google some tutorials for access queries, and that might clarify things. ( I have the position that it is a waste of time to repeat information people can google themselves - othe rs may have a different view). Besides, you have downloaded a db - open it up and look what goes on. That should clarify the role of queries.

Sort all this out before getting into specific forms. Once you are there, I'd suggest that you post specific questions in the appropriate forum - the other fora are visted more often than this one.

Don't ask for "any help" on unspecified issues- that is an open-ended invitation to a quagmire, that few respond to. Be specific. Break your problems down and solve them one by one.

 

[jdraw]

Further to the discussion, I'd beware of names of fields and objects that contain spaces or special characters.

Rainlover has a tutorial on Naming conventions etc that you will benefit from. It can be found at http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[eolson30]

Thanks for the help. i have viewed lots of tutorials in the past 2 days. its a lot to take in. but i think i understand for the most part.
So i have one specific question.
I want to have a small single form with only a text field and a button, used to search. I have no idea how to code this. i want to be able to enter an ID number and click search and a form pop up with the info.

 

[jdraw]

Review the free video tutorial at
http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[eolson30]

jdraw that was very useful. Thank you. Is there any code that i can add to the one in the video that would open a specific form that would display the info i search for.

 

[Simon_MT]

If you think about this requires knowing the ID how about by Name, DOB, Age, Classification and Race as well.

I have a Search Form luauched from a Menu Form.

Search Field for Search Type
PrisonID combibox
[Search] SName
[Search] FName
[Search] Classification

AfterUpdate Prisoner_ID CombiBox

With CodeContextObject
DoCmd.OpenForm "frm_Prisoner", , , "[PrisonerID] = " & .[PrisonerID] & ""
End With

Function PrisonerSearchCriteria()
With CodeContextObject
             PrisonerSearchCriteria = "[PrisonerSurname] like '" & .[SName] & "*" & "' and [Prisoner_First_Name] like '" & .[FName] & "*" & "' and [Classification] like '" & .[Classification] & "*" & "'"
End With

OnClick Command Button

DoCmd.OpenForm "frm_Prisoner", , , PrisonerSearchCritera

Or something similar

Simon

 

[eolson30]

Thanks Simon. But where do i add the code. do i add it to a command button with a text field. Im a little confused sorry . I a noob when it comes to access and VBA.

 

[Simon_MT]

Can you send up a version 2007 - I only need two records. Change the names.

Simon

 

[eolson30]

Sorry, Do you want me to send you the database im working on. or set u a "dummy" database so you can add the code to the search form.

 

[Simon_MT]

I understand. See what I can do.

Simon

 

[Simon_MT]

This is just a simple demo

Simon

 

[eolson30]

Simon you are amazing. that test database was exactly what i needed. can you do one more thing for me. i need to add command buttons to a form. i have the buttons but not the VBA code.

"Save & New" Save data and clears form for new entry
"Save & Close" Save data and close the form
"Clear Data" this needs to clear all fields and not save anything.
"Close" close the form.

Thank you

 

[Simon_MT]

This is a personal I think it is better that users are aware that data is to be respected and not a throw away commodity. If they change something data changes. From an administrative point of view you job is to look at the quality of data and seek the exceptions. There has to be trust with your users and you can validate the input on the form.

Save should be assumed.
Clear Data is dangerous as it leads to accidently deleting information.

Simon

 

[eolson30]

I agree with you, but i need to make this idiot proof for my fellow staff. Thats why i need so many buttons.
the one button im having problems with is the clear data button.
I need the button to clear the data on the form not delete the original data. almost like i started to enter data and didnt need it. instead of going to each field and deleting the data i could use a button to delete the data .

 

[Simon_MT]

You would be better to Goto a New Record. The problem is that how do you discern between valid information and information to be discarded.

Simon

 

[eolson30]

i did the "GoToNewRecord" but it still saves the data. is there a way to add a VBA code to not save the info entered and clear the form.
Is there any websites or info i can learn VBA code for access 2003 i think thats where i getting lost