Fairly new to Acces - need help with a basic Table

[jmccown]

I have used Excel extensively, but have decided that I have a few spreadsheets that would be better applied in Access. I am trying to create a collaborative document for my Team at work for tracking hours worked in various categories by day. Trying to keep a very similar format. I cant picture how to convert my current spreadsheet into an Access table. I am attaching in hopes that somebody can show me what I am sure is the simple solution.

Thank You all!!McCown - 2018 - Weekly Report.zip

[ssanfu]

You have 2 worksheets that look/are identical?

Is there one worksheet for one employee/team member?

How many employees/team members?

Does every column (B - K) get filled in every day/week?

---------

I see one table for employee/team members, a table for Categories (the text in row 1 columns B through K) and a table for tracking time.

---------

Maybe you could/would explain more about the process: write a description 4-5 lines (or more) in plain English (as you would tell an 8 yr old) of the "business involved". No jargon and no database quasi-terms. Bottom line -- get a clear statement of WHAT you are trying to automate. Once that is clear, then consider the options that may exist for HOW to do that automation using Access.

---------
Just curious: On sheet 2, A1 there is text:

"Week of the Month: Reporting periods run from Sunday to Saturday. Enter in box above: Week 1, Week 2 or simply 1,2,3. Example the week of March 3-9 is "Week 10"

What/Where is the "box above"??



---------

To help you, I would advise you to work through the tutorials at Roger's Access Library (http://www.rogersaccesslibrary.com/forum/forum46.html).
Don't just read - take the time to actually do them. Shouldn't take more that a couple of hours....

[ranman256]

your table would be like:
PERSONID, DATE, HRS, PROJECTID

then you can show the results similar to your table shown.

[jmccown]

Great questions Steve - Thank You. I paired down the original attachment to just the basics. If I need to reattach the entire workbook I can. On the full workbook, there are actually 52 sheets at the bottom, one for each week as this is a weekly timesheet if you will. There is a date box at the top that auto-fills in the week number which corresponds to each tab ie... January 1 returns a week 1, Jan 8 returns a week 2 and so on. There is one of these workbooks for each of the 11 associates.

Columns B-K are the header rows. The empty cells below are where the associate would log the hours for the category described in the header row. There are also totals that occur at the bottom of each column and to the far right to show the total hours logged for each day.

I hope that makes sense and helps you picture what I am trying to achieve within Access now.

Thanks again!

[jmccown]

I responded to Steve above with some clarification and a complete attachment. Im just not sure how to set up the table to gain the data in the format that i need it to read out.

Thanks for your response and assistance!

[ssanfu]

Im just not sure how to set up the table to gain the data in the format that i need it to read out.

You have to remember how data is entered and stored is/can be different than how it is displayed.
If how data is displayed (read out) is a concern, how do you need to display (read out) the data? (this is part of the analysis/design of the dB)
If you have a good table design structure, it is easier to massage the data to be displayed how you want it. (grouped, summarized, totalized)

Is security a concern? ie Is it OK for Tom to see Jane's time entries?

How often are the entries entered? Every day? Once a week?



Maybe start with (pardon my scratchings):


The category table could be modified to have 2 description fields:
one for a short description (examples: Market Focus, Project Work, Inspections/Steton, etc)
and the other would have the rest of the description. (examples: Specific team focus on regional and/or district development (i.e. "Fix Florida"), As assigned by Senior Leadership, etc)
The category table could also have items like Vacation, Sick Leave, PTO.

[jmccown]

Steve, again great help - Thank You! Let me answer your new questions below;

As it relates to the output report or display, I will create a query and/or report that handles that. That should be an easy way for sub-totals in both directions. The lower half of that spreadsheet, I will not be incorporating, so the costs and descriptions/comment will not be needed.

Security or privacy will not be an issue as I will be creating individual forms for them to input their data. That being said, they should not be able to see each other's data.

These reports or data entry will typically be input weekly, but some record on a daily basis.

My real challenge is how to create the Tables. From what you drew out (much appreciated), it looks like you are suggesting to split into 3 separate tables and creating relationships. This is critical and where I am really struggling. I am going through numerous tutorials and forums trying to gain a better understanding of proper structuring.

Thanks again for your help and expertise!

john

[ssanfu]

Security or privacy will not be an issue as I will be creating individual forms for them to input their data. That being said, they should not be able to see each other's data.

From you response, I would say security/privacy IS a concern. In stead of creating an input form for each associate, there should be 1 input form; when the person logs in, only their data would be available. This is a frequent question in the forum - search on "log in" and/or "password".

Here are some examples:
http://www.rogersaccesslibrary.com/forum/topic220.html UserSeesOnlyHisRecords.mdb

http://www.accessmvp.com/arvin/ Look for "OnlyMyRecords.zip"

http://www.rogersaccesslibrary.com/forum/topic104.html Form_LogIn.mdb

[jmccown]

I look forward to learning about those options. Thanks again, and i will let you know if I have luck with the relationships that you provided.

another aspect I am working on is a collaboration of these docs via SharePoint on mobile devices - any advice there would be quite helpful.

Thanks again Steve, I will keep you posted.

[jmccown]

Steve, on a single day/date, the associate could be working in several different categories and spending various time within each category. How do I make the table have a single TimeTrackerID show the various entry times and activities in the same row?in the same row?

[ssanfu]

You woouldn't/shouldn't.

Using the spreadsheet (2), tab 5 from Post #1, if there was only one record (row in Excel), you would need to have the AssocFK, the date and 10 fields each of CatagoryFK and WorkHours... a total of 23 fields (this includes the PK field). This type of design even has a name: it is known as "Committing Spreadsheet". Spreadsheets are typically short and wide; dB tables are tall and narrow.

If you use the normalized design you only need 5 fields.... and none of the fields are empty. In the design above (23 fields). Looking at Monday, there would be 12 fields empty (6 category and 6 hours fields).
If you looked at Friday, there would be 16 empty fields.
And what to do about Vacation time? Do you have fields for Vacation?
In the spreadsheet design, what would you do if you decided to add PTO? You would have to add 2 more fields, then modify the queries and the forms and the reports and the code. A lot to do just to add one category.


Could you make a form that LOOKS like the spreadsheet? Sure.... but the form would (must) be unbound. Then you would have/need to write code to add, update, delete data/records.
Lots of work, but I did just that for a winery in Wisconsin (IIRC). There was around 100 unbound controls to deal with.


Did I answer your question?

[jmccown]

There is no need for a vacation or PTO field. This is not to track time for pay purposes, it is intended to be able to answer the question "what has your team done this week/month/quarter/year. The overall purpose of completing this task in Access vs Excel is to make data entry more streamlined for the user via a single entry form and reporting easier for my upper-level managers utilizing pre-designed reports that would be linked to the dates desired.

There would likely never be more than 5 work categories on any given day (if that many) but would need to be able to allot time per category for those completed during the day. Also, I believe that I spoke of this before, but the tabs at the bottom are 1 for each week of the year. My hope is, that this would not be necessary rolling forward as a simple report could be designed for the time frames listed above.

Steve, your expertise, and help here are greatly appreciated. I hope that what I am trying to accomplish is somehow possible.

Thanks,
John

[ssanfu]

So I was playing around and threw this together.
Open it and select John.
You can show by week by setting a filter in the sub form.
Haven't made any queries or reports to show totals....... just a form for data entry.

[jmccown]

Steve, that is awesome - Thank You. I will study all aspects of that. It is close to what I need. I will keep you posted. By the way... Anchorage... Beautiful place!