Results 1 to 14 of 14
  1. #1
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7

    Access database to track work

    Hey guys, I am a total noob in access, only made a handful of things back in the day. I was wondering if anyone knows how to create, or has a template for this:

    - I have a startup company with a few employees
    - Every employee is payed based on the amount of work they do, so for now they each have their own excel sheet that they fill in. We do photo and video editing, so they place in the address and the number of photos / videos they edited for that specific address.
    - Each of their excel sheets has different clients in spread sheets
    - At the end of each day I go trough their excel sheets and copy the stuff into a main excel sheet.



    Now I was wondering if that can be made easier by access:

    - The employees each have their own access database where they put in what they finished during the day
    - I have a central database that collects the data from their individual access files (it can be stored on the local server so we have everything grouped)
    - Bonus would be if I could export individual client sheets so I can deliver it to them if they ask during the month, and at the end of the month for invoices...

    Let me know if this is too much to ask?

    EDIT: I am willing to pay a fee to someone who can make this.. Providing it is not something ridiculous.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the database should be split:
    1 backend with only tables on a server, and
    1 of the Frontend program with everything else (queries/forms/etc) that is given to every user.

    you may want to stop having user enter data into Excel, and start entering it in Access form directly into the DB.
    but you can have the users import the excel data: In Access form, click a button, select the XL file, it imports the time data.

    but build a Form to enter data directly.

  3. #3
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Yea I would replace the excel completely this way. As long as the users can overview their work, since now in excel it automatically calculates how much they have made so far.

    It automatically calcualtes in the main excel sheet as well for me.

    Would you be willing to give this a go? And how much would it cost for something like this to be done? It would really make our life a whole lot easier.

    Let me know.

    edit 1: here is the sample of how it looks at the moment...

    Click image for larger version. 

Name:	Sample page.JPG 
Views:	63 
Size:	178.9 KB 
ID:	37593
    Click image for larger version. 

