Results 1 to 4 of 4
  1. #1
    kerdo32 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    1

    Question: how best to store/structure this data?

    Hi all! First time poster here. I've built up a tool that my office is using, but want to build in a function to calculate how many hours over time that people have worked. I would like to have a form that will allow the user to pick a span of months (eg. Jan 2019 - May 2019) to see how many hours a given employee worked in that period of time. How should I structure this data? (There are currently 200 employees, and the number changes regularly).

    I have a table called [Employees] that is currently structured with the following fields: [Employee Name], [Employee Email], [Hours worked Jan 2019], [Hours worked Feb 2019], etc. I realize that this is stupid, but I'm not sure how best to structure this data so that I can easily pull the numbers into a form. Ideally, the user would be able to select Jan 2019 - May 2019 and select the employee Bob Smith to see how many hours he worked, compared to the average hours that other employees worked.

    Any guidance would be greatly appreciated. While I've done so much on this project already, I'm kind of confused at this point and I know that it would be a useful feature to have.

    Thank you in advance!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Welcome to the forum.
    Not sure about your table design or how you would determine if any overtime had been worked.
    I have attached a file which may give you some ideas.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by kerdo32 View Post
    I have a table called [Employees] that is currently structured with the following fields: [Employee Name], [Employee Email], [Hours worked Jan 2019], [Hours worked Feb 2019], etc. I realize that this is stupid, <snip>

    Any guidance would be greatly appreciated. While I've done so much on this project already, I'm kind of confused at this point and I know that it would be a useful feature to have.

    I would not say "stupid", I would say "un-informed"..... You need to read about Normalization.
    So let me ask you " What do you do when "Jan 2020" rolls around? You would have to modify the table to add the new fields for 2020. then you would have to modify the queries AND the forms AND the reports AND any code written. A lot of work just to add another month.

    Bob Fitz has shown you a better structure. Also note that you should not use spaces, special characters or punctuation in object names.

    Your current design is so common that it has a name "Committing spreadsheet". That is because the table is designed like a spreadsheet - typically short and wide.
    In a RDBMS, the tables are typically tall and narrow. (In my first two dBs I "Committed spreadsheet")

    See this for links to Normalization:

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Following on with the good advice you have been given, I suggest you step back from Access(physical database) and
    create some requirements that you can share/review with others.
    A description in 4-5 lines of simple English outlining the business problem/opportunity for which this proposed database is being considered.
    A list of facts or rules that apply to your business. All employees could/do work overtime? Your business is in one location or several?
    Some sample outputs (rough drafts) of some reports that are being envisioned.
    This will give you some sort of plan and help you focus on your business need rather than the syntax of MS Access. The syntax and database concept issues will come soon enough. Now is the time to focus on WHAT the business needs/wants.
    The links in my signature are to articles that you may find helpful.
    Good luck with your project and learning.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-01-2014, 05:06 PM
  2. Question on how to structure a database
    By ExterminatorJeff in forum Database Design
    Replies: 3
    Last Post: 03-17-2012, 06:34 AM
  3. General Access Structure Question
    By caw442000 in forum Access
    Replies: 6
    Last Post: 10-13-2011, 06:49 PM
  4. Structure/Relationship Question? Please Help...
    By Imgsolutions in forum Access
    Replies: 2
    Last Post: 06-27-2010, 09:52 PM
  5. An import question above the table structure
    By Shag84 in forum Import/Export Data
    Replies: 2
    Last Post: 08-20-2009, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums