Enter Daily time in Form

[Lorlai]

I have been tasked with taking a daily budget and automating the process as much as possible. There is a forecasting mechanism for figuring out how many hours will be worked in advance (with time off for PTO, holidays, etc.). The client would like an access form that can take user input in a datasheet view and store the value in the database for use in other forms/reports. The problem is as I understand it, access can only hold 256 columns. Is there a way to meet this expectation?



For example, the user would like to enter hours in the table below (user input colored red):

Employee	1/1 Mon	1/2 Tue	1/3 Wed	1/4 Thur	1/5 Fri	1/6 Sat	1/7 Sun
Bobbie McGee	8	8	8	8	8	0	0
JoJo McJoe	0	8	8	8	8	0	0
CeCe	0	0	0	8	8	0	0

*50+ employees
Is there a way to create a form/table for this data? This forecast will be done constantly, and for an unknown amount of time in the future. The client would also like the data to remain for review, so I can't delete anything. I know very very little about forms, and am in a bit of a rut.

Any help is greatly appreciated!

[orange]

Not sure where this fits access can only hold 256 columns.
But a Normalized table would rarely, if ever, approach this limit.

My suspicion is that you are quite proficient in Excel. I think you should research Normalization.

[Lorlai]

Hi Orange,

Thank you for your response. I have taken some entry level database courses and do understand the idea of normalization, but do not have the skill sets yet to manipulate data (or client requirements) to make such changes to the db structure. Forgive me, I am still learning!

Given my strict client requirements of wanting a datasheet view to enter daily time in the above format, is there a way to normalize the date data? Or a work-around to ensure the form looks like a datasheet so the user can enter the daily time in their desired format?

Thank you for your help!

[orange]

Your post isn't helping to clarify the situation, for me anyway.

You review your requirements and create Tables. You work through your table attributes to see if there are additional/hidden Tables. Use Normalization to confirm your table designs.

The user interface, the means by which a user interacts with your database, is a different thing.
Whether you have red and green screens with text boxes, combos with special back color is really not relevant to the database structure.
The structure you showed in post #1 may mean something to you and the user but it isn't a relational table structure.
It may be a report, it looks like some interface you may have in Excel.
If it is meant to be an input form for getting data into Access, I think it could require some vba (code with logic) to get the appropriate data for the appropriate person for the appropriate date into the underlying tables.

Here is a tutorial that may be useful to you. Work through the example from business facts right through to database. Repeat as necessary to understand/familiarize with the concepts, then try with your own data.
http://www.rogersaccesslibrary.com/T...lationship.zip

User interface is a separate "subject" than database table structures and relationships. Consider the database tables and relaionships as the "foundation", and the user interface as "the paint and siding/curb appeal". All the paint in the world won't fix a bad foundation.

For additional info (free videos) see post # 10 at https://www.accessforums.net/databas...ces-29892.html

[Lorlai]

Thank you again Orange, I know this must be frustrating if you have a strong understanding of databases. I appreciate your patience and guidance. The links you posted have been helpful. If I may use your knowledge again, I have an idea for getting attempting to normalize my data. Ignoring the user interface, I need to store daily time entry. Would creating monthly tables, and storing the time values within each month be a better way to go?

For example, I would have one table [January 2013], with columns of Monday 1/01, Tuesday 1/02, Wednesday 1/03 etc. and another table of [February 2013] with columns of Tuesday 2/01, Wednesday 2/02, etc.

I hope that I am being more clear, and again, I appreciate the guidance you have provided so far!

[orange]

I would not start by having a table for each month.
Can you be very, very specific in WHAT you are trying to do?
I see Employees and Dates (Are these WorkDays, Calendar Days??)
Are the numbers ActualHoursWorked? If it's a forecasting model are these ForecastedHoursWorked?
Why do some Employees have 0 Hours?
Do you record actuals against forecasted???

I think you will get more focused responses and assistance if you get into the "business facts/rules" that you are dealing with.

There are some business facts at
http://www.databaseanswers.org/data_...eets/facts.htm

that relate to a data model at
http://www.databaseanswers.org/data_...eets/index.htm

Good luck.

[Lorlai]

Thank you again Orange. I believe this question will be better placed in the design section of the forum, and will be posting a new question there. You're help has been greatly appreciated!