Project Data Model Design (1 Viewer)

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
Good Day !
I want to create project cost and Balance Model design for Cost and Balance Control Database. want to someone help basic thing and how to design this database tables and relationship for easily to calculate my below of field.

How we work progressing :
each project give Bill of quantity (BID) and approve by Purchase Order (PO)
Eg. Project No: 01 (Roco WareHouse) has been approval with in 2.4M USD.
Project No: 02 (CC7 New Building Centre) has been approval with 2.5M USD.

Example of BID : Project No : 1 ( Roco WareHouse) as mentioned.
1. Civil work- (PO- 140,000 USD) ,
2. FireFighting (PO- 160,000 USD),
3. Plumbing (PO- 180,000 USD),
4. Electrical (PO- 260,000 USD),
5. Paint (PO- 120,000 USD),
6. Decore (PO- 1540,000 USD),
Total 2.4 m USD.

Now Start work:
Cash Money (Example):
Cashier give money to Site Engineer or Site Foreman.
Site Engineer give money to Site Foreman or buy material or give to an employee Cash Advance .
Site Foreman buy any material for project no: 1 OR 2 OR 3 etc. sometime give to an employee Cash Advance (if need)

Invoice (example) :
Site Foreman give Sales invoice to cashier.
Site Engineer give Sales Invoice to cashier.
Employee Cash Advance Form Sign get money receipt and send to cashier.

I looking to-
each project how much cost and and profit ?
each site engineer how much money took and How much money invoice he send to me and How much money balance with him ?
each site foreman how much money took and How much money invoice he send to me and How much money balance with him ?
Which employee took money as cash advance and How much ?
 

Ranman256

Well-known member
Local time
Today, 05:21
Joined
Apr 9, 2015
Messages
4,337
you sorta have it laid out already,

tProjectTypes table:
--------------------
Civil
FireFighting
Plumbing
etc

that table fills the tProjects tbl below , that could still be in bid phase or project phase:

ProjID, ProjType, Phase, CiientID, etc..
--------------------
123, Civil, Bid, 101
124, FireFighting, Project, 987
125, Civil, 456

tInvoice tbl:
InvoiceID, ProjID , InvoiceDate, PaidOn
1, 123, 2/1/2019 , ,
2, 123, 3/1/2019, ,


tPayments tbl:

PayID, ProjID, PayDate, PayHow
1, 123, 1/1/2021, Cash,

you then use queries to determine, money difference vs invoices.
 

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
Thank you so much, I create and uploading it sooner...
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:21
Joined
Jul 9, 2003
Messages
16,243
Good Day !
I want to create project cost and Balance Model design for Cost and Balance Control Database. want to someone help basic thing and how to design this database tables and relationship for easily to calculate my below of field.

How we work progressing :
each project give Bill of quantity (BID) and approve by Purchase Order (PO)
Eg. Project No: 01 (Roco WareHouse) has been approval with in 2.4M USD.
Project No: 02 (CC7 New Building Centre) has been approval with 2.5M USD.

Example of BID : Project No : 1 ( Roco WareHouse) as mentioned.
1. Civil work- (PO- 140,000 USD) ,
2. FireFighting (PO- 160,000 USD),
3. Plumbing (PO- 180,000 USD),
4. Electrical (PO- 260,000 USD),
5. Paint (PO- 120,000 USD),
6. Decore (PO- 1540,000 USD),
Total 2.4 m USD.

Now Start work:
Cash Money (Example):
Cashier give money to Site Engineer or Site Foreman.
Site Engineer give money to Site Foreman or buy material or give to an employee Cash Advance .
Site Foreman buy any material for project no: 1 OR 2 OR 3 etc. sometime give to an employee Cash Advance (if need)

Invoice (example) :
Site Foreman give Sales invoice to cashier.
Site Engineer give Sales Invoice to cashier.
Employee Cash Advance Form Sign get money receipt and send to cashier.

I looking to-
each project how much cost and and profit ?
each site engineer how much money took and How much money invoice he send to me and How much money balance with him ?
each site foreman how much money took and How much money invoice he send to me and How much money balance with him ?
Which employee took money as cash advance and How much ?

I must say that's one of the nicest laid out questions I have seen for a while, it was a pleasure to read!
 

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
what i guess from you. uploaded it... Need your help to edit.....
 

Attachments

  • Project Cost Control dB.accdb
    608 KB · Views: 515

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:21
Joined
May 7, 2009
Messages
19,169
Cost and Balance Control
you need a cost accountant to go into minute details.
it is not just about invoice, payment.
you need to account office supplies, direct/indirect materials of the project.
labor, manpower, rental equipments, supplied personnel, etc.

this cannot be done by a single person alone.
you need a group/team (planning department).
who consolidate info/reports from proj. site and client.

i say this because i am in construction field (oil and gas/power).
 
Last edited:

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
you need a cost accountant to go into minute details.
it is not just about invoice, payment.
you need to account office supplies, direct/indirect materials of the project.
labor, manpower, rental equipments, supplied personnel, etc.
Thank you so much for import subject you are catch. Thanks again.
Yes, it is - account office suppliers, (I want)
No need, - direct/indirect materials of the project. ( I have Invoice what they're are buy, Material list above the BID details, Project Manager give us the copy of PO and BID original copy. In the bid theyre mentioned which company and which type of quality and quantity used)

Yes need , it is maybe need for labor cost ( I mean salary and overtime)

Yes Need - rental. (I have the worker accommodation with electric bill and water bill )

Other no needs from my project. Maybe it optional..
 

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
Exactly I dont want to much table here.
Only calculation for Balance for - Employee, Engineer, Foreman.
Project Total Cost & Profit.
Each employee how much invoice send and how much Money took...
Other all of information have within the department manager

Want a cost Center for filtering easily..
b/f I used smacc5 software there have setup a cost center. LIKE : cstID - b/f Balance - Debit - credit - balance ....
but I forget how to they're used.
 
Last edited:

mike60smart

Registered User.
Local time
Today, 09:21
Joined
Aug 6, 2017
Messages
1,899
Exactly I dont want to much table here.
Only calculation for Balance for - Employee, Engineer, Foreman.
Project Total Cost & Profit.
Each employee how much invoice send and how much Money took...
Other all of information have within the department manager

Want a cost Center for filtering easily..
b/f I used smacc5 software there have setup a cost center. LIKE : cstID - b/f Balance - Debit - credit - balance ....
but I forget how to they're used.
Hi

My first look at your database indicates that you are using Lookup Fields in tables.

You are using the Lookup field to obtain the related PK which is NOT the way Access is designed to work.

If you set the relationships up correctly then Access will automatically populate the related FK for you.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:21
Joined
Jul 9, 2003
Messages
16,243
My first look at your database indicates that you are using Lookup Fields in tables.

As Mike says, The lookup up combo boxes at table level are a known issue for developers.

This issue is discussed in Mr Weber's Smart Access September 2002 newsletter "List Boxes, Access Bugs, and More" by Chris Weber" which you can find (PDF Download) here:- (Find Sub DropThoseCombos() In the Newsletter)


I have Incorporated the code into a form to make it easy to use. You are welcome to a copy, contact me and I will tell you how you can get it.

Whenever I get a database to develop further, one of the first things I do is remove these table level combo boxes.

Warning!
However there is a there is something to watch out for, it also removes combo boxes which aren't lookups but which have value lists, be aware of that. I would suggest you experiment on the Northwind Database V2, and in particular look at the effect the code has on the table:- Orders, Field:- Payment Type, Re:- Value List:- Credit Card;Check;Cash


You can download copies of both Northwind databases here:-
 

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
As Mike says, The lookup up combo boxes at table level are a known issue for developers.

This issue is discussed in Mr Weber's Smart Access September 2002 newsletter "List Boxes, Access Bugs, and More" by Chris Weber" which you can find (PDF Download) here:- (Find Sub DropThoseCombos() In the Newsletter)


I have Incorporated the code into a form to make it easy to use. You are welcome to a copy, contact me and I will tell you how you can get it.

Whenever I get a database to develop further, one of the first things I do is remove these table level combo boxes.

Warning!
However there is a there is something to watch out for, it also removes combo boxes which aren't lookups but which have value lists, be aware of that. I would suggest you experiment on the Northwind Database V2, and in particular look at the effect the code has on the table:- Orders, Field:- Payment Type, Re:- Value List:- Credit Card;Check;Cash


You can download copies of both Northwind databases here:-
Thank you so much for your adviced. Appreciated.
My main dB all of the lookup is in the Form only. I dont use any lookup in the table OR BackEnd file. But uploaded dB file lookup I used for to know how to work it. And also there dont have any form...
 

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
Hi

My first look at your database indicates that you are using Lookup Fields in tables.

You are using the Lookup field to obtain the related PK which is NOT the way Access is designed to work.

If you set the relationships up correctly then Access will automatically populate the related FK for you.
Thank you so much Mr. Mike, Appeciate for your adviced.
Will you give me an example as my posted require...
Everyday I have 100+ Sales INVOICE for various projects. I need just one form for Daily INOVICE record data and it is save differents projects and different employees.
e.g: Today get 100+ for 5 projests.
Each Invoice have mentioned and signature project engineer by hand written which project numbers are used this materials.
So, If I have a source of Form that open and entry all of the INVOICE daily record by .

Thank you...
 

mike60smart

Registered User.
Local time
Today, 09:21
Joined
Aug 6, 2017
Messages
1,899
Can you draft out what you want to be able to do in an Excel example?
 

Rene vK

Member
Local time
Today, 10:21
Joined
Mar 3, 2013
Messages
123
Maybe it is a good idea that you do more work yourself and then show the problems that you run into.
At this moment and with the progress of your file you are expecting others to do all the work and you all the (financial?) gains.
This is a forum that is gladly helping other people with their solutions. It is not a platform for building free applications!
 

smtazulislam

Member
Local time
Today, 12:21
Joined
Mar 27, 2020
Messages
806
Maybe it is a good idea that you do more work yourself and then show the problems that you run into.
At this moment and with the progress of your file you are expecting others to do all the work and you all the (financial?) gains.
This is a forum that is gladly helping other people with their solutions. It is not a platform for building free applications!
Thanks for your reply. I dont requiring build a completed database with free. Review my all message and make sure what I wanting...
I seen, its first comment from you in my posted, but I see you are very excited.
 

Users who are viewing this thread

Top Bottom