Database Design Help: Lien Waiver Tracking

[twarner3]

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%

[orange]

Perhaps this tutorial will help with design. The answer and intermediate steps are all included.
The secret to a productive useful data base is getting your tables and relationships set up and tested to ensure they support your requirements BEFORE getting in to the queries, forms etc of Access.

[jzucca]

I found this post re: tracking construction lien waivers via MS Access and am in need of the same type of database. Where can I get more information on building this type of database? Thank you

[Welshgasman]

I found this post re: tracking construction lien waivers via MS Access and am in need of the same type of database. Where can I get more information on building this type of database? Thank you

8 year old thread?

[mike60smart]

This post is 8 years old.

How do you currently manage these lien wavers?
If you can upload an example of how you do it now then maybe we can help.

[moke123]

try the tracking section https://datamodels.databases.biz/

[jzucca]

We were previously using the Sage 300 accounting system to track our vendor’s lien compliance. But we’re converting our accounting system to Viewpoint Spectrum and going live this week. During our implementation we discovered the salesman misrepresented this function and we’re now searching for alternatives. There are several services on the market, but they require us to have them process all of our vendor payments, which we don’t want to do. So I’m exploring ways to build our own tracking system.

[jzucca]

Hi Mike60smart, We were previously using the Sage 300 accounting system to track our vendor’s lien compliance. But we’re converting our accounting system to Viewpoint Spectrum and going live this week. During our implementation we discovered the salesman misrepresented this function and we’re now searching for alternatives. There are several services on the market, but they require us to have them process all of our vendor payments, which we don’t want to do. So I’m exploring ways to build our own tracking system. Any help we can get is greatly appreciated. Thank you

[mike60smart]

Hi
Can you upload an Excel file showing how you currently manage your process?