Name:	Overview.jpg 
Views:	63 
Size:	143.3 KB 
ID:	37594

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you take this on yourself I think you will really need to understand entity/attribute relationships and normalization. The typical Excel user has one major impediment when shifting to Access, and that is a spreadsheet paradigm (oft referred to as an Excel brain). It looks like you have operations (or maybe jobs) arranged in columns. If you come up with another operation, in Excel you add a column. If you have to do that it Access (it's called a field) it is a sure sign of design problems - lack of normalization. Access is easy to do poorly. I have some links I can post if you're interested.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum......

    I'm in agreement with Micron. You really need an understanding of entity/attribute relationships and normalization. It would help to work through some tutorials.


    Looking at the images you posted, I'm guessing (actually Google Translate) these words might be in Slovenian:
    - Ime I Prezine = Name and Surname
    - Mjesec = Month
    - Dnevna = Daily

    Would you be willing to post your Excel workbooks with a few records?

    It looks like the colored tabs at the bottom of the first image are initials of the employees?
    In the 2nd image, there is
    EM P
    Ammount Price
    Ammount is the number of photos edited?
    Price (Euro) is what the employee gets paid? Or the charge to the client?

    Is "specific address" the same as "Client"?

    Does every employee get paid the same amount for editing photos?

    Any significance in the letter above "Price"? It is a "P", "G" or "R".



    Right now I've drawn out 4 tables. Might need 1 or 2 more tables.

  6. #6
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Hey, thanks for the interest! You and Micron,

    We are from Bosnia, so yea those are the name and surname of the user, month is well month..
    You are correct the two letters are initials from the customer.
    Ammount is indeed the number of photos or videos edited for that specific project.
    Address is basically the project name, or the house address we are editing the photos for, that is provided by the client in the order name. So that changes constantly and is never the same.
    Every employee gets payed the same, but there are two different excels, one with employee pricing and the other one that only I can see that is on the server and has actual prices that are charged from the client.
    Those P R G letters are just for my personal information.. I have several means of getting payed, and those letters mark the three means of payment so I know how much money goes where. the R is the biggest and most of the stuff goes there, there is also P that is a lot smaller and G that does not happen that often at all but I have it.

    Attached you can find an excel with samples, prices have been changed.
    Sample - Admin.zipSample Editor.zip

    Here is more detail about the whole thing:
    - User end where they have overview of the things they already finished, with separated clients.
    - Users can modify their database since sometimes they might enter something wrong and need to correct it.
    - Users have a fixed price for each product, but the clients prices might vary, so not all clients pay the same for the same product it depends on what I negotiated.
    - Admin end where I have overview of total ammounts for all clients.
    - I need to be able to add new clients, add new products and determine separate prices for users and for clients (since I charge one price and have another price that goes to users)
    - It needs to be separated per month. So what ever is finished one month needs to be archived and a new month is fresh.


    - It would also be great if I would be able to one click export client excels with their invoice to designated folders...
    For example, we have client 1 and client 2... They all have their individual spreads in database that tracks their amount. Now, I know that I need to export client 1 excel to folder C://clients/client 1, and I know that I need to export client 2 excel to c://clients/client 2. Would it be possible to make a one click export and export all excels into their corresponding folders (overwriting older versions)? If you understand what I mean. They would have our design of the invoice, together with individual amounts of things that are edited and list of things that is finished so far for them...


    Like I said, I think I'm way over my head for something like this in access..

    Quote Originally Posted by ssanfu View Post
    Welcome to the forum......

    I'm in agreement with Micron. You really need an understanding of entity/attribute relationships and normalization. It would help to work through some tutorials.


    Looking at the images you posted, I'm guessing (actually Google Translate) these words might be in Slovenian:
    - Ime I Prezine = Name and Surname
    - Mjesec = Month
    - Dnevna = Daily

    Would you be willing to post your Excel workbooks with a few records?

    It looks like the colored tabs at the bottom of the first image are initials of the employees?
    In the 2nd image, there is
    EM P
    Ammount Price
    Ammount is the number of photos edited?
    Price (Euro) is what the employee gets paid? Or the charge to the client?

    Is "specific address" the same as "Client"?

    Does every employee get paid the same amount for editing photos?

    Any significance in the letter above "Price"? It is a "P", "G" or "R".



    Right now I've drawn out 4 tables. Might need 1 or 2 more tables.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe this would be a start...
    an = autonumber, pk = primary key, fk = foreign key

    tblCustomer
    CustID; FName; LName; Address1; etc (an PK; text; text; text)

    tblProject
    ProjID; CustID_fk; ProjDesc (an PK; long fk custID; text)

    tblProjItems
    ProjItemsID; ProjID_fk; ItemType; ItemCount (an PK; long fk ProjID)

    tblEmployee
    EmplID; FName; LName; Specialty?? (an PK; text; text; text?)

    tblEmpProjects
    many to many table: 1 person can work on many projects and many persons could work on one project - given that a project might need simultaneous or specialized workers

    tblProjCost
    ProjCostID - only attributes related to cost. 1 field for your rate and 1 for employee rate. You can include both fields in any form, but hide your rate from all other eyes.

    It needs to be separated per month. So what ever is finished one month needs to be archived and a new month is fresh.
    I don't understand that. What needs to be separated? You'd put a date field somewhere, but I can't tell where. I have no allowance for applying daily or hourly bookings of time since the payments seem to be task based, not time based.

    It would also be great if I would be able to one click export client excels with their invoice to designated folders..
    Why? Isn't the goal to eliminate Excel? What would you need it for that can't be done in Access?

  8. #8
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Yea, one person can do for example video and another person can do photos for the same address.. It all depends on what each person does.

    Quote Originally Posted by Micron View Post
    I don't understand that. What needs to be separated? You'd put a date field somewhere, but I can't tell where. I have no allowance for applying daily or hourly bookings of time since the payments seem to be task based, not time based.
    That means each month starts fresh.. We need to keep track each month separately so I can invoice the clients on per month basis.

    Quote Originally Posted by Micron View Post
    Why? Isn't the goal to eliminate Excel? What would you need it for that can't be done in Access?
    I do need an option to export to excel or PDF does not matter, as long as I can send it to the client as an invoice. I can not send them an access database and say welp, there you go

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That means each month starts fresh.
    OK, but that doesn't address what the units are. Daily? Hourly? Task? Completed Job?
    I have no allowance for applying daily or hourly bookings of time since the payments seem to be task based, not time based.

  10. #10
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Quote Originally Posted by Micron View Post
    OK, but that doesn't address what the units are. Daily? Hourly? Task? Completed Job?

    Ah sorry for misunderstanding. Yea payment is measured by work done and not by time. So it is not important at what exact time any of them was done, just the day, so we know what was done during a month and we can make a payment based on what was done during a certain month.

    How we usually do it now is make one excel and fill it out, with a blank fresh excel next month so we keep track on what is done what month.. And I know how much to pay the employee and how much to charge the client based on months.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by .anon View Post
    Yea payment is measured by work done and not by time.
    So if it takes me 3 days to edit a video, I would only get paid for 1 item/job?
    Do you record the starting date or the ending date?

  12. #12
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    No need, it is strictly per project basis.. those are short videoa done within an hour. So timestamp is not important. They just enter it under the date it is finished. That is it.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then I guess you'd need tblProjPayments as a junction table between projects and employees for project payment details - much like tblEmpProjects is a junction between projects and employees. This assumes there is a many to many relationship; i.e. many payments can be made to one or many projects

    tblProjPayments
    ProjPayID
    ProjIDfk
    PayMth <<your date field

  14. #14
    .anon is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    7
    Quote Originally Posted by Micron View Post
    Then I guess you'd need tblProjPayments as a junction table between projects and employees for project payment details - much like tblEmpProjects is a junction between projects and employees. This assumes there is a many to many relationship; i.e. many payments can be made to one or many projects

    tblProjPayments
    ProjPayID
    ProjIDfk
    PayMth <<your date field

    Yea since I basically understood 30% of that, I guess I will have to wait to get some more time and than dig into this thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 09-09-2017, 09:38 AM
  2. Keeping track of work hours
    By UT227 in forum Database Design
    Replies: 2
    Last Post: 10-17-2016, 08:06 AM
  3. Replies: 1
    Last Post: 11-15-2014, 03:23 PM
  4. Replies: 2
    Last Post: 05-10-2013, 07:10 AM
  5. Track users use of Access database
    By booyeeka in forum Programming
    Replies: 1
    Last Post: 02-26-2009, 02:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums