Database to track quantity of work done by contractors in a construction project

[rehan0129]

Views: 18 Size: 117.6 KB">9117 SubCon database.zipHi,
I am an engineering manager working in a construction of highrise building. I want to make a database for the agencies that are working on the project as "Subcontractor". I have tried to build the database which is attached but I am unable to get satisfactory answer from the query I build.
Just an overview to what I want from this database and the complexity of the structure.

1. There are multiple subcontractors working on the project.
2. There are multiple type of work that a subcontractor can do.
3. Each work done by a subcontractor can either be new work or repair work.
4. As the project is construction of high rise building, a subcontractor is working on multiple floor
5. Each floor is further divided in to two wings, namely A-wing and B-wing. Each wing of the building has number of apartments (Flat No)
6. On a floor multiple subcontractors are working for either same type of work or different type of work.
7. A subcontractor is given a work order to do a specific work. Example: Work Order No. 123 is given to a subcontractor "X" to do gypsum plaster. Work Order No. 456 is given to a subcontractor "Y" for the same work i.e. gypsum plaster.
8. Subcontractor "X" can do multiple type of work like gypsum plaster, brick masonry, block masonry, etc. same way subcontractor "Y" can do multiple work.

​The objective of this database is;

1. To track on which location which subcontractor has worked.
2. To know the quantity of work done by all the subcontractor.
3. Most importantly to keep track that no two subcontractor claims to have worked on the same location for the same type of work.

In fact the need arise to make this database is because many subcontractors has submitted the bill claiming payment for the same type of work and at the same location. This can happen if one contractor has done new work like doing gypsum plaster on block masonry but after electrical installation work which caused some damage to the plaster than some other contractor has done the repairing work. However in such case the repair quantity should be definitely less than new work but it is seen that repair quantity is more than new work claimed by few subcontractor.

Till now all the measurement are recorded by the site engineer in measurement book (M.B) filled manually. Subcontractors has taken the advantage of this manual record as no data can be compared at the same time. As a result the project has run in to loss and is under financial instability.

I request forum members to guide me to prepare this database with their experience and expertise.Attachment 30147

I have attached two trial files in this thread for reference.

[ssanfu]

Welcome to the forum....

OK, I'll give it a go. Maybe others will chip in with suggestions/corrections.

Looking at your post and the dB, I think the table structure/relationships will change a lot....

Questions:
1) In table "WorkDone", what is the field "Quantity"? Number of workers? Hours?
The type is Double with 0 decimals. Can the "Quantity" be a partial value? (ie 250.5 or 300.25?)

2) "Rate" is money per hour?


3) Is all work done via a WO?

4) How is work assigned to a Contractor, floor, wing, flat/staircase/LiftLobby/SevantRoom/Passage?
(and what is SevantRoom?)



Would you tell us about a "normal" day in the life of building the building? Not computer jargon.
Explain what happens, like I am an interested 15 year old, with regards to what you are trying to do.

[rehan0129]

1) 'Quantity is the quantity of work done by a subcontractor for the "BOQ" item at any given location that he has worked. This quantity is recorded in "Measurement Book" (MBNo) by the engineer. Yes, quantities can have decimal values.

2)"Rate" is amount per unit of work given in "BOQ" or if the labors are working under department than these labors are paid in "man days".

3)Yes all the work is given to a subcontractor via work order only.

4) Initially work was assigned to a subcontractor floor wise only, but later due to some or the other reason the subcontractors left the work half done. This incomplete work was than completed by other subcontractors. Servant Room is just like any apartment but small in size for servants who will be working for the apartment owners. These servant rooms are constructed on each floor in both wings of the building.

By the way, today I just got a revelation from the manager that when the project was started way back in 2013, at that time the billing system was not so developed and the billing engineer assigned multiple type of work under one Work Order. When I started preparing this database, I was under the impression that each work order had one item of work from the BOQ. But now I see in one work order there are many item of work done by a subcontractor. what it means is that a work Order #123 issued to a subcontractor "X" is having 'A', 'B', 'C' type of work and the work order #456 issued to a subcontractor "Y" can also have same 'A', 'B', 'C' type of work. I believe by doing so the whole idea of issuing a work order is destroyed. Nonetheless, now every work order is unique to the BOQ item of work.

I am attaching a reworked database which works just a bit well than previous version.
One, most important thing that I want to know urgently is that how can I ease assigning "LocationID" in table "WorkDone" as I have to constantly go back and check in table "Location" the "LocationID" to be referred in table "WorkDone". I know it sounds confusing but I'll get a hang of it to get it clear.

A normal day in building a building is kind of confusing specially if the project is controlled from the very beginning. One needs to be visionary in order to complete the project within time and budget. So yes in this project, it is going really tough.

[kd2017]

(Total amateur here, follow my advice at your own risk)
Based on your description here is my take on a model for your situation. I think it can hold all the information you asked for. Maybe it will make a starting point for your database project.

I've assumed that your database does NOT need to maintain a progress history of item completion. Let me know if that's the case. QuantityCompleted, QuantityBilled (see edit) are quantity totals to date, not periodic progress updates. (It'll be easier this way )

For this model, something to note is how you record the qty of work completed. If you store the completion in units of work then (based on my model) you will have to pragmatically ensure that you don't assign more units of work than are available (TotalQuantity). However, if you store the qty assigned and qty complete as percentages of the total quantity, then at the table level you can simply make sure the field is an upper limit of 1.0. That said, either way you will have to pragmatically ensure that you don't assign more work to different subs than are totally available. This is all easy to do, but I just wanted to point it out. I think the database guys call this enforcing 'business rules'? (edit) personally, for consistencies sake, I'm thinking I would not store them as percentages.... but that's just me. Maybe a real db pro can step in with advice on this.





----------EDIT---
It occurs to me you may want to add a QuantityBilled field to the WorkOrder_Items table to keep track of what the contractor has billed for, and maybe a LastBillDate. Also note that LastUpdate is a date field that contains the date the last time QuantityCompleted was updated by your engineer.

ALSO, you could add a field to WorkOrder_Items to hold an employee ID of the last engineer that updated the QuantityCompleted field... and maybe a comment field for the engineer to make a note.

Speaking of notes you could create a Notes table that is linked to the WorkOrder_Items table to keep track of notes for that contractor/work order item... And alerts, and urgency levels, and... I'm getting too excited and ahead of my self here. Maybe work on that later after the initial requirements of the project are met.

----------EDIT AGAIN---


This is just an example.


----------EDIT AGAIN---I'm bored
Here is another example that would allow to you keep records of the billing and progress updates. Progress update quantities are the quantity of work completed to date. The Billing update qty is the amount billed for on that bill, not completed overall. For some reason that feels more natural for data entry purposes. It doesn't have to be that way... and maybe it shouldn't for consistency purposes, I don't know. Alright, I'm done brainstorming for now.



BTW In regard to the original billing engineer

By the way, today I just got a revelation from the manager that when the project was started way back in 2013, at that time the billing system was not so developed and the billing engineer assigned multiple type of work under one Work Order.

that's how I would have done it! My model allows for one work order having multiple types of work (work items).

[ssanfu]

Is this true??
1) One contractor can have many work orders (WO) and one WO is assigned to one contractor.

Is this true??
2) One WO can have many locations and one location can have many WOs.


I am guessing that each floor has only 12 flats, flats # 1 - 6 in A Wing and flats # 7 - 12 in B wing. True??

what it means is that a work Order #123 issued to a subcontractor "X" is having 'A', 'B', 'C' type of work and the work order #456 issued to a subcontractor "Y" can also have same 'A', 'B', 'C' type of work.

So one WO can have many parts.
For WO #123, can
"A" type of work (based on BOQ Item) be for Floor 6, Wing A, Flat 5,
"B" type of work (based on BOQ Item) be for Floor 6, Wing B, Flat 9 and
"C" type of work (based on BOQ Item) be for Floor 5, Wing A, Flat 5??

[rehan0129]

