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

[kd2017]

Well if you're using my model let's simplify it first. I've cut out some of the fat but I'd like to refine it. (ssanfu's model from post #8 looks like a good starting point as well)



Is it your intention for this database to keep a progress update history and/or a billing update history? Or can it always just be current?

So I'm thinking between our two designs, the measurement book entries would be the ProgressUpdates table. The BOM is more or less the WorkItems table.

Do you need to know who is making a particular entry in the Measurment Book? It seems that you suspect your own coworkers recording incorrect information here, do you need to be able to sort out who did what?

In your bill of quantities, can the work from one entry be awarded to multiple subs?

Do we need to consider change orders?

[rehan0129]

No, I don't intent to keep track of update history as the work is stopped completely and only reconciliation of all the work is underway. But yes I have to keep the track of contractor bills.

No I don't intent to keep a track of which engineer made the entry in the MB as I can easily identify from hard copy since it is signed by working engineer. It doesn't need to know the engineer who did what.

The quantity of work to be done is very large and cannot be done by one single contractor and therefore one type of work is given to multiple contractors. For example Gypsum plaster quantity in the entire project is about 50,000 Sqm, this cannot be completed by one single contractor and therefore multiple contractors are hired to complete the work.

What is change order here? didn't get that.

If you are revising the model, I suggest you work on the model that I sent earlier That way things can be better. Can you send your worked out model.

Did you checked the form? I have deleted that form here and trying to rework on it.

[kd2017]

What is change order here? didn't get that.

At this point don't worry about it I just realized you're in India so some of our practices here in the US won't apply to you.

If you are revising the model, I suggest you work on the model that I sent earlier That way things can be better. Can you send your worked out model.

Did you checked the form? I have deleted that form here and trying to rework on it.

I took a brief look at your Contractor Registration form, I can't see why it's blank.

Here is what my table structure looks like. When I have some time later I'll work out some forms and send the db file to you.

[rehan0129]

I don't know if it make sense, totally amateur. I am just trying simplify things.

[kd2017]

Sure thing, if that's what makes more sense for you! It's starting to look an awful lot like the table from post 8

[rehan0129]

I have worked on DB and it has started to work something that I wanted. But what is really bothering me is that entering of foreign keys in tables. Most of the tables are connected to other table by foreign key. Now I don't understand do I really need to remember so many primary keys? Do I have to assign foreign keys manually in all the tables? Gosh it is not possible. Imagine I have a data with 1000 of records, its not humanly possible to enter foreign key in record with accuracy. I am attaching the file for review.
I am sure there must be some way to get this done but I am just not sure how to do it.

[kd2017]

No you do not. You shouldn't have to memorize a single key. Keys should all be taken care of by your forms and/or vba. Do some studying on google, watch some youtube videos, on making access forms and subforms.

Utilize drop down combo boxes where ever it's convenient. For example, to create all of your "Areas" you might do the following
1) Single click the Areas table
2) In the ribbon go to Create -> Form
This should have automatically generated a form with fields for AreaID_PK, LocationID_FK, and AreaDescription
3) Select the text box to the right of LocationID_FK
4) Right click the text box -> Change To -> Combo Box
5) In the Ribbon's Design Tab open the "Property Sheet" if it's not already opened
6) Make sure the new Combo Box next to LocationID_FK is still selected, then in the property sheet select the "Data" tab
7) Paste the following sql statement in the "Row Source" property

SELECT Location.LocationID_PK, "Floor #" & [Floor] & " - Wing " & [Wing] AS Expr1 FROM Location;

8) (With the dropdown still selected) Go to the "Format" tab in the Property sheet. Change the "Column Count" to 2, change the "Column Widths" to 0;2
9) View your form and enjoy


AND/OR
10) Go to Design View (not layout view)
11) Right click AreaID_PK and select "Delete Row". The user doesn't need to see these
12) On the Property Sheet, in the dropdown box select "Form"
13) Set the "Allow Datasheet View" property to Yes
14) On the ribbon go to Design -> Views -> Datasheet View
Enjoy! Now you have a form to quickly create and edit all the "Areas" for the database and assign them to their appropriate Location, all without knowing their keys.

Between setting up combo boxes like this where appropriate AND using subforms with properly linked Master and Child fields I think you can accomplish just about everything you've said you need to accomplish for this project without ever showing the user a primary key.

One note from taking a quick look at your db file, do you need a UnitID_FK in both WorkDone and BOQ tables? Seems to me you only need it in one or the other.

[ssanfu]

My thoughts/comments........


RE: Post #15

dB: finishing database.accdb

<snip>
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.
<snip>

The reason that the form is not visible is because of the form record source. Here is the query for the form

Obviously, no records will be/can be returned, thus there is nothing to display in the form....


----------------------------------------------------------------------------------
RE: Post #21
dB: 9117 SubCon database.accdb

I have worked on DB and it has started to work something that I wanted.
<snip>

This relationship view looks somewhat like the one I posted in Post #8, except it has been modified to not work.


Also,

This relationship shows that you will have to type in the "AreaDescription" for EVERY record (1000s). Since you will have to type in each "AreaDescription", you might as well move the field "AreaDescription" to the table "Location" and delete the table "Areas".



At this point, I am going to step away..........


rehan0129, good luck with your project..........

[kd2017]

Yeah the area/location relationship struck me as weird, didn't take enough time to examine the relationship.

[rehan0129]

I have finally made the database as I needed. I think its pretty much in line now. I am now designing form to store data in the tables. But I have noticed one issue in the form, I have attached my file for review.

If you see the form, the record scroll at the bottom shows only two records. Yes, I know that details are filled in the form for two contractors only i.e M.S Enterprises and R.B. civil but if you see the table "SubCon" I have listed all the contractors working. So even though detail for only two contractors are filled, when I scroll the record in the form I should be seeing all the contractors with blank detail. But only two records are seen, why? I don't want the user to enter same contractor which is already there in the "SubCon" table unless there is new contractor is to be added to the SubCon table.

My intention is to make a form on the basis of subcon. The data flow in the form is something like this:

1. User inputs SubCon company name.
2. WorkOrder is assigned to the SubCon. There can be multiple WorkOrders and each WorkOrder can have multiple BOQItems.
3. Location detail of each BOQItem that the SubCon has worked at is provided by the user.
4. Quantity of each BOQItem at each Location under each WorkOrder is provided by the user.

(Words bold above are the tables in the database)

I request forum members to kindly suggest and share their knowledge and experience for developing databases.