Forms

[Mellissa via AccessMonster.com]

Hi,

This is a very random question but I am throwing it out there in hopes that
maybe someone will have an idea.

Basically, I have a main data entry form that the processors on my team enter
data upon receiving a client statement.

Data they enter is:

Client Name, SIN, Advisors Name and code, Clients account # etc...

Another field they fill out is FUNDS.

Basically, when they fill out the client information [clients info is only
enterred once] however, one clients account can hold MANY funds.

Currently they are typing each fund # like so 111, 222, 333 etc...in the
field provided or "control" on form view.

However, in order to better our Follow-up processes, I am trying to create a
subform of some sort where they will enter the fund information - each fund
inputted into its own field. The issue is, there is no way for me to know how
many fields to create for data entry purposes as a client can come along and
have 10 funds at one fund company or one fund at 5 fund companies...I cant
know.

Therefore, does anyone have any ideas where I can create a subform of some
sort with unlimited spaces for fund entry, and also a check box beside it so
that follow up people can check that box when that particular fund has been
transferred-in. ???

I know its a monster of a question...but I have been racking my brain so much
this past week to try to come up with the best, most efficient way...but
everything I try just doesnt seem to work out...I hit a dead end each time ;(

Thank you so much !!!
Mellissa

 

[John W. Vinson]

Hi,

This is a very random question but I am throwing it out there in hopes that
maybe someone will have an idea.

Basically, I have a main data entry form that the processors on my team enter
data upon receiving a client statement.

Data they enter is:

Client Name, SIN, Advisors Name and code, Clients account # etc...

Another field they fill out is FUNDS.

Basically, when they fill out the client information [clients info is only
enterred once] however, one clients account can hold MANY funds.

Currently they are typing each fund # like so 111, 222, 333 etc...in the
field provided or "control" on form view.

Ouch. Fields should be atomic.

However, in order to better our Follow-up processes, I am trying to create a
subform of some sort where they will enter the fund information - each fund
inputted into its own field. The issue is, there is no way for me to know how
many fields to create for data entry purposes as a client can come along and
have 10 funds at one fund company or one fund at 5 fund companies...I cant
know.

Therefore, does anyone have any ideas where I can create a subform of some
sort with unlimited spaces for fund entry, and also a check box beside it so
that follow up people can check that box when that particular fund has been
transferred-in. ???

You're using a relational database. Use it relationally! One *FIELD* per fund
is a spreadsheet; you want one *RECORD* per fund.

Consider handling this as a many to many relationship. These are *universal*
in Access and all other relational databases, so now's a good time to get
acquainted!

You need *THREE* tables:

Clients
AccountNo <don't use blanks or # in fieldnames>
<if the AccountNo is unique make it the Primary Key>
LastName
FirstName
SIN
<other account-specific information>

Funds
FundID <surely you have a table of all the valid fund numbers>
FundName <e.g. Acme Explosives and Gag Gifts Fund>

ClientFunds
AccountNo <link to Clients>
FundID <link to Funds>
Quantity
DatePurchased
<any other info about THIS client's purchase of THIS fund>

You would enter as many *records* into ClientFunds as needed, using a Subform
on the Clients form.


John W. Vinson [MVP]

 

[Mellissa via AccessMonster.com]

This worked perfectly.
Thank you so much
M

Hi,

[quoted text clipped - 15 lines]

Currently they are typing each fund # like so 111, 222, 333 etc...in the
field provided or "control" on form view.

Ouch. Fields should be atomic.

However, in order to better our Follow-up processes, I am trying to create a
subform of some sort where they will enter the fund information - each fund
inputted into its own field. The issue is, there is no way for me to know how
many fields to create for data entry purposes as a client can come along and
have 10 funds at one fund company or one fund at 5 fund companies...I cant
know.

Therefore, does anyone have any ideas where I can create a subform of some
sort with unlimited spaces for fund entry, and also a check box beside it so
that follow up people can check that box when that particular fund has been
transferred-in. ???

You're using a relational database. Use it relationally! One *FIELD* per fund
is a spreadsheet; you want one *RECORD* per fund.

Consider handling this as a many to many relationship. These are *universal*
in Access and all other relational databases, so now's a good time to get
acquainted!

You need *THREE* tables:

Clients
AccountNo <don't use blanks or # in fieldnames>
<if the AccountNo is unique make it the Primary Key>
LastName
FirstName
SIN
<other account-specific information>

Funds
FundID <surely you have a table of all the valid fund numbers>
FundName <e.g. Acme Explosives and Gag Gifts Fund>

ClientFunds
AccountNo <link to Clients>
FundID <link to Funds>
Quantity
DatePurchased
<any other info about THIS client's purchase of THIS fund>

You would enter as many *records* into ClientFunds as needed, using a Subform
on the Clients form.

John W. Vinson [MVP]