cross referencing two tables

[BZeyger]

Hello,
I am having a hard time getting started. I am trying to create a Access VBA
Form that will locate selected items from one table while cross referencing
the info from another table.

For Example:

Table 1:

ID Item Type

1 Truck Red
2 Car Red
3 Boat Blue
4 Bike Blue

Table 2:

ID Item Manufacture

1 Super Bike BMX
2 Charged Boat Yamaha
3 Mega Car BMW


If the user were to click a button that says "blue", a list would appear
showing the items from the second table that contained the string "boat" and
"bike".

I am working on a much larger scale. I didn't know which way to go about this.

 

[Ken Sheridan]

The following query will do it. Use a reference to the control on your form
in place of the [Enter type] parameter.

SELECT *
FROM [Table 2]
WHERE EXISTS
(SELECT *
FROM [Table 1]
WHERE [Table 2].Item LIKE "*" & [Table 1].Item & "*"
AND [Table 1].Type = [Enter type:]);

An unbound combo box would be better than a button as the user can select a
type from this by setting its RowSource property to:

SELECT DISTINCT Type
FROM [Table 1]
ORDER BY Type;

If you bind the form to the above query you then simply have to requery the
form in the combo box's AfterUpdate event procedure with:

Me.Requery

BTW I think you mean "that contained the string "boat" OR "bike". A Boolean
AND would require the words 'boat' and 'bike' to be present in the same row.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

Something seems to be missing...

In your second table, you have a field that has text in it that may/may not
match the text you have in the [Item] field in table1. What will you do if
table1 has "Bike" but table2 has "Blah blah bicycle blah blah". I would
think you'd want to make sure that you have a field in table2 that has the
same (EXACTLY) 'items' as your table1.

One way to ensure this is to use a lookup table that contains ONLY the items
and a ID field, then use ONLY the ID field in the two tables you mentioned.

Access is a relational database. It's features/functions work best when you
feed it well-normalized data.

I don't understand enough about your underlying situation to assess if your
table structure is well-normalized...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP