I know that there is a simple answer, I just can’t find it. This is the second post regarding this, the first got no replies. Maybe the way that I asked the question was poorly stated or too confusing. Please take a look at the tables and relationships that I have…http://mikeis.home.texas.net.
Lets assume that I am talking about grocery stores. There are many stores, all of which have basically the same categories (frozen foods, produce, canned veggies, etc.) but each store puts these categories on a different aisle, based on the layout of the store. What I have done is put the stores in one table, all of the categories in table 2, and then another table which will hold the value of the aisle, based on store and category.
I have the form with the stores being chosen from a combo box. I then have a subform that has the storecategories and aisles based on the store selection on the main form. However, if the aisle hasn’t been assigned to it yet, it won’t be on the list. I need all of the possible categories to be listed so that the user can basically go down the list and assign aisle values to each category.
I am trying to get it similar to a spreadsheet where once the store is chosen, column “A” would list all of the categories and column “B” would list the relevant aisle number.
If I am going about this all wrong, let me know. Any suggestions on how I might accomplish this would be greatly appreciated.
Original Post:
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.
Lets assume that I am talking about grocery stores. There are many stores, all of which have basically the same categories (frozen foods, produce, canned veggies, etc.) but each store puts these categories on a different aisle, based on the layout of the store. What I have done is put the stores in one table, all of the categories in table 2, and then another table which will hold the value of the aisle, based on store and category.
I have the form with the stores being chosen from a combo box. I then have a subform that has the storecategories and aisles based on the store selection on the main form. However, if the aisle hasn’t been assigned to it yet, it won’t be on the list. I need all of the possible categories to be listed so that the user can basically go down the list and assign aisle values to each category.
I am trying to get it similar to a spreadsheet where once the store is chosen, column “A” would list all of the categories and column “B” would list the relevant aisle number.
If I am going about this all wrong, let me know. Any suggestions on how I might accomplish this would be greatly appreciated.
Original Post:
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.