Oh. Ok. Well first off, the sample and the full part are not identical part
numbers that the only difference is the classification. Because of this you
are going to have to create a relation (join) table in which you store both
product_id's so that you can tell the database that the part numbers are
related (one being the full part one being the sample). If the part number
had identical part numbers and were only different by the classification then
you could write a query comparing the product_id and quering the data with an
OR statement.
sample qry for dup products with different classifications
SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName,
Orders.OrderID, [Order Details].ProductID, [Order Details].Quantity, Products.
ProductName, Categories.CategoryID, [Order Details_1].OrderID, Products_1.
ProductName, Categories_1.CategoryID
FROM Categories AS Categories_1 INNER JOIN (Products AS Products_1 INNER JOIN
([Order Details] AS [Order Details_1] INNER JOIN (Orders AS Orders_1 INNER
JOIN ((Categories INNER JOIN Products ON Categories.CategoryID = Products.
CategoryID) INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID
= Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON
Orders_1.CustomerID = Customers.CustomerID) ON [Order Details_1].OrderID =
Orders_1.OrderID) ON Products_1.ProductID = [Order Details_1].ProductID) ON
Categories_1.CategoryID = Products_1.CategoryID
WHERE (((Products.ProductName) Like "XX-155-0-344") AND ((Categories.
CategoryID)=1) AND (([Order Details_1].OrderID)<>[Orders].[OrderID]) AND (
(Products_1.ProductName) Like "XX-155-0-344") AND ((Categories_1.CategoryID)
=2));
That does not seem to be your case here. See, you have to be able to tell
the data that two different numbers are somehow related... see... XX-155-INT-
344 and XX-155-0-344 are different to the computer. Now, if the sample
always has "INT" and the full part ALWAYS has "0" in the exact same spot in
the product number then you could write a parsing function to get the "part
less the class" and then search the data for that, but that seems like a bit
of a mess. To solve easily create a table that will allow you to join the
two part together
tbl_Prod_FP_Sample_Join
Prod_FP_Sample_ID
Prod_FP
Prod_Sample
create a form that will allow you to join one product to another and save it
to the table. Then a query of the table becomes a bit easier.
you put two order tables, the join table, the customer table, two order
detail tables and link them accordingly then filtering them on the product of
either the sample or full part part number or just have it show you all of
the data that is related like this:
SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName,
Orders.OrderID, Products.ProductID, Products.ProductName, [Order Details].
UnitPrice, [Order Details].Quantity, Categories.CategoryName, Orders_1.
OrderID, [Order Details_1].ProductID, Products_1.ProductName, [Order
Details_1].UnitPrice, [Order Details_1].Quantity, Categories_1.CategoryName
FROM Categories AS Categories_1 INNER JOIN (((Products AS Products_1 INNER
JOIN [Order Details] AS [Order Details_1] ON Products_1.ProductID = [Order
Details_1].ProductID) INNER JOIN Orders AS Orders_1 ON [Order Details_1].
OrderID = Orders_1.OrderID) INNER JOIN (Product_Join INNER JOIN ((Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER
JOIN (Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.
OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.
CustomerID) ON Products.ProductID = [Order Details].ProductID) ON
Product_Join.Prod1 = Products.ProductID) ON (Product_Join.Prod2 = Products_1.
ProductID) AND (Orders_1.CustomerID = Customers.CustomerID)) ON Categories_1.
CategoryID = Products_1.CategoryID
WHERE (((Categories.CategoryID)=1) AND ((Categories_1.CategoryID)=3));
the assumption above is that Classification ( CategoryID ) for fullpart is 1
and sample is 3. If you want to query on a specific customer you just add
that to the query or on a certain part number add that to your query in the
where clause. You can add what ever fields you want from your table to give
you the info you want to see or have this as the source for a report and then
place whatever fields you want on the report.
Also, I would not Store the words Full Part or Sample, but store the
Classification ID (long/autonumber) in your product table. Much easier to
filter on a number than text because you dont have to worry about wild cards
or quotes.
tbl_Prod_Class
Prod_Class_ID as autonum
Prod_Class as text
tbl_Prod
tbl_Prod_ID
Product
Product_Description
Prod_Class_ID
blah
blah
This may help. Here are results of a query. The [CustomerID] and [LastName]
are in the Customers table; the [ProductModelNumber] is in the Products table
and [ProductClassification] is in the Classifications table. What I can’t
seem to filter out are those records that are with the " * ". I only want to
return those records.
