General Database/Query and Form Design question

[Guy Horton]

Currently working on an existing system written using an Access 2002 project
(.adp) and SQL Server 2000 and need to add some ehancements.

The system is a leasing system where a customer leases one or more assets
for a defined term (eg. 24, 36, 48 months...). Each lease may also be
associated with a fixed, or variable monthly repayment regime.

For example a customer wants to lease a Boat for 24 months for the first 12
months (period 1 - 12) they pay $50 per month, for period 13 - 18 they pay
$35 per month, and for the last 6 months (period 19 - 24) they pay $25 per
month.

The proposed table design is as follows:

tblLease
LeaseId int (identity) PK
CustomerId FK
TermId FK
....

tblLeaseAsset (1:M relationship to tblLease)
AssetId int PK
LeaseId int PK/FK to tblLease
AssetDescription
....

tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
1:M relationship to tblLeaseTerm)
AssetId int PK/FK
TermPeriodId int PK/FK
Payment decimal (19,4)
....

tblLeasePeriodTerm (1:M relationship to tblLease)
TermPeriodId int (identity) PK
LeaseId int FK to tblLease
FromTermPeriod small int
ToTermPeriod small int
....

Whilst the users are happy to enter the initial lease and period/term
information as a Parent/Main form and Child/Subform combination. They would
like to be able to enter the Asset and payment information together as a
single Child/Subform:

Period Period Period
Asset Description 1 - 12 13 - 18 19 - ...

A Boat $50 $35 $25

The problem is that this requires a pivot table/cross tabulation type view
of the data and these types of queries are not generally updatable.

Does anyone have any ideas how I might achieve the objective either in terms
of database, query or form design so that users can insert, update, delete
and view records?

Your assistance apreciated
Guy Horton

 

[Albert D.Kallal]

Well, for each "asset", you got


AssetName AssetCost WherePurchased
Boat $15,000 WalMart
Car $5,000 MacDonalds

etc.

Now, just put another sub-form to the "right" of a above where you can enter
"many" values for each of the above.

If your cursor is in Boat, then you can enter:

Period Amount
1 - 12 $50
13 - 18 $35
etc.

I can think of "many" cases where you got a detail line, and need "many" for
that details. consider the QuickBooks when you write a single check, and a
split amount, you need to "split" out the funds to "many" values. So, the
solution is to make two side by side sub-forms.

Take a look at the following screen shots, and especially the last one where
I have a "classic" cheque "distribution" screen (for each check/person on
the left, I can enter "many" split values for that particlar amount
(donation in this example) on the right side...

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Robert Morley]

I'd strongly recommend Albert's way, but if you're absolutely forced to
doing it on the same line, you can try a few different things that I can
think of:

1. Play with Access' PivotTable features. I've never found them all that
useful, and very klunky to use, but it may get you where you're trying to
go. Don't ask me for more detailed info on how to do that, though, I
generally avoid PivotTables like the plague.
2. Implement a temporary table that goes across as you'd like it to, then
as each record is read/updated, transfer the values to/from the "real" table
in the OnCurrent and Before/AfterUpdate events.
3. Use an embedded control of some kind (Hierarchical FlexGrid?) to display
the data instead.



Good luck,
Rob

 

[Guy Horton]

Albert,

Thank you for your excellent response. I reviewed your article and screen
shots and have to say they look very professional.

I briefly considered side by side subforms and agree with you that this is a
very workable option, and probably the option I will go with. Although, it
doesn't allow the users to view all lease rates for all the currently
visible leased assets, and they think of periods as running across as
opposed to down the form.

Your thoughts appreciated.

Best Regards,
Guy

 

[Guy Horton]

Robert,

Thank you for your response. I agree that Albert's solution is the probably
the most sensible way to go, and that PivotTable features are klunky to use.

Your thoughts appreciated
Guy