Simple Sum qn

[Davidyam]

Hi,

I am trying to create a database to clock overtime for my officers and I am new using ms access.

May I know how do I add up hours on a column base on the same name on another column on the same table and present it on another table.
e.g

Table: Overtime


Officer Overtime clock
David 5
Daniel 4
David 3

Table: Account overview
Officer Total overtime clock
David 8

Thank you very much

Best regards,
David Yam

[R_Badger]

Hi David

For what you appear want you don't actually want to store the data in a second table.

What you want is a query, something along the lines of:

SELECT DISTINCT [OFFICER], SUM([Overtime clock]) AS [Total Overtime clock] FROM [Overtime]

(I wrote it in SQL as its the easiest way to write these things, you can use the access query builder but I find it almost impossible to describe the process to do that)

It may be slightly different depending on any other fields you might have (i.e. you might want a WHERE clause on for example an OT_date field)

Just for information, as a rule of thumb:
Tables are for storing data
Queries take the stored data and manipulate it
Forms allow users to modify and view data without having full access to your tables and queries (designed for use solely on screens)
Reports allow you to get the useful data out (Designed for output to a printer)

Hopefully that should get you the results you are after.

[Davidyam]

Hi R_Badger,

Thanks for the information. Actually I need it in another table because I need to store the data in sharepoint and I can't upload query into it. Is it possible to convert the SQL that you provided and convert to a VBA code, maybe I can try to work on that and pull the data and store onto the table.

Thank You very very much.

Best Regards,
David Yam

[R_Badger]

Are you hosting the tables directly on sharepoint or exporting a table to upload?

My experience with sharepoint is limited (Although I use it the connection to our server is poor at best so I try to steer clear of it)

[Davidyam]

Hi R_Badger,

I am actually hosting the tables directly in sharepoint, and creating the link from access. My officers hope to see the raw data in sharepoint itself thus i need to create another table where by it shows the sum of the overtime clocked. I am currently using a crosstab query to update the Total OT clocked base on the status depending on how the approving officer updates it, e.g Approved, OT-pending, Rejected. However I don't know how to update my query to the table itself so that i can store it in sharepoint for my officers to see. Do you have a solution or other suggestion on what should I do..

Thank You Very much for your help.

Best Regards,
David Yam

[R_Badger]

Well the best suggestion I can make to that is having an extra table and running an append query to it, the thing is that unless you either run a delete query each time to remove the data each time, or have some other method of differentiating the data, then it will quickly become ambiguous i.e one weeks data will not be easily differentiated from another, however using your example as a basis:

Tbl_Total_OT

Officer Total_OT_HRs

Then create a and append query

INSERT INTO Tbl_Total_OT (Officer, TOTAL_OT_HRS) SELECT [Officer], SUM([OVERTIME CLOCK]) as [Total_OT_HRS] FROM [Overtime]

You would likely want more fields, however this should point you in the right direction