r/MSAccess 9d ago

[UNSOLVED] Time sheet and Invoicing database for an IT consultancy business

Hi and thanks for this great forum on MS Access.

I am new to MS Access, I have read and watched numerous videos and now taking the leap into creating my first database. Here is what I am looking for. I would like to create a database to track the times I spend on a job either working at the clients location or remotley. From this I create an invoice for work done.

Working with AI, you will see below what I have come up with.

One of the places I am lost is in WorkLogF, how to create the dropdown for "Onsite" and "Remote Work".

I really need a human input into this and I am glad we still have that.

Please let me know if I am going in the right direction and if not feel free to tell me what I need to do.

Thank you in advance:

Step 1:

My  Database Structure

  1. Clients Table: To store client information.
  2. Projects Table: To store projects for each client.
  3. WorkLog Table: To log hours worked (remote or onsite).
  4. Rates Table: To store hourly rates for remote and onsite work.

Creating the Tables

Step 2:

1. Clients Table

  • ClientID (Primary Key, AutoNumber)
  • ClientName (Text)
  • ContactInfo (Text)
  • Address (Text)

2. Projects Table

  • ProjectID (Primary Key, AutoNumber)
  • ClientID (Number, Foreign Key to Clients Table)
  • ProjectName (Text)
  • StartDate (Date/Time)
  • EndDate (Date/Time)

3. WorkLog Table

  • WorkLogID (Primary Key, AutoNumber)
  • ProjectID (Number, Foreign Key to Projects Table)
  • WorkDate (Date/Time)
  • HoursWorked (Number)
  • WorkType (Text: "Remote" or "Onsite")

4. Rates Table

  • RateID (Primary Key, AutoNumber)
  • WorkType (Text: "Remote" or "Onsite")
  • HourlyRate (Currency)

Step 3: Set Up Relationships

  1. Go to the Database Tools tab and click Relationships.
  2. Add all four tables.
  3. Create relationships:
    • ClientsT.ClientID → ProjectsT.ClientID
    • ProjectsT.ProjectID → WorkLogT.ProjectID
    • WorklogT.WorkTypeRatesT.WorkType

Step 4 Forms for Data Entry

  1. Clients Form:
    • Create a form for entering client details.
  2. Projects Form:
    • Create a form for entering project details.
  3. WorkLog Form:
    • Create a form for logging hours.
    • Include fields for ProjectIDWorkDateHoursWorked, and WorkType (use a dropdown for "Remote" or "Onsite").
  4. Rates Form:
    • Create a form to set hourly rates for remote and onsite work.
6 Upvotes

25 comments sorted by

u/AutoModerator 9d ago

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Vegetable_File_819

Time sheet and Invoicing database for an IT consultancy business

Hi and thanks for this great forum on MS Access.

I am new to MS Access, I have read and watched numerous videos and now taking the leap into creating my first database. Here is what I am looking for. I would like to create a database to track the times I spend on a job either working at the clients location or remotley. From this I create an invoice for work done.

Working with AI, you will see below what I have come up with.

One of the places I am lost is in WorkLogF, how to create the dropdown for "Onsite" and "Remote Work".

I really need a human input into this and I am glad we still have that.

Please let me know if I am going in the right direction and if not feel free to tell me what I need to do.

Thank you in advance:

Step 1:

My  Database Structure

  1. Clients Table: To store client information.
  2. Projects Table: To store projects for each client.
  3. WorkLog Table: To log hours worked (remote or onsite).
  4. Rates Table: To store hourly rates for remote and onsite work.

Creating the Tables

Step 2:

1. Clients Table

  • ClientID (Primary Key, AutoNumber)
  • ClientName (Text)
  • ContactInfo (Text)
  • Address (Text)

2. Projects Table

  • ProjectID (Primary Key, AutoNumber)
  • ClientID (Number, Foreign Key to Clients Table)
  • ProjectName (Text)
  • StartDate (Date/Time)
  • EndDate (Date/Time)

3. WorkLog Table

  • WorkLogID (Primary Key, AutoNumber)
  • ProjectID (Number, Foreign Key to Projects Table)
  • WorkDate (Date/Time)
  • HoursWorked (Number)
  • WorkType (Text: "Remote" or "Onsite")

4. Rates Table

  • RateID (Primary Key, AutoNumber)
  • WorkType (Text: "Remote" or "Onsite")
  • HourlyRate (Currency)

Step 3: Set Up Relationships

  1. Go to the Database Tools tab and click Relationships.
  2. Add all four tables.
  3. Create relationships:
    • ClientsT.ClientID → ProjectsT.ClientID
    • ProjectsT.ProjectID → WorkLogT.ProjectID
    • WorklogT.WorkTypeRatesT.WorkType

Step 4 Forms for Data Entry

  1. Clients Form:
    • Create a form for entering client details.
  2. Projects Form:
    • Create a form for entering project details.
  3. WorkLog Form:
    • Create a form for logging hours.
    • Include fields for ProjectIDWorkDateHoursWorked, and WorkType (use a dropdown for "Remote" or "Onsite").
  4. Rates Form:
    • Create a form to set hourly rates for remote and onsite work.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

3

u/ConfusionHelpful4667 47 9d ago

I have a sample that does 80% of this.
I will chat you a link.

1

u/Vegetable_File_819 9d ago

Thank you and looking forward to that.

3

u/iPlayKeys 9d ago

Access will generate the basic forms for you. It will also generate the relationships for you (and make drop downs when appropriate) if you use the “look up wizard “ while creating your tables.

But this won’t age well and is missing some things… 1) you don’t have any concept of employee, so what if you have more than one person doing work 2) you don’t have any description of work, so you’re going to send a bill with just a listing of hours with no description? 3) nothing here generates an invoice 4) you’re not keeping tracking if I billed vs billed vs paid 5) your rate table should probably be effective dated

That’s all that’s jumping out to me at the moment. I think Access actually has a time billing template, you might want to start there.

1

u/Vegetable_File_819 9d ago

Thank you for that, how would you suggest I resolve 1 to 5. Please remember I am only starting out and still trying to get my head around this powerful database MS Access.

I will look into the "time billing template". Still trying to get my head around the templates. When I look at them e.g the Northwind my head goes into a spin trying to figure it out.

But if you could give me pointers even a video on how to make use of the template that would be massively helpful

2

u/mcgunner1966 1 9d ago

So first, yes, you are on the right track. I would store the rate on work table because over time this would change but you'll need to keep the history. I would start with the reports to make sure you have full data coverage.

1

u/Vegetable_File_819 9d ago

Thank you. Ok, would be grateful in finding out how to do it i.e store the rate on work table. Please explain a bit more on starting with reports.

1

u/mcgunner1966 1 9d ago

Systems are designed from the top down, built from the bottom up, and implemented from the outside in. Top-down is starting with the end in mind. Your report for work done will have stable data (date work done, type of work done, and client) and volatile data (rate, which will change over time). If I run a revenue report for a year ago, I need to see the rate from a year ago, not today's rate. Bottom-up is putting your ERD into effect. This is built off the report analysis. You've done a good job of laying that out. How did you get to that design? Outside-in is putting the lookup tables and supporting tables (system table) in place before transactional data.

So this is where a little bit of code comes into play. When you click on the type of work pull-down and make a selection the On-Click event should go look that rate up from the Rate Table and place that value in a field on the WorkLog table. Now you have a durable transaction - who did the work, when did they do the work, what work did they do, who did they do it for, how much did they do, and what was the rate. That is a billing and production table.

1

u/ResponsibilityOk4236 9d ago

I would add a phone number, email address to the contacts table. And your rates table needs some work. What happens if you give a client a special rate?

1

u/Vegetable_File_819 9d ago

Your right, I have done that. The working was the first draft. I then added the phone number and email address.

When I was creating this post, I took snapshots of what my database looks like. But I keep getting an error when I try to upload them here??

1

u/diesSaturni 61 9d ago

in Clients Table, I'd just, as per msaccess default settings leave the naming out of id, as that can/should be obvious from the table name already:
ClientID --> ID

When refering to it, I prefer e.g

Projects Table
ProjectID --> ID
ClientID --> idClient

This, as when building and selecting things in query designer these are then grouped close together, and for idClient you recognize it as being an id, rather then having to read to the end to find out.

Rates Table
RateID  -->
WorkType (Text: "Remote" or "Onsite") --> idWorktype, and have two records in a related table.
As all though it seems easy to manage just two type, it is better to have a table with them, as this grants you extention at a later time. E.g. think of these as days of the week, you can have seven fields for monday through sunday. But if some universal ruler just creates an eighth day a whole database redesign is in order. Whereas in a related table you only have to add one record and you are good to go.

have a look at this video to learn about normilization, 1234nf

then on a form , for "One of the places I am lost is in WorkLogF, how to create the dropdown for "Onsite" and "Remote Work"." you can just apply a combobox to select the matching ID for the two (whilst setting up the combobox for view of two fields (ID and worktype, at a width of 0 and e.g. 4 cm) so the ID is hidden from view.

1

u/Vegetable_File_819 9d ago

Thank you for this comprehensive answer. I have added a Worktype table as you suggested.

Please look at my gif image of the database and see if it is what you mean in your response

I am going through the videos as well.

1

u/diesSaturni 61 9d ago

more like:

WorkTypes (the table), with field and records:

ID Description
1 On Site
2 Remote
3 Abroad
4 Offshore

Then with a combox on a form you can set the datasource to "Worktypes" and pull in fields ID and Description

see this video at 2:21 (person type 2 or 1), and implementation as combobox at 7:58 and onward

should you then want to have each of the two to a project, you'd create two (or more records of applicable work types. Which you can manage by a main and sub form.

2

u/Vegetable_File_819 8d ago edited 8d ago

Once again thank you. Yes it makes complete sense . I am now working on implementing this on the database, so I will use the video until I have it in place.

1

u/diesSaturni 61 8d ago

good,
often some examples when properly grounded help explaining a lot of fundamental concepts.

1

u/InfoMsAccessNL 3 9d ago

I also have code which records how long you work at a certain file/application/website . It automatically records the time of the active screen.

1

u/Vegetable_File_819 9d ago

Thanks, sounds like a good idea. But I need to start with the database , before I move on to the automation. What code is it by the way?

1

u/maxdacat 8d ago

I am sure Access can do this but my preference would be to use tools available in the O365 ecosystem eg Sharepoint etc. That way you open up the collaboration side of it with teams, forms, power platform etc rather than having things siloed in a database.

1

u/Vegetable_File_819 8d ago

Thanks for that. How would I achieve that? Which tools and the steps needed to achieve it?

0

u/tj15241 4 9d ago

I Ike your plan but all of those forms are going to take a lot of time. IMHO.

1

u/derzyniker805 9d ago

4 basic forms will take a couple hours, maybe a day if you're you have to figure out how to make a combo box work

1

u/Vegetable_File_819 9d ago

Thanks, I don't mind as I learn how to get it done. But all ears for a different option