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
|
|||
|
|||
Lookup 2 based upon Lookup 1
I have 3 tables:
TblA ID FldX Lookup_To_TblB_For_Product_Type Lookup_To_TblC_For_Product TblB ID Product_Type TblC ID Product Lookup_To_TblB_For_Product_Type In TblA, what rowsource do I use for Lookup_To_TblC_For_Product such that the only values that show in the list are those values from TblC where TblC.Lookup_To_TblB_For_Product_Type is equal to TblB.ID? In other words, in TblA, you choose a Product Type, and the list of Products available on that record are only those of the already select Product Type. |
#2
|
|||
|
|||
Bill Sturdevant wrote:
I have 3 tables: TblA ID FldX Lookup_To_TblB_For_Product_Type Lookup_To_TblC_For_Product TblB ID Product_Type TblC ID Product Lookup_To_TblB_For_Product_Type In TblA, what rowsource do I use for Lookup_To_TblC_For_Product such that the only values that show in the list are those values from TblC where TblC.Lookup_To_TblB_For_Product_Type is equal to TblB.ID? CREATE TABLE ProductTypes ( product_type VARCHAR(100) NOT NULL PRIMARY KEY ) ; CREATE TABLE Products ( product_name VARCHAR(255) NOT NULL PRIMARY KEY, product_Type VARCHAR(100) NOT NULL, FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type) ON UPDATE CASCADE ON DELETE CASCADE ) ; CREATE TABLE TblA ( ID INTEGER NOT NULL PRIMARY KEY, FldX NTEXT, product VARCHAR(255) NOT NULL, FOREIGN KEY (product) REFERENCES Products (product_name) ON UPDATE CASCADE ON DELETE CASCADE) ; CREATE VIEW viewA AS SELECT tblA.ID, Products.product_name, ProductTypes.product_type FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku) INNER JOIN ProductTypes ON Products.product_type = ProductTypes.product_type ; Jamie. -- |
#3
|
|||
|
|||
Jamie,
I am trying to run your suggested code and get an error on the second CREATE, runtime error 3289 "Syntax error in CONSTRAINT clause." "Jamie Collins" wrote: Bill Sturdevant wrote: I have 3 tables: TblA ID FldX Lookup_To_TblB_For_Product_Type Lookup_To_TblC_For_Product TblB ID Product_Type TblC ID Product Lookup_To_TblB_For_Product_Type In TblA, what rowsource do I use for Lookup_To_TblC_For_Product such that the only values that show in the list are those values from TblC where TblC.Lookup_To_TblB_For_Product_Type is equal to TblB.ID? CREATE TABLE ProductTypes ( product_type VARCHAR(100) NOT NULL PRIMARY KEY ) ; CREATE TABLE Products ( product_name VARCHAR(255) NOT NULL PRIMARY KEY, product_Type VARCHAR(100) NOT NULL, FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type) ON UPDATE CASCADE ON DELETE CASCADE ) ; CREATE TABLE TblA ( ID INTEGER NOT NULL PRIMARY KEY, FldX NTEXT, product VARCHAR(255) NOT NULL, FOREIGN KEY (product) REFERENCES Products (product_name) ON UPDATE CASCADE ON DELETE CASCADE) ; CREATE VIEW viewA AS SELECT tblA.ID, Products.product_name, ProductTypes.product_type FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku) INNER JOIN ProductTypes ON Products.product_type = ProductTypes.product_type ; Jamie. -- |
#4
|
|||
|
|||
Bill Sturdevant wrote:
I am trying to run your suggested code and get an error on the second CREATE, runtime error 3289 "Syntax error in CONSTRAINT clause." Sorry, I messed up my version control g. Here's the correct VBA: Sub test() With CurrentProject.Connection ..Execute _ "CREATE TABLE ProductTypes ( " & _ " product_type VARCHAR(100) NOT NULL PRIMARY KEY ) ; " ..Execute _ "CREATE TABLE Products (" & _ " sku CHAR(9) NOT NULL PRIMARY KEY," & _ " product_name VARCHAR(255) NOT NULL," & _ " product_Type VARCHAR(100) NOT NULL," & _ " FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type)" & _ " ON UPDATE CASCADE ON DELETE CASCADE);" ..Execute _ "CREATE TABLE TblA (" & _ " ID INTEGER NOT NULL PRIMARY KEY," & _ " FldX NTEXT," & _ " sku CHAR(9) NOT NULL," & _ " FOREIGN KEY (sku) REFERENCES Products (sku)" & _ " ON UPDATE CASCADE ON DELETE CASCADE);" .Execute _ "CREATE VIEW viewA AS" & _ " SELECT tblA.ID, Products.product_name, ProductTypes.product_type" & _ " FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku)" & _ " INNER JOIN ProductTypes" & _ " ON Products.product_type = ProductTypes.product_type;" End With End Sub Jamie. -- |
#5
|
|||
|
|||
Jamie,
Thanks! What you suggested works (except for the ".." before the "Execute". I changed those to a single ".". BUT, it still does not give me what I was asking for. Using your example as a basis: In the Product Table, I do not want to store Product type again. Instead, I want a Lookup, such that when I am adding records to Products, I have a combo box that lets me choose a Product Type from a list of types available in the Product Type table. Then, in TblA I want 2 Lookups, so that when I am adding records, I see, first, a combo box that lets me pick the desired Product Type, and after having chosen that, I can pick the Product, but this combo box should only have products listed that match the chosen Product Type according to what is in the Products Table. Then there can be some other fields for additional data. In practical terms, when I am entering records in TblA, I want to first choose Fruits as a product type and then only be allowed to choose from Bananas, Grapefruit and Lemons on the Product combo box. When I add the next record, I want to first choose Toothpaste, and then be allowed to choose only from Colgate, Crest and Ipana. "Jamie Collins" wrote: Bill Sturdevant wrote: I am trying to run your suggested code and get an error on the second CREATE, runtime error 3289 "Syntax error in CONSTRAINT clause." Sorry, I messed up my version control g. Here's the correct VBA: Sub test() With CurrentProject.Connection ..Execute _ "CREATE TABLE ProductTypes ( " & _ " product_type VARCHAR(100) NOT NULL PRIMARY KEY ) ; " ..Execute _ "CREATE TABLE Products (" & _ " sku CHAR(9) NOT NULL PRIMARY KEY," & _ " product_name VARCHAR(255) NOT NULL," & _ " product_Type VARCHAR(100) NOT NULL," & _ " FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type)" & _ " ON UPDATE CASCADE ON DELETE CASCADE);" ..Execute _ "CREATE TABLE TblA (" & _ " ID INTEGER NOT NULL PRIMARY KEY," & _ " FldX NTEXT," & _ " sku CHAR(9) NOT NULL," & _ " FOREIGN KEY (sku) REFERENCES Products (sku)" & _ " ON UPDATE CASCADE ON DELETE CASCADE);" .Execute _ "CREATE VIEW viewA AS" & _ " SELECT tblA.ID, Products.product_name, ProductTypes.product_type" & _ " FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku)" & _ " INNER JOIN ProductTypes" & _ " ON Products.product_type = ProductTypes.product_type;" End With End Sub Jamie. -- |
#6
|
|||
|
|||
Bill Sturdevant wrote:
a combo box that lets me pick the desired Product Type, and after having chosen that, I can pick the Product, but this combo box should only have products listed that match the chosen Product Type according to what is in the Products Table. For your first dropdown SELECT ProductTypes.product_type FROM ProductTypes INNER JOIN Products ON ProductTypes.product_type = Products.product_type; For your next dropdown, use the selected value from the first in the following: SELECT sku, product_name FROM Products WHERE product_Type = @product_Type; Then INSERT the chosen sku in TblA. Jamie. -- |
#7
|
|||
|
|||
Jamie,
I think we are almost there! I keep getting asked for a value for "@product_Type". The "@" isn't getting interpretted correctly. "Jamie Collins" wrote: Bill Sturdevant wrote: a combo box that lets me pick the desired Product Type, and after having chosen that, I can pick the Product, but this combo box should only have products listed that match the chosen Product Type according to what is in the Products Table. For your first dropdown SELECT ProductTypes.product_type FROM ProductTypes INNER JOIN Products ON ProductTypes.product_type = Products.product_type; For your next dropdown, use the selected value from the first in the following: SELECT sku, product_name FROM Products WHERE product_Type = @product_Type; Then INSERT the chosen sku in TblA. Jamie. -- |
#8
|
|||
|
|||
Bill Sturdevant wrote:
For your first dropdown SELECT ProductTypes.product_type FROM ProductTypes INNER JOIN Products ON ProductTypes.product_type = Products.product_type; For your next dropdown, use the selected value from the first in the following: SELECT sku, product_name FROM Products WHERE product_Type = @product_Type; Then INSERT the chosen sku in TblA. I keep getting asked for a value for "@product_Type". The "@" isn't getting interpretted correctly. @product_Type is a placeholder for the value from your first dropdown. Apologies for not being clearer. I guess I was suggesting you could use a procedure e.g. CREATE PROCEDURE ProductsByType (arg_product_Type VARCHAR(100)) AS SELECT sku, product_name FROM Products WHERE product_Type = arg_product_Type ORDER BY product_name; Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Lookup function w/Text and Year | Josh O. | Worksheet Functions | 1 | February 12th, 2005 11:27 PM |
Lookup values based on fields in table | RR | Database Design | 2 | December 27th, 2004 05:04 PM |
Lookup with multiple lookup values | Aaron | Worksheet Functions | 2 | September 27th, 2004 03:45 PM |
Lookup based on list of names | tojo107 | General Discussion | 1 | June 15th, 2004 07:30 PM |
Server based lookup table | Barry Gruver | Worksheet Functions | 1 | December 20th, 2003 01:24 AM |