need some help

[dave@homedeliverygroup]

i have been asked to create a database for our company to use to track our
damage claims... i am the only one in the office that has any DB experience
and that was with office 97... however we have 2003 and I have some knowledge
not enough to develop a full blown massive database. It was simple create DB
to input our claims and be able to print them as well, however after using
the DB people have decided that they want some enhancements. One is to be
able to add an addendum to the a claim. Our claim number is the primary key
only because it was specfic to the delivery and would be unique and not
duplicated however there is a need to add more information to an already
existing claim such as the following: we process the claim and payment and
print it, the work is done to fix the customer problems, however there is now
a need to say repaint the kitchen we have to be able to add this information
to the existing claim.

Can i do this by adding say several subforms on the existing form whereas
the inital claim can be looked up by entering the claim number and that claim
would come up on the form and any additional info would be on the subforms of
the main form?

I am jsut not sure on how to go about adding this request...

thanks...

 

[Jeff Boyce]

If you are trying to start with the forms, you (and Access) will be
wrestling the entire time.

As a relational database, Access "expects" to get well-normalized data. If
"relational" and "well-normalized" are not familiar terms, plan to spend
some time learning your way up that curve before building your database
application.

Start with the entities (things about which you wish to store
data/information) and their inter-relationships (e.g., one student can
enroll in many classes; one class can have many students).

When you have those, design your table structure.

When you have the tables designed, THEN you start on the forms...

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[dave@homedeliverygroup]

Thanks Jeff, however the DB is already in use about 200 records or so.. the
tables were built on information submitted by several people using the DB,
then the form was created and it has been working well just now have the
issue about there is a need to add additional info to a claim that required
more work I guess we could enter the claim as this: original claim would be
6582188 and if there was additional work needed it could go in as 6582188-1
however the two records would not be connected if someone was to look up
6582188 they would not see nor know if there was a -1 record unless they knew
already... not sure if i am making sense or not...
--
thanks for your help

Dave

If you are trying to start with the forms, you (and Access) will be
wrestling the entire time.

As a relational database, Access "expects" to get well-normalized data. If
"relational" and "well-normalized" are not familiar terms, plan to spend
some time learning your way up that curve before building your database
application.

Start with the entities (things about which you wish to store
data/information) and their inter-relationships (e.g., one student can
enroll in many classes; one class can have many students).

When you have those, design your table structure.

When you have the tables designed, THEN you start on the forms...

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[BruceM]

How does the looking-up occur? It should be possible to look up records
based on less than the entire field.

If the Claim Number is a number field you will need to do some manipulation
to get the hyphen to display.

One possibility may be to have a related Addendum table. The foreign key
(linking field) of that table would be the primary key of the main table.
You could increment a number field by 1 for each related record.

It is difficult to be specific without knowing more about the database
structure.

Thanks Jeff, however the DB is already in use about 200 records or so..
the
tables were built on information submitted by several people using the DB,
then the form was created and it has been working well just now have the
issue about there is a need to add additional info to a claim that
required
more work I guess we could enter the claim as this: original claim would
be
6582188 and if there was additional work needed it could go in as
6582188-1
however the two records would not be connected if someone was to look up
6582188 they would not see nor know if there was a -1 record unless they
knew
already... not sure if i am making sense or not...

 

[George Hepworth]

What Jeff was saying is that you need to redesign your tables to support
your workflow. Trying to put a band-aid on it (such as using hyphenated
sub-claim numbers) will NOT work out well.

Each claim can have multiple sub-parts, or sub-claims, or whatever you
choose to call them. That means you have a one-to-many relationship and that
means two tables, one for the claim and one for the sub-claims.

Again, you need to learn more about normalizing your database and use that
to redesign it.

Here's a sample db and a powerpoint presentation that can help you get
started. http://www.gpcdata.com/downloads/saugpresentation.zip

Look for additional resources on normalization. Google will turn up many.

Thanks Jeff, however the DB is already in use about 200 records or so..
the
tables were built on information submitted by several people using the DB,
then the form was created and it has been working well just now have the
issue about there is a need to add additional info to a claim that
required
more work I guess we could enter the claim as this: original claim would
be
6582188 and if there was additional work needed it could go in as
6582188-1
however the two records would not be connected if someone was to look up
6582188 they would not see nor know if there was a -1 record unless they
knew
already... not sure if i am making sense or not...

 

[Ken Sheridan]

Dave:

It seems to me that you have two entity types here, one being the claim,
i.e. the 'case' as a whole, the other being the works associated with a
claim. The claim will have attributes such as the policyholder, policy
number etc, i.e. those attributes which are specific to the claim as a whole.
The works will have attributes such as the type of work, its costs etc and
these will be specific to each set of works rather than the claim a whole.

