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

Posted on 2011-10-31
Last Modified: 2012-06-27
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
Question by:RoreConsulting
    LVL 77

    Expert Comment

    I can't find a question in your post.
    LVL 84

    Accepted Solution

    Access is certainly up to that task. There are several templates you could use to get started - check on the MS Office site:

    In fact, there is a Time Tracking utility:|

    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.


    Author Comment

    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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now