Pushing the envelope on large sets of data

[Chris Hayes]

First, this is a handful of question's I'm asking and I'm just asking for
suggestions. I understand this is my puppy but some of these issues are
troubling me.

I'm updating an Access 97 database. I'm working with a "contribution"
database. I've got over 10k contacts with 25k related records. We're
working with 5 to 6 users over a network. The end product is going to be
split. Here's some of my current concerns:

1) any filter that I run on the main form causes a delay of form load up to
30 seconds.

2) I built a form specifically entering contributions. The major factor is
that each contribution is attached to a contributor. A contributor could
have one contribution for life or a lot of contributions in one month. How
do I reduce data entry or screen contributors (if possible.)

3) In the original form users had to search (ctrl + F) for an address line
to find a contributor. Now, to enable selection of contributor (if they
currently exist in the db) I am using a combo box whose first column is the
address line (it's the fastest way I could think of locating a current
contact, forbid they moved.) I want to use a "not in list" event to create a
pop up modal form for quick entry of the new contributor. Does this sound
like the right answer?

I have more questions but if any of you are game to play with these ideas
and jot me a line I'd appreciate it much.

The situation is this:
--
Chris Hayes
Still a beginner (only 12 years)

[fyi, you can email me by getting rid of the British Comedian''s name who
wrote that song about "Spam"]

 

[Steve]

Q1 ---
Base the main form on a query - should speed up load time compared to a
filter.

Q2 ---
Sounds like you need to change table structure
TblContributor
ContributorID
Contributorname
etc

TblContribution
ContributionID
ContributorID
ContributionDate
ContributionAmount

Q3 ---
Create a query based on TblContributor
Include the fields in this order: Contributorid, ContributorLastName,
ContributorFirstName
Sort ascending on ContributorFirstName
Set the Autoexpand property to Yes.
As you enter letters in the combobox, the list will automatically scroll
to the first last name beginning with the letters you entered. The user
needs to enter just enough letters to get to the contributor he wants.
Write code in the AfterUpdate event that will cause your form to jump to
the contributorid selected in your combobox.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Chris Hayes]

Steve, thank you

I've already done all of these sugessions. Anyone else game?
--
Chris Hayes
Still a beginner (only 12 years)

[fyi, you can email me by getting rid of the British Comedian''s name who
wrote that song about "Spam"]

Q1 ---
Base the main form on a query - should speed up load time compared to a
filter.

Q2 ---
Sounds like you need to change table structure
TblContributor
ContributorID
Contributorname
etc

TblContribution
ContributionID
ContributorID
ContributionDate
ContributionAmount

Q3 ---
Create a query based on TblContributor
Include the fields in this order: Contributorid, ContributorLastName,
ContributorFirstName
Sort ascending on ContributorFirstName
Set the Autoexpand property to Yes.
As you enter letters in the combobox, the list will automatically scroll
to the first last name beginning with the letters you entered. The user
needs to enter just enough letters to get to the contributor he wants.
Write code in the AfterUpdate event that will cause your form to jump to
the contributorid selected in your combobox.


PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

First, this is a handful of question's I'm asking and I'm just asking for
suggestions. I understand this is my puppy but some of these issues are
troubling me.

I'm updating an Access 97 database. I'm working with a "contribution"
database. I've got over 10k contacts with 25k related records. We're
working with 5 to 6 users over a network. The end product is going to be
split. Here's some of my current concerns:

1) any filter that I run on the main form causes a delay of form load up
to
30 seconds.

2) I built a form specifically entering contributions. The major factor
is
that each contribution is attached to a contributor. A contributor could
have one contribution for life or a lot of contributions in one month.
How
do I reduce data entry or screen contributors (if possible.)

3) In the original form users had to search (ctrl + F) for an address line
to find a contributor. Now, to enable selection of contributor (if they
currently exist in the db) I am using a combo box whose first column is
the
address line (it's the fastest way I could think of locating a current
contact, forbid they moved.) I want to use a "not in list" event to
create a
pop up modal form for quick entry of the new contributor. Does this sound
like the right answer?

I have more questions but if any of you are game to play with these ideas
and jot me a line I'd appreciate it much.

The situation is this:
--
Chris Hayes
Still a beginner (only 12 years)

[fyi, you can email me by getting rid of the British Comedian''s name who
wrote that song about "Spam"]

 

[Tim Ferguson]

1) any filter that I run on the main form causes a delay of form load
up to 30 seconds

The end product is going to be
split.

If you are sharing a monolithic mdb, then don't. It's slow and it's
unsafe. Use one mdb for the data and give each user its own copy of the
front end on the local machine.

Make sure you have indexes set up on fields that you are use for
selecting or sorting.

2) I built a form specifically entering contributions. The major
factor is that each contribution is attached to a contributor. A
contributor could have one contribution for life or a lot of
contributions in one month. How do I reduce data entry or screen
contributors (if possible.)

Ask your users. I can't visualise how it's working at the moment; but
only they will be able to tell you the best way. Things that may help:-

- You can do drag-and-drop in Access, but it has to be programmed in VBA
and is a pain. Lots of resources to help on the web

- Use a continuous-forms form with a check box to mark lots of records
and then use a query to do the insert into the destination table

- You can use a listbox with multiple-selection to do the same sort of
thing.

3) In the original form users had to search (ctrl + F) for an address
line to find a contributor. Now, to enable selection of contributor
(if they currently exist in the db) I am using a combo box whose first
column is the address line (it's the fastest way I could think of
locating a current contact, forbid they moved.) I want to use a "not
in list" event to create a pop up modal form for quick entry of the
new contributor. Does this sound like the right answer?

I often use a separate [New] command button because it seems more like
Windows UI standards; again it should be your users you are asking, not
us!

Hope that helps


Tim F

 

[Chris Hayes]

Steve's help was very useful and Tim's help greatly increased the speed. The
indexing was what was really missing. Now the two search combo boxes run
like a champ do to the expert help here.
--
Chris Hayes
Still a beginner (only 12 years)

[fyi, you can email me by getting rid of the British Comedian''s name who
wrote that song about "Spam"]

1) any filter that I run on the main form causes a delay of form load
up to 30 seconds

The end product is going to be
split.

If you are sharing a monolithic mdb, then don't. It's slow and it's
unsafe. Use one mdb for the data and give each user its own copy of the
front end on the local machine.

Make sure you have indexes set up on fields that you are use for
selecting or sorting.

2) I built a form specifically entering contributions. The major
factor is that each contribution is attached to a contributor. A
contributor could have one contribution for life or a lot of
contributions in one month. How do I reduce data entry or screen
contributors (if possible.)

Ask your users. I can't visualise how it's working at the moment; but
only they will be able to tell you the best way. Things that may help:-

- You can do drag-and-drop in Access, but it has to be programmed in VBA
and is a pain. Lots of resources to help on the web

- Use a continuous-forms form with a check box to mark lots of records
and then use a query to do the insert into the destination table

- You can use a listbox with multiple-selection to do the same sort of
thing.

3) In the original form users had to search (ctrl + F) for an address
line to find a contributor. Now, to enable selection of contributor
(if they currently exist in the db) I am using a combo box whose first
column is the address line (it's the fastest way I could think of
locating a current contact, forbid they moved.) I want to use a "not
in list" event to create a pop up modal form for quick entry of the
new contributor. Does this sound like the right answer?

I often use a separate [New] command button because it seems more like
Windows UI standards; again it should be your users you are asking, not
us!

Hope that helps


Tim F