@KD2017
In your model as you can see there are so many cross references in each table. Which means all the 'ID' fields of each table have to either by hearted or it should be first worked out in excel.
I am finding it difficult to understand all the relationships and unless the relationship is not clear, the queries I build will also be under doubt. Do we really need so many tables I mean more the table more the data entry and more chances of wrong data input. This will make entire database unreliable.
Even though I have prepared the table as per your model but I have not run any query yet. I want to first learn how this relationship works but I am unable to completely understand the relationship.

@ssanfu
1) Yes, one contractor can have many work orders. WO is unique in itself, no two Work orders can have same WO number.

2) Yes, with one work order the contractor can work at many location. As the work order issued is for specific work, it is never mentioned in the work order at which location this work order is to be executed. As many contractors can work at the same location and each contractor having there own set of work orders, therefore one location can have many WO.

3) To some extent you are right, typical floors do have equal number of flats in each wing, however at few floors the configuration changes as per architectural design and there are different number flats in each wing.

4) Yes, under one work order there can be multiple type of work. e.g. WO #456 can have painting work, Brick masonry work, Block masonry work.

[kd2017]

First thing's first: I'm not telling you how you should do it or how it should be done, merely brainstorming ideas. I'm not a developer by profession, but I do work in the construction industry.

ID fields are typically automatically maintained by access. When you are entering data you usually design the data entry forms in such a way that that all the links would be created and maintained automatically, the user doesn't even have to know they are there.

Regarding work areas and work area types:
I just assumed that you might need to assign work to areas outside of the apartment units, like hallways or mechanical rooms, or lobbies, etc. If you don't, then you don't need the work area type look up table
I was going off of how different areas of a building are labeled in blueprints. I've attached an example of what I'm talking about, I circled the labels of the different areas in red. You'll see for example it has room labels A2, A3, and A4, with "area numbers" 1107, 1109, and 1106 respectively. Also notice there is also areas labeled TRASH, ELECTRICAL, ELEVATOR LOBBY, all with their own "area numbers". When an area type is an apartment, the area number might also be the apartment number. (This was just my thought process at the time. Again, not telling you you should do it like this)

So, a WorkAreaNumber might be the apartment number, or the electrical closet number, elevator lobby number, etc.
The WorkAreaType might be a Apartment, electrical closet, lobby, etc.



Regarding work item type:
The work item type table is a list of all the different kinds of work that might need doing. Drywall, flooring, electrical, painting, plumbing, etc. You can get as specific as you need to. Assuming you use MasterFormat you could break it down by MasterFormat divisions if you wanted.

---EDIT
I've rearranged the relationship view to hopefully illustrate what's going on


I assume the only tables that will get frequently updated here, after initial setup, would be WorkItems, WorkOrders, and all of Group 4. Just 6 tables.

Group 1)
Floors - one entry per floor in the building
Work area types - one entry for each type area in the building like apartments, bathrooms, fire stairs, etc.
Work Areas - one entry for each actual physical area that you want to divide the building into.
After setting up the database you'd hardly ever, if ever, need to update these tables.

Group 2)
Units - one entry for each kind of measurment units you use like Linear Feet (LF), Square Feet (SF), Each (EA) etc. After setting up the database the data in this list (table) will hardly ever change if ever
WorkItemTypes - one entry for each kind of work that you subcontract out: painting, electrical, etc. After setting up the database this list might not change much.
WorkItems - one entry each quantity of work that needs to be done in an area. IE 500 SF of paint in floor 8 room 809 or 30 LF moulding on floor 10 room 1002, etc.

Group 3)
Contractors - only gets updated as often as you work with new contractors
WorkOrders - self explanitory

Group 4) All updated frequently
WorkOrder_Items - Here you "assign" a portion, or all, of the work from group 2 to a contractor's work order. Per your first post, the idea here is you can assign part of this same work to contractor A and the rest to contractor B (Or as many different contractors as it takes). Warning: you will need to find a way to make sure that the total of QuantityAssigned for a WorkItemID <= TotalQuantity
BillingUpdates - If you want to, everytime a contractor sends you a bill for the work you could log that information in this table
ProgressUpdates - The table for your company to track the actual progress made on the work, to check against contractor billing
Notes - just an idea I had, a convenient place to make remarks on work. IE "Contractor x said he would finish by the end of the month" or "Finished y but damaged something else, need to create a new RFP" ....whatever

And then employees is just a list of employees so you can know who made the note, progress update, etc.

If it were me I would even add another table called RFIs or RFPs or something to logically group together WorkItems. But we can talk about that later if you're interested.

[ssanfu]

Based on your 3 objectives from Post #1, I went a little simpler (maybe too simple??)..

[kd2017]

BOQ is the type of work, right? Like plumbing, drywall, etc? So a specific type of work is always measured in the same units? I was under the impression that portions of the same work can be awarded to multiple contractors per post #3, is this not the case?

What is PgNo? Is MBNo a fk?

Should the Floor and Wing fields be in the area table?

[ssanfu]

What is PgNo? Is MBNo a fk?

Post #3: "This quantity is recorded in "Measurement Book" (MBNo) by the engineer." I am guessing "PgNo" is the page in the book (MBNp).

Don't know what "BOQ" means.

BOQ is the type of work, right? Like plumbing, drywall, etc? So a specific type of work is always measured in the same units?

I just used the provided BOQ table, then added the "Units" table. Don't understand some of the units...

I was under the impression that portions of the same work can be awarded to multiple contractors per post #3, is this not the case?

How I understood it:
One contractor can have many WOs.
Each WO (ex #123) can have many Items (ex 'A', 'B', 'C').

As I said... maybe too simple...

[rehan0129]

Let me just brief you the objective of preparing this database so that you can get an idea what I am expecting from this database. In this project that I am working on has run in to tremendous financial loss and negative cash flow. So I am given a task to find out how this project has run in to such a heavy loss. I started to investigate and found that mainly in finishing work the contractors has played tricks. Some of my company staff and contractor together made excess entries of the measurement in the measurement book (MB) and billed for the work which was not even done. Since there are hundreds of MBs where the measurements are recorded and on the basis of this payments are made. Now scouring through hundreds of MBs to find out in which MBs there are multiple measurements recorded for the same work at same location by one contractor or by multiple contractors. So by preparing this database I will simply record all the detail from the measurement book (MB) of all the contractors for all the type of work and let the database do rest of the working to find out all the wrong doing and according debit the amount to the contractor. This way I'll be able to reduce the liability burden on the company and bring out culprits.

So MBs are physical hard bound books to record measurement and pgNo is the page number in the MB from which the quantity is recorded for particular type of work.

Hope this gives fair amount of idea as to why this database is so important. And yes I have not left with much time to complete the task. So I am working with the same model as you have shown and I hope this will do the working.

[ssanfu]

Just curious, what is the meaning/abbreviation "BOQ"??

[rehan0129]

Sr No	Description	Unit	Quantity	unit Rate	Amount
1	12.5 mm Gypsum plaster over light weight block masonry over internal walls.......(further detail description)	Sqm m2	2500	350	875000

'BOQ' is Bill Of Quantities. This BOQ forms the basis for tendering and award of contract and lot more thing. In short, in BOQ you have detail description of the type of work and how that work should be executed. Total quantity of that item of work, unit rate of the work and the amount. An example is shown above.

[kd2017]

In that case I think there are a few changes to be made to the latest model. What does the actual information in the measurements book look like?

[rehan0129]

A standard measurement book looks something like this:

Sr No	Description	UOM (unit of measurement	No	Length	Breadth	Depth/Height	Quantity	Remark

I have incorporated this MB and Pg detail in WorkOrderItem Table. Hope this should work.

I am now developing queries and forms. What strange is happening in creating form is that I can view form in design view but when I switch to Form view the form is not visible. I looked on the internet but could not get around. I don't know whats the bug. I am attaching the file for review.
I need to get this database up and running soon or else I'll be in trouble. I know its kinda tricky and brain scratching but I am working hard on it. I have not slept well since a week now to get this done.
BTW, thanks a lot for your effort and help. Appreciated.