Cross tab type data entry form

[Ron in NYC]

I'm designing a tool for staff to enter hours worked on a project by dates. I want the data to go into a flat file, and I'll do cross tabs for analysis. But the users are accustomed to doing this in a spreadsheet with Dates across the top, and projects down the rows. I'd like to design a form that works like that. Suggestions?

 

[plog]

In order of preference:

1. Tell the users they are getting a new format.

2. Keep using Excel for data entry and create an import process to bring that data in.

3. Hire someone to do this in Access.

4. Spend at least 2 months learning a bunch of VBA/SQL to come close to what you want in Access. Spend another year learning more VBA/SQL to work out all the bugs.

Your low post count and the fact that you posed an open ended question lead me to believe that doing this is not easily within your grasp. I may be wrong, but it sounds like you don't even know where to start. I do know where to start and still wouldn't want to undertake this.

What you want will take more than just an intermediate skill level in Access. This involves unbound forms, dynamic input creation, data validation, building INSERT and UPDATE functions to read and write from the tables. Its just a lot of work.

 

[Ron in NYC]

I couldn't get my head around it but I ran across a posting where someone said they did it.

I moved historical data into Access by first "unpivoting" it in Excel. That worked surprisingly smoothly.

Thanks,

 

[Ron in NYC]

And you're right this is outside of my skill level (never let that stop me, though). I'm a finance professional and I use ACCESS one of several tools. I'm not a developer.

 

[plog]

I couldn't get my head around it but I ran across a posting where someone said they did it.

I saw a few posts like that too. Understood the concept, but also understood the work involved to make their solution actually function as intended. I don't think its worth it.

I really don't think #1 (new input format) is too far a reach for users. Instead of horizontally, you work vertically. What does your times sheet table look like:

TimeSheet
ts_ID, autonumber, primarky key
ID_Project, number, foreign key to Projects table
ID_Employee, number, foreign key to Employees
ts_WorkDate, date, date work occured on
ts_Hours, number, number of hours worked

You could have a main menu where users select who they are (ID_Employee) and click a button, that opens a continous form. Each row of that continous form has a drop down for the Project (ID_Project), an input for the date (ts_Date) and an input for the number of hours worked (ts_Hours). They fill that row in, a new blank one appears where they can log more time. You could even do some VBA trickery to default the next record to the same Project, but with the date set to the day after what they just input.