cross referencing two tables

  • Thread starter Thread starter BZeyger
  • Start date Start date
B

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.
 
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
 
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
 
Back
Top