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
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
I'm working on a database for a huge amount of information that realistically
is related ONLY by a person's ID number, unless I'm just not thinking of it correctly. Basically we collect general demographic information and information about the person entering the info (I lumped this all in as a drop-down in the box because it's only one variable and we don't really need another table to explain who's who). My "main table" is [Main], which contains information on the following: General Case Information, Last Specialist Visit, Last 12 month medical overview, and Life Events. I didn't break these down into separate tables because each person will only have one entry for each of them and I wanted them to be on the same form. Then I have other tables: [Last Visit] which contains all visits to any provider on a given day, [LVMDx] which contains last visit diagnoses from any of the visits in the [Last Visit] table, [LSVDx] which is only diagnoses from the last specialist visit, [SA] which contains information on substance use at various points in time when it was evaluated, [SI] which contains other assessed medical information at specific (different) timepoints, and [Events] which contains the ID and year/time of event. Is this a viable way to break down the information? The people I work with prefer more information in one table to a lot of information in other places, so for the most part all my relationships are one-to-many and based on ID. Earlier on I attempted to integrate the [Last Visit] with the [Main] table and ended up in a situation where you could add a new subject but evidently not enough of a new subject to "make it stick" in form view - so it would be a poor sad little nothing in the table, and I'm hoping to find a way to avoid that. I don't know if it was related to my setup, or ??? Also, was having errors with multiple people trying to access the DB at once. I'm not sure if that was a remote server problem or if it was a bad database problem, but if anyone has any light to shed I would appreciate it. |
#2
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
Hold on a sec...
If the ONLY reason you are putting everything into one table is so that your FORM can have it all, step away from the PC! Access tables are NOT like Excel spreadsheets ... you don't have to put it all in one place. Access is a relational database ... both you and Access have to work overtime when you feed it 'sheet data. Access tables store data, Access forms and reports display it, and Access queries gather together all the pieces that you want to display. Use a query to assemble data for display in a form. That said, here's three areas I recommend getting ahead of the learning curve to use Access to create an effective application (i.e., one that gets used): 1) relational data base design and normalization 2) Access tips and tricks (how Access does it) 3) Graphical user interface design (how to help the user get the job done) Oh yes, you need to know how to do software development projects too...g Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Allie" wrote in message ... I'm working on a database for a huge amount of information that realistically is related ONLY by a person's ID number, unless I'm just not thinking of it correctly. Basically we collect general demographic information and information about the person entering the info (I lumped this all in as a drop-down in the box because it's only one variable and we don't really need another table to explain who's who). My "main table" is [Main], which contains information on the following: General Case Information, Last Specialist Visit, Last 12 month medical overview, and Life Events. I didn't break these down into separate tables because each person will only have one entry for each of them and I wanted them to be on the same form. Then I have other tables: [Last Visit] which contains all visits to any provider on a given day, [LVMDx] which contains last visit diagnoses from any of the visits in the [Last Visit] table, [LSVDx] which is only diagnoses from the last specialist visit, [SA] which contains information on substance use at various points in time when it was evaluated, [SI] which contains other assessed medical information at specific (different) timepoints, and [Events] which contains the ID and year/time of event. Is this a viable way to break down the information? The people I work with prefer more information in one table to a lot of information in other places, so for the most part all my relationships are one-to-many and based on ID. Earlier on I attempted to integrate the [Last Visit] with the [Main] table and ended up in a situation where you could add a new subject but evidently not enough of a new subject to "make it stick" in form view - so it would be a poor sad little nothing in the table, and I'm hoping to find a way to avoid that. I don't know if it was related to my setup, or ??? Also, was having errors with multiple people trying to access the DB at once. I'm not sure if that was a remote server problem or if it was a bad database problem, but if anyone has any light to shed I would appreciate it. |
#3
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
On Tue, 14 Oct 2008 11:51:03 -0700, Allie
wrote: I'm working on a database for a huge amount of information that realistically is related ONLY by a person's ID number, unless I'm just not thinking of it correctly. Basically we collect general demographic information and information about the person entering the info (I lumped this all in as a drop-down in the box because it's only one variable and we don't really need another table to explain who's who). My "main table" is [Main], which contains information on the following: General Case Information, Last Specialist Visit, Last 12 month medical overview, and Life Events. I didn't break these down into separate tables because each person will only have one entry for each of them and I wanted them to be on the same form. It sounds like you're on the wrong track. Unless you want to erase all visits except the last, all overviews except the last, and record one and only one Life Event, I see at least four tables here - Demographics, related one to many to Visits, Overviews (depending on what's in the overview), and LifeEvents. DON'T assume that you must have all this information in one table; you're using a relational database, and joining information from multiple tables is bread and butter to Access. Then I have other tables: [Last Visit] which contains all visits to any provider on a given day, [LVMDx] which contains last visit diagnoses from any of the visits in the [Last Visit] table, [LSVDx] which is only diagnoses from the last specialist visit, [SA] which contains information on substance use at various points in time when it was evaluated, [SI] which contains other assessed medical information at specific (different) timepoints, and [Events] which contains the ID and year/time of event. Several many to many relationships here. Is this a viable way to break down the information? The people I work with prefer more information in one table to a lot of information in other places, They're wrong, but then they should not care how the data is STORED, just how it's presented. so for the most part all my relationships are one-to-many and based on ID. Earlier on I attempted to integrate the [Last Visit] with the [Main] table and ended up in a situation where you could add a new subject but evidently not enough of a new subject to "make it stick" in form view - so it would be a poor sad little nothing in the table, and I'm hoping to find a way to avoid that. I don't know if it was related to my setup, or ??? It is. Also, was having errors with multiple people trying to access the DB at once. I'm not sure if that was a remote server problem or if it was a bad database problem, but if anyone has any light to shed I would appreciate it. Post some information about the nature of the problems. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
"John W. Vinson" wrote: On Tue, 14 Oct 2008 11:51:03 -0700, Allie wrote: I'm working on a database for a huge amount of information that realistically is related ONLY by a person's ID number, unless I'm just not thinking of it correctly. Basically we collect general demographic information and information about the person entering the info (I lumped this all in as a drop-down in the box because it's only one variable and we don't really need another table to explain who's who). My "main table" is [Main], which contains information on the following: General Case Information, Last Specialist Visit, Last 12 month medical overview, and Life Events. I didn't break these down into separate tables because each person will only have one entry for each of them and I wanted them to be on the same form. It sounds like you're on the wrong track. Unless you want to erase all visits except the last, all overviews except the last, and record one and only one Life Event, I see at least four tables here - Demographics, related one to many to Visits, Overviews (depending on what's in the overview), and LifeEvents. DON'T assume that you must have all this information in one table; you're using a relational database, and joining information from multiple tables is bread and butter to Access. I have all of these as separate tables. The "Main" table contains any information that only is to be entered once per person, ever. I couldn't figure out what reason there would have been to separate them. Then I have other tables: [Last Visit] which contains all visits to any provider on a given day, [LVMDx] which contains last visit diagnoses from any of the visits in the [Last Visit] table, [LSVDx] which is only diagnoses from the last specialist visit, [SA] which contains information on substance use at various points in time when it was evaluated, [SI] which contains other assessed medical information at specific (different) timepoints, and [Events] which contains the ID and year/time of event. Several many to many relationships here. The way I have related them is ID (primary key of demographics) to ID - (foreign key of each related table). I don't understand why I'd want to link other things, or link each table to all other tables. Should every variable that could potentially be linked always be linked to everything it could relate to? That just seems nightmarish somehow when you have a total of 7 tables... Is this a viable way to break down the information? The people I work with prefer more information in one table to a lot of information in other places, They're wrong, but then they should not care how the data is STORED, just how it's presented. Basically, what's going to become of this database is that a handful of people people will use it in lieu of paper forms while we review medical charts and collect specific pieces of information. It's focused on form entry primarily because our stats people are going to want it reformatted and imported into their programs outside of all things Microsoft. I'm sure there's probably some way to turn all of my entry-friendly drop down menus into a numeric system probably through another table and more relationships (?), but that seems like overkill. Or is it? so for the most part all my relationships are one-to-many and based on ID. Earlier on I attempted to integrate the [Last Visit] with the [Main] table and ended up in a situation where you could add a new subject but evidently not enough of a new subject to "make it stick" in form view - so it would be a poor sad little nothing in the table, and I'm hoping to find a way to avoid that. I don't know if it was related to my setup, or ??? It is. Would it fix it to separate out [Last Visit] from [Main]? That's what I was going to work on trying next - I've never run into this problem before where my data just vanishes from a form... Also, was having errors with multiple people trying to access the DB at once. I'm not sure if that was a remote server problem or if it was a bad database problem, but if anyone has any light to shed I would appreciate it. Post some information about the nature of the problems. -- My DB is stored on another server that we access through Citrix. Usually with the DBs I've worked with, multiple people can work in the database at the same time, just not in the same record. But when my coworker was connected and I went to connect, it told me that it was locked by an administrator on the server... not sure what to make of that. John W. Vinson [MVP] |
#5
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
On Tue, 14 Oct 2008 18:39:00 -0700, Allie
wrote: The way I have related them is ID (primary key of demographics) to ID - (foreign key of each related table). I don't understand why I'd want to link other things, or link each table to all other tables. Should every variable that could potentially be linked always be linked to everything it could relate to? That just seems nightmarish somehow when you have a total of 7 tables... HUH!? Nobody suggested that, that I can see. I think you have it right - ID primary key linked to ID foreign key. You would certainly NOT try to link every table to every other table, only the actual, logical links. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
"John W. Vinson" wrote:
On Tue, 14 Oct 2008 18:39:00 -0700, Allie wrote: The way I have related them is ID (primary key of demographics) to ID - (foreign key of each related table). I don't understand why I'd want to link other things, or link each table to all other tables. Should every variable that could potentially be linked always be linked to everything it could relate to? That just seems nightmarish somehow when you have a total of 7 tables... HUH!? Nobody suggested that, that I can see. I think you have it right - ID primary key linked to ID foreign key. You would certainly NOT try to link every table to every other table, only the actual, logical links. -- John W. Vinson [MVP] That makes sense... sorry. (I'm not really a computer person - I'm a research assistant who happens to know a little bit about how to use a computer, so making this has become my job.) Thanks for your help. Wondering if anyone can shed light on this one - I have (because people don't want to enter codes for things, they want to pick their response from combo boxes) a million and a half drop-down boxes of the same responses. I'd really like to put all those responses in some kind of ResponseID table and link it to my variables. But the problem is, it's the responses for everything, and I'm worried that that will create problems. Is that something people do successfully? For most questions the responses are "Yes/No/Not Applicable/Not Mentioned", though there are some variations. I think they only want the options available to that particular item to show up, so I would need a few response tables for different types of responses. So I thought I could then make my comboboxes pull the values from the response tables and store the response IDs in the fields of the visit tables? Does that work? Sorry for all the questions... usually the DBs I work with are structured a lot differently from this. |
#7
|
|||
|
|||
Help with relationship setup? (Or, am I messing it up?)
Allie
This sounds a little like it might be related to a survey or a questionnaire or a test. If so, take a look at the excellent work Duane H. has done setting up an Access application to help you generate a well-normalized "survey" database: http://www.rogersaccesslibrary.com/O...p#Hookom,Duane Regards Jeff Boyce Microsoft Office/Access MVP "Allie" wrote in message ... "John W. Vinson" wrote: On Tue, 14 Oct 2008 18:39:00 -0700, Allie wrote: The way I have related them is ID (primary key of demographics) to ID - (foreign key of each related table). I don't understand why I'd want to link other things, or link each table to all other tables. Should every variable that could potentially be linked always be linked to everything it could relate to? That just seems nightmarish somehow when you have a total of 7 tables... HUH!? Nobody suggested that, that I can see. I think you have it right - ID primary key linked to ID foreign key. You would certainly NOT try to link every table to every other table, only the actual, logical links. -- John W. Vinson [MVP] That makes sense... sorry. (I'm not really a computer person - I'm a research assistant who happens to know a little bit about how to use a computer, so making this has become my job.) Thanks for your help. Wondering if anyone can shed light on this one - I have (because people don't want to enter codes for things, they want to pick their response from combo boxes) a million and a half drop-down boxes of the same responses. I'd really like to put all those responses in some kind of ResponseID table and link it to my variables. But the problem is, it's the responses for everything, and I'm worried that that will create problems. Is that something people do successfully? For most questions the responses are "Yes/No/Not Applicable/Not Mentioned", though there are some variations. I think they only want the options available to that particular item to show up, so I would need a few response tables for different types of responses. So I thought I could then make my comboboxes pull the values from the response tables and store the response IDs in the fields of the visit tables? Does that work? Sorry for all the questions... usually the DBs I work with are structured a lot differently from this. |
Thread Tools | |
Display Modes | |
|
|