Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suggestions on structure of MOAD? (Access 2000)

    MOAD - Mother of all Databases...
    Ok, here's my dilemma:

    I'm a Marine, charged with keeping a database of all Marines in my unit (roughly 1200). That's easy. Now take those Marines, divide them into 5 companies who all need to edit and store various information that I at one time or another will need access to. I have roughly 50 extra columns of information (various dates, addresses, duty statuses, etc.) that also needs to be archived....entered once the Marine checks into my unit, and changed throughout the year. The five companies (as I am hoping for) should each have their own table, importing that info into a 'master table'. The way we do it now is that I have one table storing ALL that info, and the 5 companies filter for their assigned Marines using a query prompting [Enter Company Code] (A,B,C,H, or W). I'm just looking to play around with Access, hopefully making our database a little more efficient and organized. One other thing to keep in consideration is that sometimes Marines change companies...and I don't want to allow the companies to add or delete NAMES, only info (after Rank, Last Name, First Name, SSNs, etc).

    This is as best as I can phrase my request with short time, sorry it's so sloppy. And thanks in advance for your advice!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    I would stick to one central table for all Companies - if a Marine changes to another Company, you only have to change the Company field in his record, instead of physically transfering his record to another table.

    I suspect that some of those 50 extra columns might be split off into subordinate tables, but without knowing the structure it is impossible to give specific advice.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    What do you mean by knowing the structure? Right now it's about as basic as possible, with very few variables, except of course dates and addresses. As of now, we just use queries to pick the info we want to update. On a slightly related note, is there a way to import data from an excel document into access tables linking by SSN? or even importing the excel document into access as a table and then moving the data?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    1. You wrote that you have 50 extra columns of information. That is quite a lot. I tend to keep the number of fields in a table small. But again, I do not know what these fields are used for.

    2. You can import data from Excel (File | Get External Data | Import...) or link to a table in Excel (File | Get External Data | Import Tables...). You can use action queries (update queries, append queries) to transfer information from the imported or linked Excel table to other tables.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    The fields are for personal information pertaining to each individual Marine, (blood type, addresses, etc....). I understand that having multiple tables is possible, but if I'm using queries to filter the info, do you suggest I still make separate tables? Is there anything negative about having so many columns?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    In itself, there is no objection to having many fields. One would have to analyze the table to see if there is repetitive information that might be split off.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    I found one significant phrase you used in your description: "...needs to be archived". To me, this may be the most important phrase you wrote, because it may control the entire design of the database. Exactly what do you mean by "archived" and what information needs to be archived? When I hear the word "archived", I assume it means that you want to capture essentially a snapshot of the information at a point in time. Therefore each time information is to be changed, you aren't really changing the information that currently exists in the database, rather you are creating a new record (such that each record is datestamped so you know when it was effective).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    Well, maybe not necessarily 'archived', as used in that context, but taking me to another eventual desire, I'd like the database to be backed up at least once a day without manually having to do so, because often times the other database custodians working on it (from the 5 companies) will turn off their computer without closing the program, or do some other funky stuff like trying to delete the lockfile, causing the database to 'crash', and it is unable to repair itself. If I could keep, say, a day old snapshot of the database until the next day comes...that would be perfect. I just want to be able to not worry about recreating all the changes that occur throughout the day if the last time I manually backed it up was sometime last week. Then I must try to recreate all the changes that occurred....it's truly a royal pain...thanks for your help!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    You can just use the Copy command to copy the database, if all you want to do is make a copy.

    More important, however, is your database schema. Do you have a split frontend/backend design? And if so, do all users share the same frontend or do they have their own copy?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    I do use copy....I just don't want to. I'm confused on the frontend/backend design....could you elaborate a little bit? Basically my computer is the server, with the database running of a shared drive. All 5 of them connect to my computer, pick their query and filter for their company, ensuring no one record is being modified at the same time.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    Fellow moderator <!profile=WendellB>WendellB<!/profile> has a good tutorial on splitting databases on his website: Why Split a Database?.

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    HansV,
    Thanks a lot for the web site, after reading briefly (skimming for a few seconds), I realized that splitting my database and using a front-end design seems to be exactly what I need. That will be another problem for another day....thanks again!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    The backend database contains the tables and relationships; that's all. The frontend database contains everything else: forms, queries, reports, code, etc. You create linked tables in the frontend which point to the real table in the backend. Access has a wizard for splitting databases that makes it very easy.

    Give each user a copy of the frontend to be put on their local drives. The backend is put on a shared folder on the "server".

    This dramatically reduces corruption problem, such as the type you have been experiencing.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    Mr Liquorman,
    I've been reading on the web site that HansV posted, and splitting the database is exactly what I was looking for. I'll undoubtedly be on here asking for help throughout that process. Thanks a lot
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Suggestions on structure of MOAD? (Access 2000)

    Regarding your backup needs, if the back-end (the database with all the tables) is stored on your PC, you probably should get some software that will backup files on your hard drive. A simple copy using explorer is adequate for protecting against corruption, but you should also worry about hard drive crashes and such. We've found that USB hard drives are an excellent solution these days. We also like a utility program called Second Copy that you can find out about at http://www.centered.com.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •