Not quite sure what to do

[golfinray]

I am building, as a part of our existing database, a form for payments. We
pay school districts for construction projects with state funds. Sometimes
those payments stretch out to 30 payments over 2 years of construction. So
far, I have three tables. Fiscal:
PK district id
School
project description
Wealth Index (they get paid a percentage according to their wealth)
Total cost of the project
State share of the project

Then District:
pk District ID
District name

Then I need a table for:
Date of pay request
Partial or Final?
Partial payment number (say payment 1 - payment 30)
Payment amount
Cumulative payments made
Cumulative percent complete

I am having trouble visualizing how I am going to have the form call up
payment 22 when it is time for that payment. The calcualtions should be
pretty simple, keeping up with cumulative and so forth. I am just kinda lost
on the rest. Thanks a bunch!!!

 

[Jeff Boyce]

A couple of observations ...

As your tables are designed, no school could have more than one project --
does that match your situation?

I need more information about your specific situation -- how are projects
related to schools? How are districts related to schools?

How are payments related to projects (I'm assuming payments are on
projects)?

Any field named "cumulative xxxx" is probably unnecessary. Rather than
trying to store and update and keep synch'd a calculated value like this,
just use a query to calculate the "cumulative" amount on the fly.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[golfinray]

Yes, districts can have more than one project at a time. Schools belong to
districts, projects belong to districts. Payments are on projects and they
usually get one payment per month for the life of the project. In the case of
a large project, maybe up to two years. We do need to store each individual
payment and date made for historical and auditng purposes but the cumulative
will be a calcualtion. Thanks!

 

[Maarkr]

ProjectTbl
ProjID
DistrictID
Amt
Scope, etc

PaymentTbl
PmtID
ProjID
ProjPaymentNo
Date
Amt

Form with subforms with main District, Sub Projects, Sub Payments
PaymentTbl fields in sub Payments showing plus Calculated fields-
totalPayments and BalanceRem

 

[Jeff Boyce]

Were I in your situation, I would use the description you've just provided
to make sure I had tables and relationships that match my situation.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.