In a relational database entity types are modelled by tables and their
attributes by the columns (fields) of the tables. So you would have a claims
table with columns for its attributes, and a works table with columns for its
attributes, the two being related in a one-to-many relationship by means of a
foreign key ClaimNumber column in the Works table referencing the primary key
ClaimNumber of the Claims table.

You are right to think in terms of a subform, but you would have only one, a
works subform within a claims form, the two linked on ClaimNumber. All
works, including those associated with the initial claim, would be entered as
records in the subform. Consequently when claim 6582188 is retrieved all the
works associated with it would be shown in the subform and as many additional
works as necessary can be added at any time simply by inserting another
record in the subform.

As it sounds like you have everything in the one table at present you will
need to 'decompose' it into the two tables. This is very simple to do.
having created the Works table you then create an 'append' query which
inserts rows into the works table from your current table. You'd append the
values of the CaseNumber column along with the values from the other columns
which are attributes of the works, and which are now represented by columns
in the works table. You can then create a relationship between the tables,
enforcing referential integrity, cascade updates (the latter to cater for a
claim number being changed after its associated works record(s) had been
entered – this might merely be the correction of a simple error by the user),
and, if appropriate, cascade deletes (this ensures that if a claim record is
deleted the works records associated with it are also deleted).

Once you are satisfied that the works table is correctly populated you can
delete the now redundant columns from the original table.

Finally you'd redesign your form to reflect the tables, deleting the
redundant bound controls from the form and adding the works subform.

You mention that the ability to add further works is only one of the
enhancements proposed. Be sure to look at the totality of these when
considering the modifications to the database which are needed as there may
well be further entity types involved and further relationships with existing
or yet to be created tables. The basis of a solid relational database is
that it is a 'logical model' of the real world entities and the relationships
between them. Getting the logical model right is the key to success. Do so
and the interface will fall into place naturally, but get it wrong and you'll
end up constantly jumping through hoops to get round the defects in the
model. I'd strongly endorse Jeff's advice that you take time to become
familiar with the principles of the database relational model before getting
too deep into the application design.

Ken Sheridan
Stafford, England

Thanks Jeff, however the DB is already in use about 200 records or so.. the
tables were built on information submitted by several people using the DB,
then the form was created and it has been working well just now have the
issue about there is a need to add additional info to a claim that required
more work I guess we could enter the claim as this: original claim would be
6582188 and if there was additional work needed it could go in as 6582188-1
however the two records would not be connected if someone was to look up
6582188 they would not see nor know if there was a -1 record unless they knew
already... not sure if i am making sense or not...

 

[John... Visio MVP]

Dave,

George gave you good advise when he said avoid putting a bandaid on your
current database. I provide help with Access applications for a very
reasonable fee. I would like to help you; my fee would be very modest.
Contact me at (e-mail address removed).

Steve

To bad you can not take good advice.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many experts who gladly give their time to help posters. Stevie is
not one of them, his only purpose is to con people out of money by offering
hollow promises of help. Over the years, he has been the only one who has
constantly ignored the FREE nature of these newsgroups. In the past he has
claimed to have helped thousands, but if that was partially true, his repeat
business would not allow him time to grovel for work. Of course, the lack of
repeat business is a good indication of the "quality" of his work.

John... Visio MVP

 

[BruceM]

Very modest fee to match your abilities, is it?

Dave,

George gave you good advise when he said avoid putting a bandaid on your
current database. I provide help with Access applications for a very
reasonable fee. I would like to help you; my fee would be very modest.
Contact me at (e-mail address removed).

Steve


"dave@homedeliverygroup" <[email protected]>
wrote in message

 

[dave@homedeliverygroup]

--
thanks for your help

Dave

i have been asked to create a database for our company to use to track our
damage claims... i am the only one in the office that has any DB experience
and that was with office 97... however we have 2003 and I have some knowledge
not enough to develop a full blown massive database. It was simple create DB
to input our claims and be able to print them as well, however after using
the DB people have decided that they want some enhancements. One is to be
able to add an addendum to the a claim. Our claim number is the primary key
only because it was specfic to the delivery and would be unique and not
duplicated however there is a need to add more information to an already
existing claim such as the following: we process the claim and payment and
print it, the work is done to fix the customer problems, however there is now
a need to say repaint the kitchen we have to be able to add this information
to the existing claim.

Can i do this by adding say several subforms on the existing form whereas
the inital claim can be looked up by entering the claim number and that claim
would come up on the form and any additional info would be on the subforms of
the main form?

I am jsut not sure on how to go about adding this request...

thanks...

Thanks for all the input guys... we have decided to continue to use the
database that way that it is... until I can re-write it the correct way...
from reading all this it was completely wrong and would require more than a
band-aid to fix or add any enhancements to it...

Dave