Hello, I am currently stuck in building a research database for a GAP analysis.
I created a very useful research database for an environmental program however i am having a problem with developing a request I was given from the program manager.
My database has a total of 6 tables. 1 table is called "Master", which is where all the data is stored once a record is entered on a form i created "MasterData". The other five tables are presets of information: I.E one of these tables is called "Focus". This table is a preset of different focus' of research, currently there are 5 different focus': Water, Ecosystems, Biodiversity, Farming, Air Emissions. Initially (before creating the subform), the program manager would have her administrator enter research data and I had a combo box in the form where the user could select a focus. Once the record was saved, my "Master" table would be updated with the record.
Now, the program manager notified me that some research can relate to multiple focus' hence my problem. A simple combo box does not allow to select more than 1 field. Therefore, I created a subform within this "masterdata" form and after testing this, although I was able to enter more than 1 focus per record, after it is saved, the table is updated is an odd manner.
To be more clear, in the masterdata form, there is a combo box for Year (to), a combo box for Year (From), a combox box for MethodUsed a text box for NumberOfRespondents and a Memobox for Results. These are all unique entries for each research, however after making the selections above, let's say the user has to select 3 different types of focus' in the subform, how can all the the other feilds be populated in the same row of the "Master Table" - In this case there should be 3 rows appended to the table however in my case, there is 4 rows: the 1st row has all the data entered from the main form, and the next three rows, only the focus is showing without the rest of the data populated in the main form.
I've attached a screen shot of the MasterData form & my relationships. If possible, can someone please shed some light on this, maybe my approach is flawed, maybe I have to create new relationships, maybe I have to use VBA (i hope not).
Thanks in advance!!
I created a very useful research database for an environmental program however i am having a problem with developing a request I was given from the program manager.
My database has a total of 6 tables. 1 table is called "Master", which is where all the data is stored once a record is entered on a form i created "MasterData". The other five tables are presets of information: I.E one of these tables is called "Focus". This table is a preset of different focus' of research, currently there are 5 different focus': Water, Ecosystems, Biodiversity, Farming, Air Emissions. Initially (before creating the subform), the program manager would have her administrator enter research data and I had a combo box in the form where the user could select a focus. Once the record was saved, my "Master" table would be updated with the record.
Now, the program manager notified me that some research can relate to multiple focus' hence my problem. A simple combo box does not allow to select more than 1 field. Therefore, I created a subform within this "masterdata" form and after testing this, although I was able to enter more than 1 focus per record, after it is saved, the table is updated is an odd manner.
To be more clear, in the masterdata form, there is a combo box for Year (to), a combo box for Year (From), a combox box for MethodUsed a text box for NumberOfRespondents and a Memobox for Results. These are all unique entries for each research, however after making the selections above, let's say the user has to select 3 different types of focus' in the subform, how can all the the other feilds be populated in the same row of the "Master Table" - In this case there should be 3 rows appended to the table however in my case, there is 4 rows: the 1st row has all the data entered from the main form, and the next three rows, only the focus is showing without the rest of the data populated in the main form.
I've attached a screen shot of the MasterData form & my relationships. If possible, can someone please shed some light on this, maybe my approach is flawed, maybe I have to create new relationships, maybe I have to use VBA (i hope not).
Thanks in advance!!
