Linking more than one subform and searching through them

[Paterson10987]

I am working on basically an inventory. I have tblParts for all inherent
part information, and then tables tblSuppliers (to hold supplier, part#,
price, lead time) and tblLocation (details what asset(s) and where part is
used). The two tables may have multiple records joined to one record in
tblParts [ID] field by their [PartID] field. I'm trying to create an easy
form to search through the parts.

I started with unbound controls that are used to filter my subform,
including by its locations and supplier info. The subform has a data source
that is a query simply joining the three tables and displays the datasheet. I
can click a button that takes me to a seperate form to edit the selected
record. It works great but as i continue to add more information in its
becoming really annoying. Say I have a part that has two suppliers and is
used at 7 locations.. I now have 14 separate entries for the same part.

Now I would like to only show a list of all the parts (and only one copy of
it) and, when selected, display its respective supplier and location info.

I can do this by bounding my form to tblParts, and if I display using
continuous forms I can leave my filtering stuff in the header and put
subforms in the footer.

Thats fine and easy, but then I can't search for parts by supplier and
location info, just by name or description.

So is there someway I can filter tblParts by fields in its joining tables,
but only show one copy of the part? Or if anyone can come up with a much
better solution for easily finding my part, including restructuring my
tables, queries or what have you, I'm very open to ideas.

Thanks

 

[John W. Vinson]

So is there someway I can filter tblParts by fields in its joining tables,
but only show one copy of the part?

Sure, though it may be a bit tricky.

Take your three-table query, and have criteria on the supplier and other such
fields, but *uncheck* the Show checkbox on the fields. Only leave it checked
for those fields in the Parts table that you want the user to see.

Then view the query's Properties and set its "Unique Values" property to Yes.

This will let you search on "many" side tables, but display only one instance
of each part. Note that this query will not be updateable, but typically you
wouldn't want it to be!

 

[Paterson10987]

Thanks this does give me the unique values that i need but when I try to run
the filter it cannot find the field. I just get the input box asking me what
i mean. When I uncheck the 'show' box it's no longer in the query, and if I
leave it i lose the unique values.

is there a way around this?

I also tried to sneak them in there by unchecking them and ordering by them,
but i guess it has a conflict with the sql keyword DISTINCT

 

[John W. Vinson]

Thanks this does give me the unique values that i need but when I try to run
the filter it cannot find the field. I just get the input box asking me what
i mean. When I uncheck the 'show' box it's no longer in the query, and if I
leave it i lose the unique values.

is there a way around this?

Please post the SQL of the query. I'm not sure what you're dealing with!

 

[Paterson10987]

SELECT DISTINCT SpareParts.*, [PartName] & " - " & [Description] AS Name
FROM (SpareParts LEFT JOIN PartLocations ON SpareParts.ID =
PartLocations.PartID) LEFT JOIN PartSuppliers ON SpareParts.ID =
PartSuppliers.PartID;

 

[John W. Vinson]

SELECT DISTINCT SpareParts.*, [PartName] & " - " & [Description] AS Name
FROM (SpareParts LEFT JOIN PartLocations ON SpareParts.ID =
PartLocations.PartID) LEFT JOIN PartSuppliers ON SpareParts.ID =
PartSuppliers.PartID;

Try using a Subquery instead:

SELECT SpareParts.*, [PartName] & " - " & [Description] AS TheName
FROM SpareParts
WHERE SpareParts.ID IN
(SELECT PartID FROM PartSuppliers INNER JOIN Suppliers
ON Suppliers.ID = PartSuppliers.SupplierID
WHERE Supplierers.SupplierName = [Enter supplier name:]);

You can use the same logic for Locations.

 

[Paterson10987]

This is what I ended using and it works great! Thanks a lot.

SELECT SpareParts.*
FROM SpareParts
WHERE (SpareParts.ID In (SELECT PartSuppliers.PartID FROM PartSuppliers
WHERE ( [Enter Supplier Filter] )) AND
SpareParts.ID IN (SELECT PartLocations.PartID FROM PartLocations WHERE (
[Enter Location Filter] )));

SELECT DISTINCT SpareParts.*, [PartName] & " - " & [Description] AS Name
FROM (SpareParts LEFT JOIN PartLocations ON SpareParts.ID =
PartLocations.PartID) LEFT JOIN PartSuppliers ON SpareParts.ID =
PartSuppliers.PartID;

Try using a Subquery instead:

SELECT SpareParts.*, [PartName] & " - " & [Description] AS TheName
FROM SpareParts
WHERE SpareParts.ID IN
(SELECT PartID FROM PartSuppliers INNER JOIN Suppliers
ON Suppliers.ID = PartSuppliers.SupplierID
WHERE Supplierers.SupplierName = [Enter supplier name:]);

You can use the same logic for Locations.