[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Access Form selecting from multiple tables, adding detail via input fields and writing to separate transactional table

I am trying to create a timesheet entry tool for a consulting business and need to create an input form for the capture of daily entries by consultants.  Attached is the data structure, but in summary the following tables should be the sources for combo boxes: 10_Customers;  12_Projects; 13_Workorders; 21_Consultants; 31_TimeSheetDays

The form should allow the user to select the consultant name from 21_Consultants then the Customer from 10_Customers, the Project from 12_Projects (only those linked to the selected Customer), the work-order from 13_Workorders (only those linked to the selected Project), and finally the week ending date.  They would then have a grid to select the day of the week from 31_TimeSheetDays and then enter start/finish times along with comments on the work completed - there could be multiple entries per day in the grid.

On completion they would select an update button that would save the selected values from the input tables along with the time/day details to a a table called 32_TimesheetDetail. Other linked tables with fee structures could then be used to generate invoices. Spec-for-changes-to-Timesheet-to.xlsx
0
RoreConsulting
Asked:
RoreConsulting
1 Solution
 
peter57rCommented:
I can't find a question in your post.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access is certainly up to that task. There are several templates you could use to get started - check on the MS Office site:

http://office.microsoft.com/en-us/templates/CT010152732.aspx?av=ZAC

In fact, there is a Time Tracking utility: http://office.microsoft.com/en-us/templates/CT010375245.aspx?av=ZAC#ai:TC101898281|

That would be a good one to review to get some ideas. Once you get the basics in place, you can post back here with specific issues you need help with.

I would encourage you to review your table strucutres a bit more carefully. For example, your 1000_WorkOrder Detail table contains details much more relevant to the Project (like Project.StartDate and Project.EndDate). When determining what data to include in a table, remember that a specific table should deal with a specific Object/Entity, and should only contain attributes that are relevant to that specific object/entity. As an example, if you had a database storing information about Cars and their Drivers, it wouldn't make any sense to store the color of the Car in the Drivers table, and it would also not make much sense to store the Name of the Driver in the Cars table.

Same concepts apply with your 4010_Timesheet Elements table. As an example there, you're storing the ProjectName value in that table. You should instead "relate" the Projects table to the TimeSheet table by storing the PrimaryKey value of the Project in the Timesheet table. You then don't need to store the ProjectName, StartDate, EndDate, etc in the Timesheet table, since you can then easily get that data through a Join or Subquery.

0
 
RoreConsultingAuthor Commented:
I'll go through the examples and then post some more specific questions regarding how to create the form(s) to accomplish this task - that remains the key challenge.

LSMC, thank you for your guidance.  I will review the table structures again, although perhaps I created some confusion by including screenshots of the queries that pull the data together (eg the examples you noted are queries rather than tables) - I was hoping these would help create visibility of the intended data relationships
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now