Daily Population Database Help... please.

[amish]

Good Morning,

I have visited this forum many times for help, but this is the first time I had to post, so I just registered today. I have found the information that everyone has posted here to be invaluable. So thank you up front for anything anyone can offer.

I am designing an Access 2003 database to track my facilities Daily Population. This database should be able to show the Current Population with releases from that day as well as being able to show previous days population when requested.

So far, I have two tables listed below:
1.
tblPopSheetAdmissionInformation
strAdminNumber (Admission Number)
strAdmissionReason (Reason for Admission)
strUnit (Where the resident will reside)
strFirstName
strLastName
dtmDOB (Date of Birth)
strPO (Probation Officer)
dtmAdmissionDate
dtmAdmissionTime
strGender

2.
tblReleaseInformation
strAdminNumber (Admission Number)
ysnReleased (Yes/No to indicate release)
strDestination (Where resident is released)
dtmDateReleased
dtmTimeReleased

The tables will have a 1-1 relationship using strAdminNumber.

I figure I will create 1 form which has all of the above information on it. Which will make it easier for the user to enter the information. I am still consider splitting it between two forms.... not sure yet.

I will create a query that will calculate current residents, I will also create one that will allow a date to be entered to show past residents (hopefully)

I am guessing I will need to make a "make table query" to help me keep track of current residents.

Right now, I am in the planning stages, and am looking for suggestions on the best way to accomplish my task. I have listed below what I need to do:

What this database needs to do:
Show current residents with current releases
Show past residents for any given day with those days releases.

If anyone can offer any guidance, or thoughts I would really appreciate it. I feel comfortable doing this myself, but I wanted input first before I do a lot of work only to find that I did it completely wrong.

Thank you all for your help and guidance!

Tom

 

[amish]

I am working on this, and am having trouble showing all data once the Release Yes/No check box is marked yes.

It now skips over those that have nothing checked and only shows those that are checked on the form.

 

[Fifty2One]

Do you have Yes as a criteria for the Release field in the query that is feeding data to the form?

 

[amish]

For the fields that are showing the yes is there. I am trying to figure out why it is only showing those that have a yes and not all of them right now. Currently, I am just testing some stuff and created a form using the two tables.

 

[voidcranium]

Is your form based on a query? Sounds like your form is only pulling those records where 'YES' is checked, which would be how you do it with a query.

 

[The_Doc_Man]

The way I see it, you are going to run into a normalization problem. Therefore, my first advice will be to Google-search or Yahoo-search articles on "Database Normalization." You can try Access Help and the Wikipedia.ORG articles on that topic, too. Limit your reading to hits on college or university sites whose names you recognize, probably all in the .EDU domain.

Once you understand that, you will understand some suggestions I'm going to make.

I will also create one that will allow a date to be entered to show past residents

Because of this, I'm going to suggest that you need to split your tables.

tblPopSheetAdmissionInformation
strAdminNumber (Admission Number)
strAdmissionReason (Reason for Admission)
strUnit (Where the resident will reside)
strFirstName
strLastName
dtmDOB (Date of Birth)
strPO (Probation Officer)
dtmAdmissionDate
dtmAdmissionTime
strGender

This should be

tblPerson
PersID (autonumber), FirstName, LastName, DOB, Gender

tblAdmission
AdmitID (autonumber), PersID (foreign key), AdmitDTM (date/time in one field), AdmitRsn (see below), ProbOfcr (see below), more to come...

For the PersID, AdmitRsn, and ProbOfcr, these can be looked up via either a list box or combo box to appropriate tables. If you want to store a code for the AdmitRsn, a combo box is in order and should be driven from a table having the code number and a verbal explanation of that reason. The probation officer should be a lookup to a table of probation officers. I put it in the Admission table rather than in the Person table because it seems that assigned probation officers might change from one visit to the next (over a long series of visits).

So these ideas suggest two more tables - a reason table and a probation officer table.

Your idea about the release table is probably not right. This should be part of the Admission table. Instead of

strAdminNumber (Admission Number)
ysnReleased (Yes/No to indicate release)
strDestination (Where resident is released)
dtmDateReleased
dtmTimeReleased

I would do this to the admissions table:

tblAdmission
AdmitID (autonumber), PersID (foreign key), AdmitDTM (date/time in one field), AdmitRsn, ProbOfcr, Released (Y/N), Destination, ReleaseDTM (date/time in one field).

Then populate this from a form based on the admission table as the primary. When you have a new person, have a separate form to add that person's information for the first time, or have a pop-up form capture it if there is no such person on record (yet). Then your admission form can do a drop-down to find the person in question. Look up "Cascading Combo Boxes" in this forum to see how to select the person's last name, first name, and DOB as cascading selection critera. Or you can just have the combo box show last name, first name, and DOB as 3 columns and just select the one you wanted.

You can build a relationship such as person:admission::1:many for the main lookup. The AdmitRsn and ProbOfcr would be xxxx:admission::1:many as well. DON'T declare the field types to be Lookup. Instead, just build the relationship and let the form and combo-box wizards sort it out for you.

You would have TWO forms. One for Admit (where the Release flag is always force-defined as NO) and one for Release (where you search only for Release Flag NO cases, perhaps via query, and when you finish, it is force-defined as YES) and you select which person from a drop-down list. There is a combo-box wizard you can use that will let you jump to the record you select from the drop-down, so this should be pretty user-friendly, too.

The only thing I cannot advise you on is the release destination because I can't tell how complex that can be. If it is encoded to a list of values, though, you can treat it like your AdmitRsn field. If it is a real address, you have to decide how much you want to keep and how you want to keep it.