Beginners Question

[drunkcrusher78]

Ok, to make this simple for me to explain, this is what I'm trying to do:

Im a police officer, and I am trying to make a database to hold all my arrest information. This is what I have set up:

FORMS
frmInitial
frmSuspect
frmVehicle
frmArrestAfd
frmQuestionaire
frmSFST

TABLES
tblInitial
tblSuspect
tblVehicle
tblArrestAfd
tblQuestionaire
tblSFST

Each table has a primary key of "Case Number" which is unique to each record. My first problem I am having is setting up the relationships to link all these tables together by "Case Number"

My problem is a simple one (I think). All I want to be able to do, is fill out multiple forms that send the information to one record. Example, my Main form is filled, then my suspect, then vehicle, etc. and I want all that information sent to the same record in the database. The problem Im having right now is when I open one of my other forms, (Not the main form) it wants to create a new record. I would like to have all these extra forms, when they open, to be on the same record that the main form is on. Therefor all of my information from all the forms go to a single record.

After everything is set up, I export the records to OmniForm, for printing. The information that I store in Access is printed on Forms created in Omniform. As long as each record has all the information that I need, it tranfers into Omniform without a glitch.

I sure hope I am not confusing everyone. I am a beginner at Access, and have basic self-taught programming knowledge.

Any guideance on this topic would be greatly appreciated!
Thanks
Oren Haydel

 

[Len Boorman]

Basically if you want all the data sent to the same record then you only want one form.

I think that you really need to consider what you want to do.

For example

Access could handle a Person with
Multiple arrests
Multiple addresses
Multiple vehicles
Multiple allias's
etc

but is this what you want to do ?.

If you do then you need to consider carefully the scope of what you want to achieve. You do not really want to rewrite the national crime analysis system.

If you want a simple arrest, who, when, why then keep it simple and use one table that contains all the data.



len

 

[drunkcrusher78]

The reason I cannot use one form and one table, is because I have more than 255 fields. I have many checkboxes and fillable fields, therefore I need more than one table. The example above does not reflect what I am trying to do. Each arrest is basically one record.

 

[Len Boorman]

Okay

I am not at all certain that what you are trying to do really follows "relational database methodologies" . Going back to your original post the problem you are having linking tables.
What type of primary key are you using. I believe that you need to determine one table as the lead table and then link all others to this in a "star" like pattern.

Data from each form goes into each table linked by the Case Number. I believe that you will have some difficulties retrieving data from this format because duplications will be generated by queries.

It is not the normal practice to construct a database in this manner.

An example.

A case has a person and a car involved.
The car details would be in a separate table. Primary key probably registration. There is now a link from Case to Car via the registration. All car details are kept in the Car table and the link is not by Case. It would then be possible to check if a car is involved in more than one case.

If you want a pure data repository then maybe the multi table "Star" pattern will do what you want to do but it is not really a relational database.

len

 

[Parker]

The reason I cannot use one form and one table, is because I have more than 255 fields. I have many checkboxes and fillable fields, therefore I need more than one table. The example above does not reflect what I am trying to do. Each arrest is basically one record.

And there in lies your second problem!

I suspect, knowing more than a little about our boys in blue, that you are using Omni forms to replicate a standard Police form system yes?

If this is so then that is going to be a major problem. But it can be worked around with a little patience.

Next and probably the most valid questions at this stage are:

1. Do you intend to use this only on a personal basis?
2. Do you intend to let colleagues within your force use it
3. Are you designing a system for nationwide use?

No mater what the answer to the above questions you are going to need a little time and patience. What Len said is true, the way you posted your question leads one to believe that this is not suited to an RDB such as Access but I think I know what you are trying to do and I do know some of the problems involved with the systems that you have to use within the force. So, while my experience of Access is limited, I may be able to guide you along the right lines.

 

[Len Boorman]

Okay

Parker
You have the "Boys in blue" and "OmniPage" experience mixed with some Access

I would not classify myself as an expert in Access but will contribute as much as I can.


Drunkcrusher
As you can see there are some fundamental situations to be resolved. I do not believe this is going to be quick or painless

Len

Okay

 

[drunkcrusher78]

This database I am trying to create is only for my personal use.

I think Im making progress. I set up my tables, and linked them all together by Case Number. Now whenever I pull up one of my "Sub Forms" it displays the records. All I have to do is use the Last Record navagational button to bring me to the current record I was working on on the main form. It fills, and then I close the form and it saves the data. The only problem Im having right now, is I was playing with filters and macros earlier, and the rest of my forms are still trying to set those filters and not letting me see the records correctly. Anyway, one of my forms is working correctly, so I just need to duplicate that with the rest. I can e-mail my database to someone if they wanted to look at what I'm doing.

Thanks

 

[Len Boorman]

Controls here at work will not let me download. See how you get on and if necessary I will give you an email address

Len