Attention Ken Sheridan

Hey Ken i picked up MS Office Access 2003... since that is the version my
company is using ... thanks for the head start...



--
thanks for your help

Dave


"dave@homedeliverygroup" wrote:

Ken,
First off thanks for the time that you have put into your replies...


the one reason why i had put it into one table is that even though some of
the fields are not used they are all printed out on a report...

(BTW we are a delivery company for Lowes)

You have broke down my table this way
Claims (1 -9)
Customers (10-19)
WorkProgramme (20 – 25)
Materials (26 -30)
Vehicles (31 – 34)

you were almost correct in your description 1-9 is the claim info and what
hub it was out of(our hubs are mainly on the east coast) as well as what
store and the loacation of that store, which is a drop down(storesTBL) list
containg all our store information, 10-19 is the customers info where the
damage happened. 20-25 is actually the delivery contractor that caused the
damage, how much he needs to repay(as we pay off all the claims then deduct
from our contractors), the number of weeks the payoff will be deducted till
paid back in full and a brief description of the damage... 26-30 is actually
info for a merchadise damage claim if our contractor damages the merchandise
then the cost to replace the damaged merchandise would also go into the 20-25
section... 31-34 is if the contractor has an accident involving another auto,
or hits something with the delviery truck again the cost to pay back would go
into the 20-25 section. So section 1-9 is always entered as is 10-19, and
20-25... 26-30 only if merchandise damage, and 31-34 only if an accident has
occured.

I am not sure what you mean by the city and state and having a seperate city
and state table as we deliver to hundreds of citys across several states i do
see what you mean as the same city could be entered in New York as well as
Pennsylvania. Would that mean i would have to create a city/state table and
enter the info or create them and let the data entry build the tables????

Also the addresses and telphone numbers you mentioned to have a separate
addresses table that references the customers table is way beyond me...more
reading on that for me.

Currenlty there is not another person in our company that has any Access
experience other than myself and i only had it when i was in college which
was a bit of time ago... so i am moving forward myself with plenty of
reading... the DB that i have created will work for the time being, however i
want to be able to enhance, make changes and make it better functional wise i
see the flaws in it now. for instance i have all 200 of our stores in one
table... LOL one table again... must be my way of thinking... i tried to get
it to work where when a person entered the market_hub the store_and_location
only listed the stores for that hub... sounded simple, but did not work... we
have currently 10 hubs so i had 10 stores tables which made sense but then i
just entered all the info into one table... but as i read and re-read your
"stock" summaries somethings are coming to light...

dave





--
thanks for your help

Dave


"dave@homedeliverygroup" wrote:

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

Ken thanks for you above reply you are correct i have all the info in one
table as follows...

one table name is hdg_claimTBL

1. today_date
2. hdg_ticket_number (primary key)
3. claim_status (from drop down list)
4. date_of_occurence
5. market_hub (from drop down list)
6. store_and_location (from drop down list)
7. checkpayableto (from drop down list)
8. claimType(from drop down list)
9. original_invoice
----------------------------------------------------------------
10. first_name
11. last_name
12. address
13. second_address
14. apartment_number
15. city
16. state
17. zip_code
18. telephone
19. alt_telephone
------------------------------------------------------------------------------
20. contractor_name
21. driver_name
22. deduction_amount
23. repayment_amount
24. number_of_weeks
25. description_of_claim
------------------------------------------------------------------------------
26. sku_number
27. model_number
28. merch_description
29. item_retail_cost
30. item_cost
-----------------------------------------------------------------------------
31. year
32. make
33. model
34. vin_number

as you can see it is a very larger tabel(34 items)... as you stated... now
according to "normalization" there are no duplicative columns unrelated to
the ticket number, however i do see where i can separate the table down into
five different tables of related data as i serperated them with the dashed
lines... does that make more sense? I also realize now that i can't make the
ticket number the primary key...
--
thanks for your help

Dave

--
thanks for your help

Dave

Thanks, Ken. Obviously, I missed that!

Regards

Jeff B.
"Ken Sheridan" wrote in message
...
Jeff:

I obvious wasn't clear enough. That's exactly what I was saying. I
invented the hypothetical San Francisco in New Hampshire to illustrate
that
my first non-3NF table might not necessarily have been incorrect in terms
of
its content. The point I was making was that our Martian visitor would
not
know for certain whether there are two San Franciscos or not from the
original table, but if its decomposed and there were two San Francisco
rows
in Cities with different CityID and State values then it would be clear
that
there are two, hypothetically speaking.

