If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
About my Database
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 |
#2
|
|||
|
|||
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" wrote: 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 |
#3
|
|||
|
|||
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 "Robert" wrote: 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 |
#4
|
|||
|
|||
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 "Robert" wrote in message oups.com... 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 |
#5
|
|||
|
|||
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 m l wrote: 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 |
#6
|
|||
|
|||
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... ------------------------------------------------------------------------------------------------------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. "Robert" wrote in message ups.com... 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 m l wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
setting up a multi user database in access 97 database | Edward Letendre | General Discussion | 1 | March 31st, 2005 03:10 AM |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
MS Access unable create MDE Database....please help | Ismail baba | General Discussion | 1 | November 17th, 2004 05:38 PM |
cannot change password | Richard | General Discussion | 13 | November 14th, 2004 10:00 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |