Hello
First off, I am a new Access user, no formal training. I've tried to map this out and I just can't seem to come up with anything that doesn't cause data redundancy. Any help/advice would be greatly appreciated
My Scenario:
I work for a commercial construction company and I am trying to create a database that can track our lien waivers on various jobs and subcontractors. We require our direct subs to provide a list of secondary vendors, along with amounts expected to be paid to these vendors for the job. We require our direct subs to provide monthly waivers from these secondary vendors.
Hopefully this will give a better picture:
Now just imagine multiple subcontractors and multiple jobs.
Also as an example:I have #1 Contractors on job 13-0490. in this case, they have one secondary vendor, Drywall, Inc., that we need waivers from. That vendor's contract is $13,000. I have the following waivers:
June 30, 2013 $2,000
July 30, 2013 $4,000
August 30, 2013 $1,000
I have tried doing this in Excel but it is just too jumbled. I need to be able to make a report that will show how much of their contract has been paid to each secondary vendor by each subcontractor on each job.
As a note, I do not need any addresses or contact info included in this database. The way I see it, I have a list of job numbers and descriptions, a list of subcontractors on that job, a list of secondary vendors for that subcontractor, the secondary vendors contract amount, and secondary vendors waiver amounts.
Any thoughts on how to make this work or am I looking at the wrong data solution?
I've tried to include everything on here, please let me know if you need more info.
Just for reference, see below for the way I have the information in Excel, Premier is the subcontractor, all others are secondary vendors to Premier
Premier Concrete 13-0489 |
|
|
|
|
|
Couser Supply |
|
|
|
|
|
Waiver Date Paid Thru |
Waiver Amount |
|
|
|
|
6/30/2013 |
$5,823.58 |
|
|
|
|
7/30/2013 |
$3,728.40 |
|
|
|
|
8/30/2013 |
$69,765.98 |
|
|
|
|
Base Contract |
Change Orders |
Other Add's |
Total Contract |
Total Waivers |
% Paid |
$90,120.00 |
$0.00 |
$0.00 |
$90,120.00 |
$79,317.96 |
88% |
|
|
|
|
|
|
Form Services |
|
|
|
|
|
Waiver Date Paid Thru |
Waiver Amount |
|
|
|
|
6/30/2013 |
$4,084.37 |
|
|
|
|
8/30/2013 |
$0.00 |
|
|
|
|
Base Contract |
Change Orders |
Other Add's |
Total Contract |
Total Waivers |
% Paid |
$16,626.00 |
$0.00 |
$0.00 |
$16,626.00 |
$4,084.37 |
25% |
|
|
|
|
|
|
Jernigan Concrete Pump |
|
|
|
|
|
Waiver Date Paid Thru |
Waiver Amount |
|
|
|
|
6/30/2013 |
$527.28 |
|
|
|
|
7/31/2013 |
$6,996.76 |
|
|
|
|
8/30/2013 |
$23,869.50 |
|
|
|
|
Base Contract |
Change Orders |
Other Add's |
Total Contract |
Total Waivers |
% Paid |
$147,403.00 |
$0.00 |
$0.00 |
$147,403.00 |
$31,393.54 |
21% |
|
|
|
|
|
|
Mohawk Bridge and Iron |
|
|
|
|
|
Waiver Date Paid Thru |
Waiver Amount |
|
|
|
|
6/30/2013 |
$1,827.00 |
|
|
|
|
8/30/2013 |
$14,616.00 |
|
|
|
|
Base Contract |
Change Orders |
Other Add's |
Total Contract |
Total Waivers |
% Paid |
$33,616.00 |
$0.00 |
$0.00 |
$33,616.00 |
$16,443.00 |
49% |