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
|
|||
|
|||
database design
i need to create a database that will document visits to people, at different
locations, on different days. the summary report needs to include the days visited and infrom from each event (what was discussed, supplies used, details regarding the person visited that day, etc). i suspect i need the following, but may need subforms(?): table - personnel (names of people visited, their address, etc) table - details regarding visit (what was discussed, etc) i'm not sure, but i suspect i need other tables, also aagain, i would like to create a summary report at the end of the month for each person, listing their name, their details, as well as dates of visit(s) and details of visits thanks for any help! -- cg acker |
#2
|
|||
|
|||
CG -
You're right - storing your data in a couple tables is the best move. Keep in mind though - Forms themselves don't store data, tables do Forms are what display your data in a way that is meaningful and understandable. Having a good table structure is like having a good foundation in a house... Without it - everything will eventually fall apart, and will never be as flexible as you might wish... you get the picture.... For a project like this, you are just about right on the mark. I would set up tables in something similar to the following format: 1. Clients 1. Client ID (Primary Key) 2. First Name 3. Last Name 4. Street Address 5..... (other fields relating to how to find/contact/locate a client) The goal of this table should be to have 1 record for each client. Your Client ID field is probably not ever going to be seen by your user (unless you want to make Name be one field, in which case, that can be your ClientID since it's not likely you'll ever have 2 clients with the same name. If this is possible, leave the numerical ID in or come up with some way to tell the identical names apart, and use that for your ID.) 2. Visits 1. Visit ID (Primary Key) 2. Client ID (Foreign Key) 3. Date/Time 4. .... any other fields relating to what happened at that visit. The trick to making all this work is having the foreign key. This is what associates a visit with a client, and the reason WHY client ID has to be unique. You can't be in two places at once... :-) if you have information that doesn't really fit into either catagory of Visit Information or Client Information - but is related to one of the two, use the concept of Foreign Keys again to link that table to whatever else you need to create. For Example... if you are going to track travel expenses for each visit - you might make an Expenses table that has Visit ID as a foreign key (since if you wanted to be really picky, you have 2 trips for each visit... one each direction) I hope that helps! You are defenitely on the right track in thinking things through! If you get stuck building forms and reports to read and edit your data - check out those two sections of the Discussion Group, and holler if you have more questions! Happy Designing! Amanda "cgacker" wrote: i need to create a database that will document visits to people, at different locations, on different days. the summary report needs to include the days visited and infrom from each event (what was discussed, supplies used, details regarding the person visited that day, etc). i suspect i need the following, but may need subforms(?): table - personnel (names of people visited, their address, etc) table - details regarding visit (what was discussed, etc) i'm not sure, but i suspect i need other tables, also aagain, i would like to create a summary report at the end of the month for each person, listing their name, their details, as well as dates of visit(s) and details of visits thanks for any help! -- cg acker |
#3
|
|||
|
|||
amanda-
thanks very much for the help. i will post further if any problems arise. cga "Amanda Payton" wrote: CG - You're right - storing your data in a couple tables is the best move. Keep in mind though - Forms themselves don't store data, tables do Forms are what display your data in a way that is meaningful and understandable. Having a good table structure is like having a good foundation in a house... Without it - everything will eventually fall apart, and will never be as flexible as you might wish... you get the picture.... For a project like this, you are just about right on the mark. I would set up tables in something similar to the following format: 1. Clients 1. Client ID (Primary Key) 2. First Name 3. Last Name 4. Street Address 5..... (other fields relating to how to find/contact/locate a client) The goal of this table should be to have 1 record for each client. Your Client ID field is probably not ever going to be seen by your user (unless you want to make Name be one field, in which case, that can be your ClientID since it's not likely you'll ever have 2 clients with the same name. If this is possible, leave the numerical ID in or come up with some way to tell the identical names apart, and use that for your ID.) 2. Visits 1. Visit ID (Primary Key) 2. Client ID (Foreign Key) 3. Date/Time 4. .... any other fields relating to what happened at that visit. The trick to making all this work is having the foreign key. This is what associates a visit with a client, and the reason WHY client ID has to be unique. You can't be in two places at once... :-) if you have information that doesn't really fit into either catagory of Visit Information or Client Information - but is related to one of the two, use the concept of Foreign Keys again to link that table to whatever else you need to create. For Example... if you are going to track travel expenses for each visit - you might make an Expenses table that has Visit ID as a foreign key (since if you wanted to be really picky, you have 2 trips for each visit... one each direction) I hope that helps! You are defenitely on the right track in thinking things through! If you get stuck building forms and reports to read and edit your data - check out those two sections of the Discussion Group, and holler if you have more questions! Happy Designing! Amanda "cgacker" wrote: i need to create a database that will document visits to people, at different locations, on different days. the summary report needs to include the days visited and infrom from each event (what was discussed, supplies used, details regarding the person visited that day, etc). i suspect i need the following, but may need subforms(?): table - personnel (names of people visited, their address, etc) table - details regarding visit (what was discussed, etc) i'm not sure, but i suspect i need other tables, also aagain, i would like to create a summary report at the end of the month for each person, listing their name, their details, as well as dates of visit(s) and details of visits thanks for any help! -- cg acker |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Database Design for Inventory Control | A P | General Discussion | 2 | October 25th, 2004 12:51 PM |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Database design | Legal Yoda | Database Design | 1 | September 17th, 2004 09:55 PM |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |
Database design help | Kirk | Database Design | 3 | May 4th, 2004 05:31 AM |