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
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
I have a very complex database - I'll admit from the get go that I'm probably
in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#2
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
Can you share with me what the non profit is and who sponsers it?
-- Dave Hargis, Microsoft Access MVP "MStadnik" wrote: I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#3
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
I'll answer the first bit of your question (I'd stick to 1 question per
letter if I were you) Create a query based on your Participant Table Add ParticipantID. In the next field write FullName: [SurName] & ", " & [FirstName] If you wish, you can add the Surname field to the query again so that you can sort by that. When you create your combo in your Form (and that IS the way to go, don't get sucked into using those nasty lookups in tables or subdatasheets), choose this Query instead of a table and when the wizard opens the query pull the ID field closed so that you can't see it. The ID field will be the Value of your combo and will be the one that you choose when the Wizard says Store that Value in This field. Evi "MStadnik" wrote in message ... I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#4
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
1)
I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. A combo will only display one field at a time when "at rest" so, *within the query* you need to create one field that combines those 3 values. Create a saved query called qcboParticipants. The SQL would be something like: SELECT ParticipantID, FirstName & " " & MI & " " & LastName as FullName FROM tblParticipants ORDER BY FirstName, LastName Set the Rowsource of your combobox to qcboParticipants Column Count =2 ColumnWidths = 0, 2 (hides the IDField. FullName becomes the first visible field so it will show when "at rest") Bound Column = 1 (the ID field) This should make the ID number invisible to the user (which is fine), but it is still the glue that holds things together. 2) The Rowsource for your participants form should be a query based on tblParticipants (or the table itself, but a query is better). You should be able to move from record to record and see all participants. Get this working first. Now, add a tab control. On one page of the tab, add a subform for one of your sub-tables, say family-caregiver. In the properties of the new subform control set Link Master/Child Fields to ParticipantID (a field both tables have in common). This is what synchronizes the forms. There are other methods, but this is the simplest non-code code method, so if you can go this route, do so. The result *should be*, as you scroll from record to record in participants, you will only see the Family/Caregiver records related to the currently displayed participant. Add another page to the tab, add another subtable, etc., Repeat -- HTH, George "MStadnik" wrote in message ... I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#5
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
Hi,
Yes... I don't see why I can't share that info. We aren't sponsored by anyone really - we are funded by a variety of sources including state medicaid programs, private donations and grants. The name of our organization is Bennington Project Independence - we are an adult day center/day health rehabilitation center located in Bennington, VT. "Klatuu" wrote: Can you share with me what the non profit is and who sponsers it? -- Dave Hargis, Microsoft Access MVP "MStadnik" wrote: I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#6
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
Sorry about the multiple questions in one post. Thanks for the info. I'm
still quite a newbie and have really only used queries from the design screen... this is different? "Evi" wrote: I'll answer the first bit of your question (I'd stick to 1 question per letter if I were you) Create a query based on your Participant Table Add ParticipantID. In the next field write FullName: [SurName] & ", " & [FirstName] If you wish, you can add the Surname field to the query again so that you can sort by that. When you create your combo in your Form (and that IS the way to go, don't get sucked into using those nasty lookups in tables or subdatasheets), choose this Query instead of a table and when the wizard opens the query pull the ID field closed so that you can't see it. The ID field will be the Value of your combo and will be the one that you choose when the Wizard says Store that Value in This field. Evi "MStadnik" wrote in message ... I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#7
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
Hi,
Thanks for the info... I'll play with the info I received here and if I have more questions afterwards I'll be back. Thanks a bunch! "George Nicholson" wrote: 1) I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. A combo will only display one field at a time when "at rest" so, *within the query* you need to create one field that combines those 3 values. Create a saved query called qcboParticipants. The SQL would be something like: SELECT ParticipantID, FirstName & " " & MI & " " & LastName as FullName FROM tblParticipants ORDER BY FirstName, LastName Set the Rowsource of your combobox to qcboParticipants Column Count =2 ColumnWidths = 0, 2 (hides the IDField. FullName becomes the first visible field so it will show when "at rest") Bound Column = 1 (the ID field) This should make the ID number invisible to the user (which is fine), but it is still the glue that holds things together. 2) The Rowsource for your participants form should be a query based on tblParticipants (or the table itself, but a query is better). You should be able to move from record to record and see all participants. Get this working first. Now, add a tab control. On one page of the tab, add a subform for one of your sub-tables, say family-caregiver. In the properties of the new subform control set Link Master/Child Fields to ParticipantID (a field both tables have in common). This is what synchronizes the forms. There are other methods, but this is the simplest non-code code method, so if you can go this route, do so. The result *should be*, as you scroll from record to record in participants, you will only see the Family/Caregiver records related to the currently displayed participant. Add another page to the tab, add another subtable, etc., Repeat -- HTH, George "MStadnik" wrote in message ... I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#8
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
Well... frustrated... I can't figure out how to "write" these queries. I'm
guessing it's in the expression builder but I keep getting the error "the expression you entered contains invalid syntax". I'm not getting it "MStadnik" wrote: Hi, Thanks for the info... I'll play with the info I received here and if I have more questions afterwards I'll be back. Thanks a bunch! "George Nicholson" wrote: 1) I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. A combo will only display one field at a time when "at rest" so, *within the query* you need to create one field that combines those 3 values. Create a saved query called qcboParticipants. The SQL would be something like: SELECT ParticipantID, FirstName & " " & MI & " " & LastName as FullName FROM tblParticipants ORDER BY FirstName, LastName Set the Rowsource of your combobox to qcboParticipants Column Count =2 ColumnWidths = 0, 2 (hides the IDField. FullName becomes the first visible field so it will show when "at rest") Bound Column = 1 (the ID field) This should make the ID number invisible to the user (which is fine), but it is still the glue that holds things together. 2) The Rowsource for your participants form should be a query based on tblParticipants (or the table itself, but a query is better). You should be able to move from record to record and see all participants. Get this working first. Now, add a tab control. On one page of the tab, add a subform for one of your sub-tables, say family-caregiver. In the properties of the new subform control set Link Master/Child Fields to ParticipantID (a field both tables have in common). This is what synchronizes the forms. There are other methods, but this is the simplest non-code code method, so if you can go this route, do so. The result *should be*, as you scroll from record to record in participants, you will only see the Family/Caregiver records related to the currently displayed participant. Add another page to the tab, add another subtable, etc., Repeat -- HTH, George "MStadnik" wrote in message ... I have a very complex database - I'll admit from the get go that I'm probably in over my head. Having said that... hiring a programmer is not really an option for our small non-profit firm... so I put on my "database designer" hat, took a quick and dirty Access class, and jumped right in. Gotta love working for a nonprofit ;o) The project: A database that tracks information for our adult day clients. This information includes basic participant info (primary table) - name, social, address, phone, etc., then additional tables related to each participant entitled - family-caregiver, funding, medical, plan of care, progress notes & incident reports. In total there are 7 tables. The primary key in the participant table is "participantID" which is an auto number field. This field is then the foreign key to all the other tables. Each of these tables has many fields and in some cases there can be multiple entries in one category - such as up to four family members for each participant by type (primary, paid, familymember1, familymember2, familymember3) It occurred to me as I was designing the Forms that the data entry person having to remember each participants auto generated "ID" to enter into the other tables (to ensure that each participant is linked properly to the info in the other tables) would be a real pain. So my first challenge is that I want to bring in the participant's actual name instead - from a dropdown (I guess) but I'm open on how best to accomplish this. I started to add this by incorporating a lookup list based on the participant table and choosing the fields - firstname, MI and LastName. This "works" sort of but you end up only see the firstname of the participant in the field instead of a combination of the 3 fields. That's when I showed up here and started reading about the problems with combo boxes and am now totally confused as to how to accomplish this. My other challenge is that I want to create a form whereby the user can easily enter the information for all the appropriate tables - my instructor suggested the use of a "tab form". I created one starting with the participant form and putting each of the other table's forms as subforms on each tab. Worked sort of - but for some reason now my participant form (the main tab) now shows no existing data - only an autonumber for entering a new record and all the subforms seem to have all the data... that's a problem and I'm not sure how this happened. But it also occurs to me that this may not be the best way to accomplish what I'm trying to do - which is for the user to only see the data for each participant - one at a time... right now you can scroll through all the records on the subform and unless you know what you're looking at it would be impossible to tell which family-caregiver records relate to which participant. So in summary - the two questions a 1) How do I link the participant to his other tables in a more meaningful way other than "participantID" which is a number? and 2)What is the best way to design a form that would display the information for each participant one at a time, rather than all the records for each form which is what I seem to have now... if such a thing is possible? Thanks for any help in pointing me in the right direction... the good news is that I think the tables themselves are pretty well designed... I just need a little help in organizing this so that it is user friendly and makes sense |
#9
|
|||
|
|||
Complex Database - How do I accomplish the Goal?
"MStadnik" wrote in message ... Well... frustrated... I can't figure out how to "write" these queries. I'm guessing it's in the expression builder but I keep getting the error "the expression you entered contains invalid syntax". I'm not getting it Create a new query in the query window - don't add any tables. Select View... SQL from the Menu to get into the SQL query editor. The query grid isn't really the query - it's just a tool to create SQL, which is the "real" query. Copy and paste the suggested queries from the posts here; edit your tablename and fieldname to match your actual object names. Note that you will need Forms - not just queries!! - to make effective use of Access. To answer your question about users needing to memorize ID numbers: users should not need to even *SEE* ID numbers, much less type them or memorize them! You can have a Form based on your Individuals table with Subforms based on the related tables, using the ID as the subform's master/link field property to automatically synch them. Also, your family table needs rethinking. "Fields are expensive, records are cheap" - rather than four *fields* for children, you need a one to many relationship with one child per record. Good luck with the project, and don't hesitate to post back! |
Thread Tools | |
Display Modes | |
|
|