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 |
#1
|
|||
|
|||
Basic Database Design
Hi people.
I am attempting to develop a new database and I’m new on Access. It will work (I hope!) like this: Database has 4 Tables: tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice) tblInsurance (InsuranceID;CompanyName;Phone;Fax) and tblClient (Date;ClientName;Category;Product;Insurance;UnitPr ice) I have a list of Twelve Insurance Companies Each Insurance Company furnishes all the products, but with different prices from one Insurance Company to another. I want to create a Form where I insert this data: 1)The Form will look like this: Frm Client based on TblClient Date: ___(Field Date)_______ Name: ___(Field ClientName)_____________________ Age :__(fldAge) ___ And perhaps other fields to identify the client …. Category: __cboCategory____(Rowsource from tblCategory) Product: __cboProduct___ (filtered from the cboCategory) Insurance Company: ____cboInsurance____(Rowsource from tblInsurance) UnitPrice: __txtBox __(which should automatically fills with the Price for that product that the Insurance company that I selected previously is paying) - AND where is where i'm stucked 2)In Date field I’ll type the date of the record. 3)I type the Clients Names (the same client will have different records for different dates) 4)I type all the other fields that identify that particular Client 5)In the cboCategory I select the kind of work I did to that Client (Ex: A-Consultation; B-Surgery; C-Exams ; D-Medications; …) 6)In the cboProduct appear the products filtered by cboCategory I selected (Ex: A-Consultation = 1)Gyne 2)Obst 3)Urgency 4)… B-Surgery = 1)Laparotomy 2)Salpingectomy 3)Hernia repair 4)…. and so on. 7)Then when I select the Insurance Company on the cboInsuranceCompany Ex: AXA Insurance; MedicalInsurance; Doctorhelp;….) the Unit Price txtBox will automatically fills with the price that the Insurance company pays for that product, regardless of the client I inserted. Example1: Date: 16-07-2007 Name: Silvex Age:---- Category: Surgery Product: Hernia repair Supplier: AXA Unit Price: fills automatically according with the price AXA pays for Hernia Repair Example2: Date: 25-06-2007 Name: Carol Stwart Age:42 Category: Consultation Product: Gyne Supplier: Medical Insurance Unit Price: fills automatically according with the price Medical Insurance pays for Gyne Consultation Example3: Date: 5-07-2007 Name: Catherine Boulevard Category: Surgery Product: Hernia Repair Supplier: Medical Insurrance Unit Price: fills automatically according with the price Medical Insurance pays for Hernia Repair Sorry for my bad English, and thank you for any help in advance |
#2
|
|||
|
|||
Basic Database Design
Do you have a question? I didn't see one in your post.
Silvex wrote: Hi people. I am attempting to develop a new database and I’m new on Access. It will work (I hope!) like this: Database has 4 Tables: tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice) tblInsurance (InsuranceID;CompanyName;Phone;Fax) and tblClient (Date;ClientName;Category;Product;Insurance;UnitPr ice) I have a list of Twelve Insurance Companies Each Insurance Company furnishes all the products, but with different prices from one Insurance Company to another. I want to create a Form where I insert this data: 1)The Form will look like this: Frm Client based on TblClient Date: ___(Field Date)_______ Name: ___(Field ClientName)_____________________ Age :__(fldAge) ___ And perhaps other fields to identify the client …. Category: __cboCategory____(Rowsource from tblCategory) Product: __cboProduct___ (filtered from the cboCategory) Insurance Company: ____cboInsurance____(Rowsource from tblInsurance) UnitPrice: __txtBox __(which should automatically fills with the Price for that product that the Insurance company that I selected previously is paying) - AND where is where i'm stucked 2)In Date field I’ll type the date of the record. 3)I type the Clients Names (the same client will have different records for different dates) 4)I type all the other fields that identify that particular Client 5)In the cboCategory I select the kind of work I did to that Client (Ex: A-Consultation; B-Surgery; C-Exams ; D-Medications; …) 6)In the cboProduct appear the products filtered by cboCategory I selected (Ex: A-Consultation = 1)Gyne 2)Obst 3)Urgency 4)… B-Surgery = 1)Laparotomy 2)Salpingectomy 3)Hernia repair 4)…. and so on. 7)Then when I select the Insurance Company on the cboInsuranceCompany Ex: AXA Insurance; MedicalInsurance; Doctorhelp;….) the Unit Price txtBox will automatically fills with the price that the Insurance company pays for that product, regardless of the client I inserted. Example1: Date: 16-07-2007 Name: Silvex Age:---- Category: Surgery Product: Hernia repair Supplier: AXA Unit Price: fills automatically according with the price AXA pays for Hernia Repair Example2: Date: 25-06-2007 Name: Carol Stwart Age:42 Category: Consultation Product: Gyne Supplier: Medical Insurance Unit Price: fills automatically according with the price Medical Insurance pays for Gyne Consultation Example3: Date: 5-07-2007 Name: Catherine Boulevard Category: Surgery Product: Hernia Repair Supplier: Medical Insurrance Unit Price: fills automatically according with the price Medical Insurance pays for Hernia Repair Sorry for my bad English, and thank you for any help in advance -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Basic Database Design
"Silvex" u36021@uwe wrote in message news:758166b4acb83@uwe... Hi people. I am attempting to develop a new database and I’m new on Access. It will work (I hope!) like this: Database has 4 Tables: tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice) Silvex, I see that each product may only have one category. This may be what you intended, but in most businesses products can have more than one category, so I just wanted to point this out. If it turns out that your products may have multiple categories, you will need a new table, tblProductCategories (or, preferrably, ProductCategories), that has: ProductCategoryID -- PK ProductID --\ CategoryID -- 2-column unique index. tblInsurance (InsuranceID;CompanyName;Phone;Fax) and tblClient (Date;ClientName;Category;Product;Insurance;UnitPr ice) I have a list of Twelve Insurance Companies Each Insurance Company furnishes all the products, but with different prices from one Insurance Company to another. I want to create a Form where I insert this data: 1)The Form will look like this: Frm Client based on TblClient Date: ___(Field Date)_______ Name: ___(Field ClientName)_____________________ Age :__(fldAge) ___ And perhaps other fields to identify the client …. Category: __cboCategory____(Rowsource from tblCategory) Product: __cboProduct___ (filtered from the cboCategory) Insurance Company: ____cboInsurance____(Rowsource from tblInsurance) UnitPrice: __txtBox __(which should automatically fills with the Price for that product that the Insurance company that I selected previously is paying) - AND where is where i'm stucked Forms questions belong in: microsoft.public.access.forms microsoft.public.access.formscoding Example1: snip example 1 Example2: snip example 2 Example3: snip example 3 I most sincerely hope that none of that is private medical information that belongs to real people, but it sort of looks real, which makes me wonder . . . Sincerely, Chris O. |
#4
|
|||
|
|||
Basic Database Design
Hi! jahoobob
Thanks for your reply. Probably I wrote to much and I did'nt explain well. Sorry. My problem ( where I'm stucked..) is how to fill the 3 comboBox in the formClient in order to automatic fill the TxtBox UnitPrice after update the cboInsurance, with the price for that Category/Product/InsurranceCompany. Thus this make any sense to you !!! Hope so!! Regards Silvex jahoobob wrote: Do you have a question? I didn't see one in your post. Hi people. I am attempting to develop a new database and I’m new on Access. [quoted text clipped - 62 lines] Sorry for my bad English, and thank you for any help in advance |
#5
|
|||
|
|||
Basic Database Design
Thanks Chris2 for your concern.
Don't worry, the examples I give are not real. I invented them just for better expose my doubts OK. The products only belong to a Category One Category - several products One Product - One Category I adress this in this group because I think is a question of Tables/Forms/queries so Database Design Sorry if I'm wrong. regards Silvex Chris2 wrote: Hi people. I am attempting to develop a new database and I’m new on Access. It will work (I hope!) like this: Database has 4 Tables: tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice) Silvex, I see that each product may only have one category. This may be what you intended, but in most businesses products can have more than one category, so I just wanted to point this out. If it turns out that your products may have multiple categories, you will need a new table, tblProductCategories (or, preferrably, ProductCategories), that has: ProductCategoryID -- PK ProductID --\ CategoryID -- 2-column unique index. tblInsurance (InsuranceID;CompanyName;Phone;Fax) and tblClient (Date;ClientName;Category;Product;Insurance;UnitPr ice) [quoted text clipped - 15 lines] that product that the Insurance company that I selected previously is paying) - AND where is where i'm stucked Forms questions belong in: microsoft.public.access.forms microsoft.public.access.formscoding Example1: snip example 1 Example2: snip example 2 Example3: snip example 3 I most sincerely hope that none of that is private medical information that belongs to real people, but it sort of looks real, which makes me wonder . . . Sincerely, Chris O. |
#6
|
|||
|
|||
Basic Database Design
"Silvex" u36021@uwe wrote in message news:7583145993c9e@uwe... snip I adress this in this group because I think is a question of Tables/Forms/queries so Database Design Sorry if I'm wrong. regards Silvex Silvex, You're not wrong to post here. Forms questions are posted here, and get answered here, all the time. I was just making a recommendation. As for the table designs, the first and third look ok. As for the second, tblProducts, InsuranceID needs to be split out into a new table. ProductInsurance ProductInsuranceID -- PK ProductID -- FK to Products (ProductID) -----\ InsuranceID -- FK to Insurance (InsuranceID) -\ ProductInsuranceDate -------------------------- 3-column unique index. As for the fourth, tblClient, it combines client data with product, category, and Insurance data all at the same time. Category information is already contained in tblCategory and tblProduct You need three separate tables, Clients, ClientProducts , and ClientInsurance. Clients ClientID -- PK ClientNamePrefix ClientNameFirst ClientNameMiddle ClientNameLast ClientNameSuffix ClientProducts ClientProductsID -- PK ClientID --- FK to Clients (ClientID) --\ ProductID -- FK to Products (ProductID) -\ SaleDate --------------------------------- 3-column unique index. ClientInsurance ClientInsuranceID -- PK ClientID --- FK to Clients (ClientID) -------\ InsuranceID -- FK to Insurance (InsuranceID) -\ InsuranceDate --------------------------------- 3-column unique index. I personally would use InsuranceStart and InsuranceEnd, so I would know how long they were insured under a particular insurance. (And probably two dates for ProductInsurance, as well.) Oh, I just noticed I stopped using the "tbl" prefixes in the middle of writing that. Also, you have the UnitPrice column in both tblProduct (where it appears to belong), and in tblClient (where it does not belong, as UnitPrice does not describe a client). I would also add at least one date column to tblProduct, so you can track when prices change. Sincerely, Chris O. |
#7
|
|||
|
|||
Basic Database Design
Chris Thanks again.
I will try to do the design that you have send, and will "post" you a note in order to tell you if it worked. OK Regards Silvex Chris2 wrote: snip I adress this in this group because I think is a question of Tables/Forms/queries so Database Design Sorry if I'm wrong. regards Silvex Silvex, You're not wrong to post here. Forms questions are posted here, and get answered here, all the time. I was just making a recommendation. As for the table designs, the first and third look ok. As for the second, tblProducts, InsuranceID needs to be split out into a new table. ProductInsurance ProductInsuranceID -- PK ProductID -- FK to Products (ProductID) -----\ InsuranceID -- FK to Insurance (InsuranceID) -\ ProductInsuranceDate -------------------------- 3-column unique index. As for the fourth, tblClient, it combines client data with product, category, and Insurance data all at the same time. Category information is already contained in tblCategory and tblProduct You need three separate tables, Clients, ClientProducts , and ClientInsurance. Clients ClientID -- PK ClientNamePrefix ClientNameFirst ClientNameMiddle ClientNameLast ClientNameSuffix ClientProducts ClientProductsID -- PK ClientID --- FK to Clients (ClientID) --\ ProductID -- FK to Products (ProductID) -\ SaleDate --------------------------------- 3-column unique index. ClientInsurance ClientInsuranceID -- PK ClientID --- FK to Clients (ClientID) -------\ InsuranceID -- FK to Insurance (InsuranceID) -\ InsuranceDate --------------------------------- 3-column unique index. I personally would use InsuranceStart and InsuranceEnd, so I would know how long they were insured under a particular insurance. (And probably two dates for ProductInsurance, as well.) Oh, I just noticed I stopped using the "tbl" prefixes in the middle of writing that. Also, you have the UnitPrice column in both tblProduct (where it appears to belong), and in tblClient (where it does not belong, as UnitPrice does not describe a client). I would also add at least one date column to tblProduct, so you can track when prices change. Sincerely, Chris O. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200707/1 |
#8
|
|||
|
|||
Basic Database Design
"Silvex via AccessMonster.com" u36021@uwe wrote in message news:758432d250800@uwe... Chris Thanks again. I will try to do the design that you have send, and will "post" you a note in order to tell you if it worked. OK Regards Silvex Silvex, The table designs I provided were suggestions. You need to be able to review them for applicability to your situation, and modify or extend them as necessary. To do that, you'll need some further knowledge on the subject of database design. Books: General: Beginner Database Design for Mere Mortals by Michael J. Hernandez SQL Queries for Mere Mortals by Michael J. Hernandez, John L. Viescas Database Normalization: Basics: About.com http://databases.about.com/od/specif...malization.htm Tips to Get You Going http://home.att.net/~california.db/tips.html#aTip1 Microsoft: Description of database normalization basics in Access 2000 (not significantly changed by Access 2007, see the article's own references at the end to material from the early 1990s). http://support.microsoft.com/support.../q209/5/34.asp Intermediate: MySQL's website: http://dev.mysql.com/tech-resources/...alization.html Advanced: Wikipedia: http://en.wikipedia.org/wiki/Database_normalization Very Advanced: University of Texas: I like this whole site, since it has a handy menu on the right describing many important aspects of the database world: http://www.utexas.edu/its/windows/da...ng/rm/rm7.html Sincerely, Chris O. |
#9
|
|||
|
|||
Basic Database Design
Shi! Chris.
Now you really scare me. I thought it was a little and basic thing to do. I have no time to study all that books but I will visit those sites you post me. I was already thinking if the struture and the alterations that you send me previously, where enought to solve my problem whitch is, 3 comboBoxes in the form (cboCategory, cboProduct,cboInsurance) On cboCategory I select the Category .One category have several products, so on the CboProducts I will select the proctuct from the Category I select previously ( I think I know how to do the code for filter cboProducts to the Category selected on cboCategory). Now, the Client has a Insurance Company (always the same, doesn't change). There are various Insurance companies and all fournish all the products at diferent prices. What I wish is that when I select the Insurance company (on the third comboBox - cboInsurance) that the client I'm recording is associate, the price that this company pays for the product I select on the cboProduct, will automatically appears on the UnitPrice Txtfield. regards Silvex Chris2 wrote: Chris Thanks again. I will try to do the design that you have send, and will "post" you a note in order to tell you if it worked. OK Regards Silvex Silvex, The table designs I provided were suggestions. You need to be able to review them for applicability to your situation, and modify or extend them as necessary. To do that, you'll need some further knowledge on the subject of database design. Books: General: Beginner Database Design for Mere Mortals by Michael J. Hernandez SQL Queries for Mere Mortals by Michael J. Hernandez, John L. Viescas Database Normalization: Basics: About.com http://databases.about.com/od/specif...malization.htm Tips to Get You Going http://home.att.net/~california.db/tips.html#aTip1 Microsoft: Description of database normalization basics in Access 2000 (not significantly changed by Access 2007, see the article's own references at the end to material from the early 1990s). http://support.microsoft.com/support.../q209/5/34.asp Intermediate: MySQL's website: http://dev.mysql.com/tech-resources/...alization.html Advanced: Wikipedia: http://en.wikipedia.org/wiki/Database_normalization Very Advanced: University of Texas: I like this whole site, since it has a handy menu on the right describing many important aspects of the database world: http://www.utexas.edu/its/windows/da...ng/rm/rm7.html Sincerely, Chris O. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200707/1 |
#10
|
|||
|
|||
Basic Database Design
I have a list of Twelve Insurance Companies
Each Insurance Company furnishes all the products, but with different prices from one Insurance Company to another. okay, having read your further explanation as well, we're actually talking about services paid for by insurance companies. so one service may be paid for by many companies, and one company may pay for many services. also, one patient may receive many services (with payment from one or many companies), and one service (from any company) may be received by many patients. these are many-to-many relationships, which are not correctly represented in your posted table design. suggest the following, as tblCategories CatID (primary key) CatName CatDescription tblServices ServID (pk) ServName CatID (foreign key from tblCategories) tblInsCompanies CoID (pk) CoName CoPhone CoFax i'll go with that setup, though it's not normalized; proper design would call for a communications table (linked to tblInsCompanies) where you can enter multiple records for each company - phone, fax, email, website, etc. or at the very least, a simple phone number table, linked as noted above. tblCompanyServices CoServID (pk) CoID (fk from tblInsCompanies) ServID (fk from tblServices) UnitPrice you don't need a category field in this table, because each service is already linked to a specific category, in tblServices. note that you can dispense with the CoServID primary key field, and use the two foreign key fields as a combination pk. i prefer to not do that, especially when the table's primary key will be used as a foreign key in another table, as below. tblPatients PatID (pk) PatFirstName PatLastName PatDOB (don't store a patient's *age*, because that keeps changing. store the date of birth, and you can calculate the age whenever and wherever needed.) other fields that describe a patient only, no data about the services received by a patient. tblPatientServices PatServID (pk) ServiceDate PatID (fk from tblPatients) CoServID (fk from tblCompanyServices) Cost this situation calls for bending the normalization rule re duplicating data. cost for each service should be stored in the service record, because over time the price that each insurance company pays for a given service will change - but the price paid for a specific service to a specific patient in the past should not change, so it must be hard-coded in this table. but, much better than just going with the above design, suggest you read up (or read more) on relational design principles before proceeding. correct tables/relationships design is critical to successful database development, so it really pays to invest the time up front to get it right. and, armed with an understanding of relational design principles, you're the best person to structure your database - because you know more about your business process than you can hope to tell us in this forum. for more information, see http://home.att.net/~california.db/tips.html#aTip1. last but not least, this is an *extremely* simplified medical services tracking/billing structure. if it truly meets your needs, that's fine; but in a real-world medical practice, i have to think that one of the many commercial products on the market would serve you better. you also need to consider whether this, or any, setup you use is in compliance with the HIPAA patient data security requirements (if you're in the USA, that is). hth "Silvex" u36021@uwe wrote in message news:758166b4acb83@uwe... Hi people. I am attempting to develop a new database and I'm new on Access. It will work (I hope!) like this: Database has 4 Tables: tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice) tblInsurance (InsuranceID;CompanyName;Phone;Fax) and tblClient (Date;ClientName;Category;Product;Insurance;UnitPr ice) I have a list of Twelve Insurance Companies Each Insurance Company furnishes all the products, but with different prices from one Insurance Company to another. I want to create a Form where I insert this data: 1)The Form will look like this: Frm Client based on TblClient Date: ___(Field Date)_______ Name: ___(Field ClientName)_____________________ Age :__(fldAge) ___ And perhaps other fields to identify the client .. Category: __cboCategory____(Rowsource from tblCategory) Product: __cboProduct___ (filtered from the cboCategory) Insurance Company: ____cboInsurance____(Rowsource from tblInsurance) UnitPrice: __txtBox __(which should automatically fills with the Price for that product that the Insurance company that I selected previously is paying) - AND where is where i'm stucked 2)In Date field I'll type the date of the record. 3)I type the Clients Names (the same client will have different records for different dates) 4)I type all the other fields that identify that particular Client 5)In the cboCategory I select the kind of work I did to that Client (Ex: A-Consultation; B-Surgery; C-Exams ; D-Medications; .) 6)In the cboProduct appear the products filtered by cboCategory I selected (Ex: A-Consultation = 1)Gyne 2)Obst 3)Urgency 4). B-Surgery = 1)Laparotomy 2)Salpingectomy 3)Hernia repair 4).. and so on. 7)Then when I select the Insurance Company on the cboInsuranceCompany Ex: AXA Insurance; MedicalInsurance; Doctorhelp;..) the Unit Price txtBox will automatically fills with the price that the Insurance company pays for that product, regardless of the client I inserted. Example1: Date: 16-07-2007 Name: Silvex Age:---- Category: Surgery Product: Hernia repair Supplier: AXA Unit Price: fills automatically according with the price AXA pays for Hernia Repair Example2: Date: 25-06-2007 Name: Carol Stwart Age:42 Category: Consultation Product: Gyne Supplier: Medical Insurance Unit Price: fills automatically according with the price Medical Insurance pays for Gyne Consultation Example3: Date: 5-07-2007 Name: Catherine Boulevard Category: Surgery Product: Hernia Repair Supplier: Medical Insurrance Unit Price: fills automatically according with the price Medical Insurance pays for Hernia Repair Sorry for my bad English, and thank you for any help in advance |
|
Thread Tools | |
Display Modes | |
|
|