Somewhere along the line I pointed out that there are four Stafford's in
the
USA, all presumably originally named after this little one horse town
where I
am.

Ken Sheridan
Stafford, England

"Jeff Boyce" wrote:

Ken

I can only locate one city named "San Francisco", and it is in
California.

But if you look up the city named "Salem", there's one in Oregon, one in
Illinois, one in Massachusetts, one in ...

Going only by city name is NOT guaranteed to provide unique cities.

(or maybe I'm mis-interpreting your description...)

Regards

Jeff Boyce

Ken if you read this again... i can send you a copy of the DB you could then
see the business aspect of it... it is a simple DB...

is my email... address...



--
thanks for your help

Dave


"dave@homedeliverygroup" wrote:

Hey Ken i picked up MS Office Access 2003... since that is the version my
company is using ... thanks for the head start...



--
thanks for your help

Dave


"dave@homedeliverygroup" wrote:

Ken,
First off thanks for the time that you have put into your replies...


the one reason why i had put it into one table is that even though some of
the fields are not used they are all printed out on a report...

(BTW we are a delivery company for Lowes)

You have broke down my table this way
Claims (1 -9)
Customers (10-19)
WorkProgramme (20 – 25)
Materials (26 -30)
Vehicles (31 – 34)

you were almost correct in your description 1-9 is the claim info and what
hub it was out of(our hubs are mainly on the east coast) as well as what
store and the loacation of that store, which is a drop down(storesTBL) list
containg all our store information, 10-19 is the customers info where the
damage happened. 20-25 is actually the delivery contractor that caused the
damage, how much he needs to repay(as we pay off all the claims then deduct
from our contractors), the number of weeks the payoff will be deducted till
paid back in full and a brief description of the damage... 26-30 is actually
info for a merchadise damage claim if our contractor damages the merchandise
then the cost to replace the damaged merchandise would also go into the 20-25
section... 31-34 is if the contractor has an accident involving another auto,
or hits something with the delviery truck again the cost to pay back would go
into the 20-25 section. So section 1-9 is always entered as is 10-19, and
20-25... 26-30 only if merchandise damage, and 31-34 only if an accident has
occured.

I am not sure what you mean by the city and state and having a seperate city
and state table as we deliver to hundreds of citys across several states i do
see what you mean as the same city could be entered in New York as well as
Pennsylvania. Would that mean i would have to create a city/state table and
enter the info or create them and let the data entry build the tables????

Also the addresses and telphone numbers you mentioned to have a separate
addresses table that references the customers table is way beyond me...more
reading on that for me.

Currenlty there is not another person in our company that has any Access
experience other than myself and i only had it when i was in college which
was a bit of time ago... so i am moving forward myself with plenty of
reading... the DB that i have created will work for the time being, however i
want to be able to enhance, make changes and make it better functional wise i
see the flaws in it now. for instance i have all 200 of our stores in one
table... LOL one table again... must be my way of thinking... i tried to get
it to work where when a person entered the market_hub the store_and_location
only listed the stores for that hub... sounded simple, but did not work... we
have currently 10 hubs so i had 10 stores tables which made sense but then i
just entered all the info into one table... but as i read and re-read your
"stock" summaries somethings are coming to light...

dave





--
thanks for your help

Dave


"dave@homedeliverygroup" wrote:

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

Ken thanks for you above reply you are correct i have all the info in one
table as follows...

one table name is hdg_claimTBL

1. today_date
2. hdg_ticket_number (primary key)
3. claim_status (from drop down list)
4. date_of_occurence
5. market_hub (from drop down list)
6. store_and_location (from drop down list)
7. checkpayableto (from drop down list)
8. claimType(from drop down list)
9. original_invoice
----------------------------------------------------------------
10. first_name
11. last_name
12. address
13. second_address
14. apartment_number
15. city
16. state
17. zip_code
18. telephone
19. alt_telephone
------------------------------------------------------------------------------
20. contractor_name
21. driver_name
22. deduction_amount
23. repayment_amount
24. number_of_weeks
25. description_of_claim
------------------------------------------------------------------------------
26. sku_number
27. model_number
28. merch_description
29. item_retail_cost
30. item_cost
-----------------------------------------------------------------------------
31. year
32. make
33. model
34. vin_number

as you can see it is a very larger tabel(34 items)... as you stated... now
according to "normalization" there are no duplicative columns unrelated to
the ticket number, however i do see where i can separate the table down into
five different tables of related data as i serperated them with the dashed
lines... does that make more sense? I also realize now that i can't make the
ticket number the primary key...
--
thanks for your help

Dave

--
thanks for your help

Dave