Newbie needs help. I have spent way to much time on this when I know that one of you experts out there can resolve this for me in less than a minute. I have created three tables as follows:
[tblStores].[store]
[tblStorecategories].[storecategory]
[tblStoreindex].[storeindexid]
[tblStoreindex].[store]
[tblStoreindex].[storecategory]
[tblStoreindex].[aisle]
I have a form with one subform. The main form has a combo box to look up the value in [tblStores].[store]. In the subform (tabular) I have two text boxes, one to display the value for [tblStorecategories].[storecategory] and the other to display the value for [tblStoreindex].[aisle]. Unfortunately, the subform works like one would normally expect. It displays the storecategories and aisles, based on the store selected and the records present in the tblStoreindex table. However, what I would like is for the subform to display all records from [tblStorecategories].[storecategory] wether or not there is a matching value in the tblStoreindex table or not so that the user can basically see a list of all of the storecategories available and type in an aisle value for each.
The query that I am using for the subform is: SELECT tblStores.*, tblStoreindex.aisle, tblStorecategory.storecategory
FROM tblStores INNER JOIN (tblStorecategory INNER JOIN tblStoreindex ON tblStorecategory.storecategory = tblStoreindex.storecategory) ON tblStores.store = tblStoreindex.store;
I could probably do this using a spreadsheet with the field [store] as the column headers and the [storecategories] listed in column A and the aisle data filling in the rest, but I don't want to involve a spreadsheet in this project. Anybody out there have any ideas? Thanks in advance.
[tblStores].[store]
[tblStorecategories].[storecategory]
[tblStoreindex].[storeindexid]
[tblStoreindex].[store]
[tblStoreindex].[storecategory]
[tblStoreindex].[aisle]
I have a form with one subform. The main form has a combo box to look up the value in [tblStores].[store]. In the subform (tabular) I have two text boxes, one to display the value for [tblStorecategories].[storecategory] and the other to display the value for [tblStoreindex].[aisle]. Unfortunately, the subform works like one would normally expect. It displays the storecategories and aisles, based on the store selected and the records present in the tblStoreindex table. However, what I would like is for the subform to display all records from [tblStorecategories].[storecategory] wether or not there is a matching value in the tblStoreindex table or not so that the user can basically see a list of all of the storecategories available and type in an aisle value for each.
The query that I am using for the subform is: SELECT tblStores.*, tblStoreindex.aisle, tblStorecategory.storecategory
FROM tblStores INNER JOIN (tblStorecategory INNER JOIN tblStoreindex ON tblStorecategory.storecategory = tblStoreindex.storecategory) ON tblStores.store = tblStoreindex.store;
I could probably do this using a spreadsheet with the field [store] as the column headers and the [storecategories] listed in column A and the aisle data filling in the rest, but I don't want to involve a spreadsheet in this project. Anybody out there have any ideas? Thanks in advance.