If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Attention Ken Sheridan
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 |
#13
|
|||
|
|||
Attention Ken Sheridan
|
|
Thread Tools | |
Display Modes | |
|
|