Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2007
    Posts
    14

    Unanswered: Desperately Need assistance with an Access DB

    trial and error.... thus far everything i have learned in the IT field was from trial / error / reading .. very little formal instruction if any at all.

    And now 15 years of being a Network Admin i have encountered my biggest challenge... Databases, and i have ran into a wall. I know nothing about DB's and would love some assistance. As it is now .. ALL of my information is done with excel.... 3 spreadsheets that are ENORMOUS and really dont do everythign that i want.

    I am trying to create a database for work that does the following

    Lists employee's and has the following fields

    ----------------
    employee ID #
    first name
    preferred name
    last name
    work email address
    work phone number
    non work email address
    non work phone number
    group
    supervisors name
    managers name
    hired date
    released date
    site location

    -And a few other fields in which i have all of the data already in a excel spreadsheet

    ------------------

    with a simple form to scroll through and view information and update / add employees
    also look people up by:
    -------------------------
    employee ID # (one)
    site (many)
    managers name (many)
    hired date (many)
    -------------------------

    and would like to have the ability to have a "issue" form that i can click issue .. look them up via the listed items above .. have that autopopulate the data that does not change (name / etc) and then be able to put in a issue report .. and rate it "positive" or "negative"

    and be able to sort through them ...

    does this make sense to anyone? does this sound reasonable and possible ?

    and most importantly is there anyone out of the kindness of there heart would be able to assist me with this .. could offer services in other areas? money? good faith?

    -Desperately needing assistance from a DB guru

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    This might not be quite what you think you are looking for, but I do know it is something that you really need to do. Follow the link in my address block titled Access Design Tips. Click on the top link on the web page and then read and STUDY that paper on normalization. It is actually a very logical thing, this normalization of data and this paper does an excellent job of explaining normalization.
    The time you spend now learning normalization will more than pay for itself within the next year at the most.
    One quick tip as you get started in building your first database. Use the CamelBackStyle of object naming. Rather than using an underscore or space, just capitalize the first letter of each word in the name. Keep the names short, but stay away from abbreviations whenever possible. Standardize your object names. For example, "tbl" at the start of all your table names, "qry" at the start of all your queries, "frm" for forms, "rpt" for reports, and "bas" for Basic modules. I don't use any prefixes with my field names, but some people do. The biggest need is to find a naming system that works, AND STICK TO IT.
    Good Luck!

  3. #3
    Join Date
    Aug 2007
    Posts
    14
    i appreciate the reply and the information .. im going to take a look at your site when i get to work. hopefully this will shed some light on the "idea" of databases =)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would absolutely backup Vic's assertion re being consistent re naming conventions. I would not get too hung up what convention to use. To give a bit of balance, I actually do everything exactly the opposite to Vic. The point is though we both do different things consistently and consistency is the key.

    I'll need to read Vic's link some time. In case you can't get enough this is an excellent summary of relational database design:
    http://www.tonymarston.net/php-mysql...se-design.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I agree with blindman: Listen to the Flump. The Flump speaks Wisdom.

  6. #6
    Join Date
    Aug 2007
    Posts
    14
    i read threw those and i understand the concept ... and have created a table with all of my data on each employee... now i am running into the issue.. in a form how do i set up a "search" field in the header so it auto populates the information below and alows me to modify it if needed.

    I tried a query setup and im not exactly sure how to make these function


    Babysteps =)

    and i REALLY appreciate everyones assistance / great knowledge

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Specc - just before we move on one thing is very important - make sure you have designed your database in its entirety (as far as this is possible) before looking at your forms. It is always tempting to feel that you have started really producing something once you have a UI to play with but this is actually a hinderence and will bite you on the ass at a later date if you have not nailed down your design. No good starting to build your house if you will need to dig up the foundations at a later date....

    Have you specified everything that you want out of this system and produced a logical design to meet these needs? I ask because a single-table-normalised-database is a very, very rare thing indeed....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Aug 2007
    Posts
    14
    well that is a great eye opening question, and to answer it ...

    I have input all of the data needed in this table. to create the form that i want to view it

    in addition to the Main form however i would like to have "tabs" that access information in other tables (issues being 1 table and Reviews being another table)

    so should i try to create the other tables first? i know what i want.. i do not know how to make it work or link them properly with a "many to many" relationship .. i imagine that is it based on many people out of the employee table can have many different issue reports that will go into the issue database or many reviews that will go into the reviews database

  9. #9
    Join Date
    Aug 2007
    Posts
    14
    i would like to have .. in conjuction with the "employee's" table .. timestamped issues / notes / reviews tables that i can tie together .. so when on the main form i look up a individual employee... the initial screen will show me:

    -all of there work related information on the main form(which is in the employees table)
    -outside of work contact information in another tab (which is in the employee table)
    -notes on another tab (needs a seperate table as there could be MANY notes for MANY different people)
    -issues on another tab (needs a seperate table as there could be MANY issues for MANY different people)
    -reviews on another tab (needs a seperate table as there could be MANY reviews for MANY different people)

    as i have read ... notes / issues / reviews would have to be seperate forms that are just "linked" inside the main form on the tabs

    guidance is needed =)

  10. #10
    Join Date
    Feb 2007
    Posts
    15

    Question

    Hello Specc,

    Have you already created your tables in Access?
    Are you moving on to create forms for input/output data?
    At this point what kind of help you need?

    OS

  11. #11
    Join Date
    Aug 2007
    Posts
    14
    Have you already created your tables in Access? 1 table .. The main employee database

    Are you moving on to create forms for input/output data? yes

    At this point what kind of help you need? help creating other tables to link data and creating the forms

  12. #12
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Specc
    In your original post, you said:
    -------------------------
    employee ID # (one)
    site (many)
    managers name (many)
    hired date (many)
    -------------------------
    I don't know about the others, but this confused me. I bet this will be asked shortly, but can we get a list of your tables, and fields, before you go jumping into forms. A few weeks ago I had to delete a few weeks worth of work because I didn't set my tables up correctly.
    Ryan
    My Blog

  13. #13
    Join Date
    Feb 2007
    Posts
    15
    Ok Specc,

    Let's make this clear. A database is a file where you store tables, queries, forms, reports, macros and modules.

    The thing you called main employee it's a table, and a table is where you store your data. A table is made up of fields and records. Every single column is a field, i.e a piece of information regarding an employer like is name, is address, etc., every single row it's a record i.e, the whole information about that employer.

    Now, like you did with your main employee you should create other tables regarding other subjects. For instance, you have a table to store data about your employers, probably (or not) you would like to store the department name where employer work. You don't need to write for every single record the name of the department, just create a table of departments wiht a department_id field and in the employeers table create an equal field department_id, then relate them in the relationship board. After that you could define the field department_id on employer table to look for values at the department table.

    I hope this was clear to you, if not just call back.

    OS

  14. #14
    Join Date
    Aug 2007
    Posts
    14
    Table Name: tblEmployees
    Fields in "tblEmployees"

    FormalName
    PreferredName
    LastName
    Shift
    Role
    Group
    ComboTrained
    Manager
    Supervisor
    Site
    StartDate
    EndDate
    Domain
    TimeZone
    W2KID
    RacfID
    EmployeeID
    EmailAddress
    SeatLoc
    VoipLogin
    VoipID

    thats the table currently for all employees (this is a excel spreadysheet as it is .. with well over 200 employee's listed) so im hoping everyone can see why i would like to convert this to a database

    i would also like to set up a form that reads this information and "makes it all pretty" and easy to input data to. Tabbed out in Main info / contact info / site info

    in another spreadsheet i have employee ID / Name / Issue Date / Followup Date / Memo .. where i put in issues

    I would like to have

    Table Name: tblIssues
    Fields in "tblIssues"

    EmployeeID
    IssueDate
    FollowUpDate
    Issue

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by osilvaware
    Now, like you did with your main employee you should create other tables regarding other subjects. For instance, you have a table to store data about your employers, probably (or not) you would like to store the department name where employer work. You don't need to write for every single record the name of the department, just create a table of departments wiht a department_id field and in the employeers table create an equal field department_id, then relate them in the relationship board. After that you could define the field department_id on employer table to look for values at the department table.
    That's a matter of opinion. This is basically the old surrogate vs natural keys debate. I fall on the side of natural keys personally. You are correct though - a department is (most likely) best considered an entity in itself. Whether or not you use the department name or some id as the value to be used in the foreign key of the employee table is a design decision.

    Specc - going back to Ryan's point - when you say many sites, many managers do you mean that an employee may be assigned to many (2+) sites, an employee may have many managers (2+)? Where I work (and have worked) this was true for both counts. This has an implication for the design of your database.

    You really need to consider relational design & normalisation very carefully especially when converting from something like Excel. A spreadsheet and a relation (the formal name for a table in a relational database) may look similar but they are actually very different and the rules determining their structure are likewise very different.

    I would still strongly recommend that you formalise your database structure before you move on to forms. Your comment about notes, issues, reviews etc is proof that you need more tables. Ryan has mentioned his own experience of having to start again due starting his interface before finalising his database design. Ideally you don't want to learn this lesson the hard way. This is where foregoing a thorough specification analysis (the ground work) appears attractive but is far from a short cut.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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