search multiple tables

[soulpiercing]

I am developing a large database for the army legal offices. I have separate tables for each type of action that we do but now I need to be able to query all tables at once to search by name or SSN to check for prior cases on people.

I am stuck and I am sure it is a simple fix.

Thanks

 

[Jack Cowley]

It sounds to me like your data is not normalized. The types of actions should be one table (a lookup) with another table that is related to the individuals and this table would list the actions that applied to that individual. Now you have one table to find the SSN and it will produce a list of actions for the selected SSN.

tblIndividuals
SSN (PK)
LastName
FirstName
Rank
SN
...other data...

tlbActions
ActionsID (PK)
Description

tblIndividualActions
IndividualActionsID (PK)
SSN (FK)
ActionsID (FK)

You can use autonumber instead of SSN for the PK if you like and add the SSN as a field.

hth,
Jack

 

[soulpiercing]

problem

The problem I have with using one mass table for all actions is that each major type of action has various options and details that are not relevant to others. or Example, separations has a total of about 15 types of chapters and I track recommendations to discharges as well as circumstances for the separation as well as discharge type, approval dates, etc. Courts Martial I track charges, confinement, hearing dates, level of court, investigation times and results, and court findings/sentence.

I am looking into a cross tab query that I could automate to post query results in a 'lookup' table with only the simple data. Any ideas?

 

[Jack Cowley]

Gotcha! My only suggestion at this time is to be sure that all of your tables are normalized. If they meet 3rd Normal Form you are good to go and displaying your data in a report should not be too difficult. I just get a bit concerned when someone has to get data from many different tables to get the answers they need, but this is your database and I am sure you know what you are doing. I'm sorry that I don't have any other suggestions for you...

Good luck!

Jack

 

[raskew]

Jason-

Jack's solution, modified to meet your needs, is the way to go! You need just one table for your active duty population. SSN is the key identifier.

Take a look at the Access StudentsAndClasses database template. This is very close to the model you should be following, with Classes being replaced with LegalActions (and a lookup table that spells-out the various actions you deal with).

raskew
SGM, USA (Ret.)

 

[Jack Cowley]

Thanks for the support Bob! And isn't at a coincidence that I was just looking at the Students and Class sample db just a couple of days a go. Great minds? Nah....

Jack
PFC USA (ret.)
SFC USN (ret.)

 

[neileg]

I have no quibble with the previous answers because they are correct!

However, one quick way out of your dilemma is to create a union query that pulls the relevant fields from each table into one homogeneous dataset. Then search on this.

But normalising the database is still the right way to go.

 

[soulpiercing]

new attachment - searching multiple tables

I posted the new .zip file of my DB after fixing a lot of issues now I am getting some new errors....

Could you look at it if you get a chance and provide ANY input.

http://jag_clad.tripod.com/

or the file directly: http://jag_clad.tripod.com/clad.zip

Thanks

Everyone here on Access World is AMAZING.