Need NON relavant records in Subform

[1deadman]

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.

 

[riny]

Hello,

Difficult for me to understand what the problem really is.
I humbly submit it is maybe a conceptual problem (the naming of tblStoreindex in relation to the other tables puts me on this track).

So there are stores;
Stores have categories;
Categories have aisles;

did I get that right?

If so, I see 3 tables something like this:
table 1: Store ID, store name
table 2: Store ID, category ID.
table 3: Store ID, Category ID, Aisle ID.

The tables are related 1-2 on "Store ID" and 2-3 on "Store Id;Category ID" as one-to-many

If there is a form "stores", it includes a subform "categories" and subform "categories" includes a subform "aisles"

Thus you could have a select combobox on the main "stores" form to select the store you want, and it would automatically give the "categories" for that store in the subform (and the aisles in the subform of "categories" for the default "category&quot.
You can then select another "category" in the subform to see the "aisles" in the subform of that "category" for that store.

Depending on your situation, users can add/edit/delete categories and or category aisles for a store, or not.

I imagine category and aisle names are the same accross stores, so there would also be tables like this:

table 4: Category ID, category name
table 5: Aisle ID, aisle name

when you want to show category or aisle name in a form, you would look up the name in table 4 or 5 according to the ID.

I apologise if I completely misunderstood your problem.

cheers,
Riny

 

[jobo123]

Hi 1deadman,

You could try this. Remaining in the design of the main form, go to the overall properties of the sub form and delete the entries in:

Link Child Fields
Link Master Fields

This will destroy any link between the Master and Child form and subform so that the subform always shows all the records there are regardless of what the master form is doing.
NB - make a back up main form ist in case you want o reverse this. Hope this helps jobo123

 

[1deadman]

Thanks, Jobo 123 but I need the fields linked.

Riny,

Thanks for replying, I think that you are on the right track. Please, take a look at what 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 want to do is have 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. Thanks for the time that you have already spent on this for me.

 

[1deadman]

OK, the link had an extra period in it.

http://mikeis.home.texas.net