General Database/Query and Form Design question

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 HortonWell, 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/AlbertKa...ticles/Grid.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal|||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" <guy.horton@.nospam.bigpond.com> wrote in message
news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
> 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,
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
"Albert D.Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ukKGPmwwFHA.460@.TK2MSFTNGP15.phx.gbl...
> 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/AlbertKa...ticles/Grid.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.members.shaw.ca/AlbertKallal
>|||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
"Robert Morley" <rmorley@.magma.ca.no.freakin.spam> wrote in message
news:eYrGDpxwFHA.3756@.tk2msftngp13.phx.gbl...
> 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" <guy.horton@.nospam.bigpond.com> wrote in message
> news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Fuzzy lookup error when adding additional lookup columns

I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.

It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:

Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.

These errors occur regardless of what columns I am attempting to add to the lookup list.

I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.

Any ideas on what else could be causing this?
Have you tried deleting the component and recreating it?|||Yes, I just tried deleting and recreating the fuzzy lookup, and the same errors are occuring.
|||

Uh! there is a similar issue reported in Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=266185

but no response from MSFT.

You may want to add your vote

Full-text search services on existing cluster node?

We have a two node cluster that is currently running two instances of SQL 2000.
It turns out that one node has full-text search installed while the other
node does not. Is there a way to install full-text search capabilities on an
existing node? Our research has provided an inconclusive answer. Short of
having to do a full re-install to the one node, is there a way to add
full-text search to an existing node?Hello,
You may want to refer to the following steps to install full-text search
service on the node:
1. Use the Searchstp.exe program to install the full-text search service.
If the full-text search service is not installed on the computer, the
Searchstp.exe program will create it.
2. Use the Ftsetup.exe program to configure the full-text search service
and to configure the instance of SQL Server as an application that uses the
full-text search service. On SQL Server clusters, the Ftsetup.exe program
also creates the required configuration files to maintain the failover
properties for the full-text search resource.
Searchstp.exe is under setupCD: x86\FullText\ftsetup.exe. You may want to
run searchstp.exe of SP4 folder to upgrade it to the proper latest version.
The Ftsetup.exe program uses the following parameter options:
? ApplicationName : For a default instance of SQL Server, the parameter
value must be SQLServer. For a named instance of SQL Server, the parameter
value must be SQLServer\ Instance Name .
? User : For a local system account, the parameter value must be 0. For a
domain user account, the value of the parameter must be Domain Name \ User
Account .
? IsMasterNode : This parameter indicates whether the Ftsetup.exe program
runs on the node that owns the disk where the FTDATA folder will be
created. The parameter value must be 0 or 1.
? IsUpgrade : This parameter indicates whether the FtSetup.exe program is
upgrading the full-text search service to a later version. This parameter
value must be 0 or 1.
? IsCluster : This parameter indicates whether the Ftsetup.exe program runs
on a clustered instance of SQL Server. For a stand-alone instance of SQL
Server, the parameter value must be 0. For a clustered instance of SQL
Server, the parameter value must be 1.
? IsUninstall : This parameter indicates whether the Ftsetup.exe program
removes the full-text search service. If the Ftsetup.exe program installs
the full-text search service, the parameter value must be 0. If the
Ftsetup.exe program removes the full-text search service, the parameter
value must be 1. However, if the parameter value is 1, the values for the
IsMasterNode parameter, the IsUpgrade parameter, and the IsCluster
parameter must all be set to 0.
You shall also remove the registry entries for a clean removal of the
full-text search service. To do so, remove the following registry keys on
both nodes of the SQL Server cluster:
? HKEY_LOCAL_MACHINE\Software\Microsoft\Search
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSCNTRS
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSEARCH
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSGATHERER
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSGTHRSVC
? HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSINDEX
Since the issue might be complex, we probably will not be able to resolve
the issue through the newsgroups. If above steps does not work for you, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Full-text search services on existing cluster node?

We have a two node cluster that is currently running two instances of SQL 20
00.
It turns out that one node has full-text search installed while the other
node does not. Is there a way to install full-text search capabilities on an
existing node? Our research has provided an inconclusive answer. Short of
having to do a full re-install to the one node, is there a way to add
full-text search to an existing node?Hello,
You may want to refer to the following steps to install full-text search
service on the node:
1. Use the Searchstp.exe program to install the full-text search service.
If the full-text search service is not installed on the computer, the
Searchstp.exe program will create it.
2. Use the Ftsetup.exe program to configure the full-text search service
and to configure the instance of SQL Server as an application that uses the
full-text search service. On SQL Server clusters, the Ftsetup.exe program
also creates the required configuration files to maintain the failover
properties for the full-text search resource.
Searchstp.exe is under setupCD: x86\FullText\ftsetup.exe. You may want to
run searchstp.exe of SP4 folder to upgrade it to the proper latest version.
The Ftsetup.exe program uses the following parameter options:
ApplicationName : For a default instance of SQL Server, the parameter
value must be SQLServer. For a named instance of SQL Server, the parameter
value must be SQLServer\ Instance Name .
User : For a local system account, the parameter value must be 0. For a
domain user account, the value of the parameter must be Domain Name \ User
Account .
IsMasterNode : This parameter indicates whether the Ftsetup.exe program
runs on the node that owns the disk where the FTDATA folder will be
created. The parameter value must be 0 or 1.
IsUpgrade : This parameter indicates whether the FtSetup.exe program is
upgrading the full-text search service to a later version. This parameter
value must be 0 or 1.
IsCluster : This parameter indicates whether the Ftsetup.exe program runs
on a clustered instance of SQL Server. For a stand-alone instance of SQL
Server, the parameter value must be 0. For a clustered instance of SQL
Server, the parameter value must be 1.
IsUninstall : This parameter indicates whether the Ftsetup.exe program
removes the full-text search service. If the Ftsetup.exe program installs
the full-text search service, the parameter value must be 0. If the
Ftsetup.exe program removes the full-text search service, the parameter
value must be 1. However, if the parameter value is 1, the values for the
IsMasterNode parameter, the IsUpgrade parameter, and the IsCluster
parameter must all be set to 0.
You shall also remove the registry entries for a clean removal of the
full-text search service. To do so, remove the following registry keys on
both nodes of the SQL Server cluster:
HKEY_LOCAL_MACHINE\Software\Microsoft\Se
arch
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSCNTRS
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSEARCH
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSGATHERER
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSGTHRSVC
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSINDEX
Since the issue might be complex, we probably will not be able to resolve
the issue through the newsgroups. If above steps does not work for you, I
recommend that you open a Support incident with Microsoft Product Support
Services so that a dedicated Support Professional can assist with this
case. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.