About my Database

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hello Again Everyone

As mentioned earlier, I would probabally be posting a great deal. Here
is the overview of my database and a subsequent problem I am having.

Initially, there was one HUGE table, now I have several tables, these
are named as follows:

These are tables that are referenced by autolookup fields

LstBldTyp
LstClnc
LstGndr
LstKeyBilletO
LstRankList
LstSwimClass
LstWorkSection
LstWpnsQual

These are tables that contain data

TblG3Account
TblG3Basic
TblG3Clnc
TblG3Med
TblG3NoK
TblG3NonResPME
TblG3OIF0406TrngALL
TblG3OIF0406TrngSel
TblG3Promo
TblG3Recall
TblG3ResPME
TblG3Skills
TblG3Training
TblG3Uniform
TblG3Vehicle
TblG3Weapons

I use the TblG3Basic as (what I consider) my master table with the
basic information. I am using a MbrSSN field (Social Security Number)
as the master key and as my reference for all relationships on a one to
one basis.

When I open my TblG3Basic, I do not see any of the sub datasheets,
Trying to figure out why that is. Also as I said, I use my TblG3Basic
as the starting point, I am trying to figure out how to get it so that
when I enter a social security number in, it cascades through all of my
other tables (creates a new record I guess). I have referential
integrity and cascade update and delete, but for some reason I still
have to enter the SSN's manually...

Any help would be appreciated, if you need more information, please let
me know!!

Robert A. Wukich, Sr
Sgt/USMC
 
I don't have an anser to your specific question.
However, I feel very uneasy whenever I hear of anyone storing SSNs in a
database, especiually a database as unsecure as Microsoft Access. As you may
be aware, identity theft is one of fastest growing areas of crime. Unless you
absolutely must store social security numbers, I would avoid it. Try to come
up with some type of employee number (this is now mandated in many cases by
the Government, although probably not the military as they seem to be
excepted from almost all laws). If you must store social security numbers,
try to keep the rest of the personal information (especially the persons name
and address) elsewhere and not easily associated with the SSN. It would not
hurt to encrypt the SSNs either.
 
Robert,
You cannot do this through the tables themselves. You will need to set up
forms for your data entry. If you start with a main form based off your main
table, then the next form can be linked by your key field to add data to the
Many side of your One to Many relationship. Tables are just for storing the
data. Use forms and queries to create the correct format for entering that
data.

Hope this helps.
Jackie
 
Hi Robert!
It looks to me like you might have a structural problem - I'm not sure that
all your tables are
built properly. Do you need all those tables? I agree that there was no way
that you could do anything
with one big table but could you not manage with the Basics table and one
other table for all the details?
Without all the information it's a bit hard to see the whole picture but
here's where I'd start:
- make the Basics table the Master with key on SSN
- make the other tables Detail-tables with key on SSN + id field
- define all relationships on SSN in each detail table and SSN in master
(it's like Orders and Order_details in the Microsoft Northwind database)
- then you can create a Details form with subforms for all the other tables
so that when you enter SSN in the master, it will be entered into the
details,
again like Order# in the Orders form

Anyway, hope this helps to get you started.
Good luck!

Mike L
I have a free tutorial on Access that you might want to check out.
It's at www.profsr.com Profsr.com Tutorials
 
Hello

I believe it is a structural problem, but I cannot find where... When
you state master and detail-tables would I start with the Basic table
as my anchor and in table view establish subdatasheets based on the
other tables? If so, how do I get the SSN's to populate the other
datasheets so that I may update the information...

Here is what happens now:

1. I have a form specifically for adding new personnel, it all goes to
the tblg3basic Table.
2. I save the data, and close it
3. I have a form based off of a query to search for names, I do this so
that I can ensure that the data made its way into the basic table. From
there I have command buttons that open other forms and when I do that,
the forms do not display the information.
4. The way around this right now is for me to plug in the SSN manually
in each table and only than can I update this information...

I hope this helps you to help me!!

Thanks
Robert A. Wukich, Sr
Sgt/USMC
 
Please read my responses to your earilier posts, where I talk about how to
help us help you (Introduction, Programming Automation)

Note the following:

Tables -- store data (they are not for data entry)
Relationships -- tie tables together using primary and foreign keys
Forms -- Display data, add data, edit data, and sometimes (with caution)
delete data
Queries -- Table like in that they select data to display in forms or
reports.
Reports -- Print out data or in preview mode can display data
Macros -- code like things that allow those of us that are not programmers
to do some really neat (dangerous) things.
Modules -- this is where the VBA code lives.

Did I mention tables are not for data entry?

sub-forms are a powerful friend.
-------------------------------------------------------------------------------------------------------snip
I save the data, and close it
3. I have a form based off of a query to search for names, I do this so
that I can ensure that the data made its way into the basic table. From
there I have command buttons that open other forms and when I do that,
the forms do not display the information.
4. The way around this right now is for me to plug in the SSN manually
in each table and only than can I update this information...
Click to expand...
------------------------------------------------------------------------------------------------------snip

the 'bell ringer' is: "close it".

To get the 'Cascade' of key fields you are wanting you have to use forms and
sub-forms.

For those on the list that don't speak 'military'

LstBldTyp ????
LstClnc ????
LstGndr gender
LstKeyBilletO key billet authorized
LstRankList list of pay grades Enlisted-1 to
Enlisted-9 and Officer-1 to Officer-10 (or their equivelent)
LstSwimClass Swiming qualification level
LstWorkSection Work Section
LstWpnsQual Level of Weapons Qualification

TblG3Account not a clue
TblG3Basic SSN(Key), FirstName, LastName, other stuff
TblG3Clnc not a clue
TblG3Med something to do with medical ?????
TblG3NoK 1:Many --> Next of Kin (each member can have
several Next of Kin listed on their emergency data form (e.g. Wife, mother,
father, brother)
TblG3NonResPME Non Reserve PME (??? I have not a
clue)(NAC)
TblG3OIF0406TrngALL not a clue
TblG3OIF0406TrngSel not a clue
TblG3Promo 1:Many --> Promotions each member is
promoted (demoted, some marines 'play rough on nights out' ;>,) several
times :: sample fields PromotionID, SSN, date, NewPaygrade, Notes.
TblG3Recall 1:Many --> (Maybe) Each Marine could be
recalled on several occasions and each recall could have unique data
elements attached.
TblG3ResPME not a clue
TblG3Skills 1:Many --> (even if Marines ;>, each
may have more than one skill)
TblG3Training
TblG3Uniform 1:Many --> each member may be (issued)
several different uniforms. May be paid for uniforms more than once. May be
assigned a different Uniform allowance at different times.
TblG3Vehicle
TblG3Weapons 1:Many --> each member may be qualified in many
weapons:: sample fields WeaponQualID, SSN, date, (LstWpnsQualID, from
LstWpnsQual)


So, In general your tables are 'almost correct' (assuming you have a 1:many
relationship in the tables I cannot decipher)
You want to use forms and sub-forms to enter data (Link parent-child fields)
You probably want to use a tabbed control to contain the various sub-forms.

That is about all the damage this old submariner can do with the information
provided.

Ed Warren.
 
Back
Top