Using single input to query multiple non joined tables

  • Thread starter Thread starter Fred
  • Start date Start date
F

Fred

Please help this newbee.

Consider two tables in Access 2000:

Table 1 consists of the fields: Name (primary key), phone number, address
Table 2 consists of the fields: Number (primary key), Date, Description

The field "Description" in table 2 is text containing discriptions of
contacts with people named "Name". A few example records could be:

Table 1
Name Phone Number Address
John 555-5555 123 Ley St.
Fred 556-5555 535 Yel St.


Table 2
Number Date Description
151 3/1/91 Met with John at mall.
152 5/1/91 John and Jill went to dinner.
153 5/12/91 Fred found a penny.
154 6/1/91 Johnny is a good friend.
155 7/1/92 Fred and John work together.


I can easily run a parameter query on Table 2 which returns entries
containing an inputed name. For example, Like "*" & [Input Name] & "*"
where John is entered will return the desired entries with number 151, 152,
154, and 155.

What I cannot figure out how to do is create a query where the user inputs a
name such as "John" with the query returning the single appropriate entry in
Table 1 and all entries in Table 2 where "John" is contained in the
Description field.

If anyone has any suggestions, please let me know.

Thanks
 
You don't show how Table 1 and Table 2 are related, so this is a bit tricky.

(By the way, it's not good practice to use Date, Name, etc. as field names.
These and other words are "reserved" words in ACCESS because they are the
names of VBA functions, form/report/table properties, etc., and ACCESS can
get very confused when you use them without [ ] around them (which is
required in these situations if you want to use them as field names or
control names)).

Something like this, perhaps, will get you started. This is a union query
that would return records from each table in "vertical" sequence (depending
upon the format of your fields, you may get errors if the fields' formats
are different in the two tables):

SELECT [Table 1].[Name], [Table 1].[Phone Number], [Table 1].[Address]
FROM [Table 1]
WHERE [Table 1].[Name] Like "*" & [Enter person's name:] & "*"

UNION

SELECT [Table 2].[Number], [Table 2].[Date], [Table 2].[Description]
FROM [Table 2]
WHERE [Table 2].[Description] Like "*" & [Enter person's name:] & "*";



If you want a "horizontal" sequence, unless you can identify the linking
field, then using a cartesian query may work for you. Something like this,
perhaps will get you started:

SELECT [Table 1].*, [Table 2].*
FROM [Table 1], [Table 2]
WHERE [Table 1].[Name] Like "*" & [Enter person's name:] & "*"
AND [Table 2].[Description] Like "*" & [Enter person's name:] & "*";


--
Ken Snell
<MS ACCESS MVP>



Fred said:
Please help this newbee.

Consider two tables in Access 2000:

Table 1 consists of the fields: Name (primary key), phone number, address
Table 2 consists of the fields: Number (primary key), Date, Description

The field "Description" in table 2 is text containing discriptions of
contacts with people named "Name". A few example records could be:

Table 1
Name Phone Number Address
John 555-5555 123 Ley St.
Fred 556-5555 535 Yel St.


Table 2
Number Date Description
151 3/1/91 Met with John at mall.
152 5/1/91 John and Jill went to dinner.
153 5/12/91 Fred found a penny.
154 6/1/91 Johnny is a good friend.
155 7/1/92 Fred and John work together.


I can easily run a parameter query on Table 2 which returns entries
containing an inputed name. For example, Like "*" & [Input Name] & "*"
where John is entered will return the desired entries with number 151, 152,
154, and 155.

What I cannot figure out how to do is create a query where the user inputs a
name such as "John" with the query returning the single appropriate entry in
Table 1 and all entries in Table 2 where "John" is contained in the
Description field.

If anyone has any suggestions, please let me know.

Thanks
Click to expand...
 
Please help this newbee.

Consider two tables in Access 2000:

Table 1 consists of the fields: Name (primary key), phone number, address
Table 2 consists of the fields: Number (primary key), Date, Description
Click to expand...

I HOPE that this is a hypothetical example. If not, it's hopelessly
ambiguous. Names are NOT unique; if you're Fred, so are my friends
Fred Brown, Fred Brown, Fred Brown, and Fred Felch. And your
Description field might include a phrase like "Fred and Jill went to
see the movie Joan of Arc", and if there were a Name equal to JOAN
you'ld get a false hit.
The field "Description" in table 2 is text containing discriptions of
contacts with people named "Name". A few example records could be:
...
I can easily run a parameter query on Table 2 which returns entries
containing an inputed name. For example, Like "*" & [Input Name] & "*"
where John is entered will return the desired entries with number 151, 152,
154, and 155.

What I cannot figure out how to do is create a query where the user inputs a
name such as "John" with the query returning the single appropriate entry in
Table 1 and all entries in Table 2 where "John" is contained in the
Description field.
Click to expand...

You *could* create a query joining Table1 to Table2 by a "non equi
join" with a criterion on Table1.Name:

SELECT Table1.Name, Table2.Number, Table2.Description
FROM Table1 INNER JOIN Table2
ON Table2.Description LIKE "*" & Table1.Name & "*"
WHERE Table1.Name = [Enter name:]

but (again, unless your real situation is markedly different) this
will get a LOT of false hits.
 
Assuming that you have one table consisting of individuals and a second
table which contains contact information about the same individuals, there
needs to be a field relating the two tables in a one to many relationship.
Without this key, any query involving the two unjoined tables is going to
return a cartesian product. Two records containing "John" in each table
will return four records. Three each will return nine records, etc.

However, if you create this parent:child relationship in the first place
you'll be able to correctly recall all contact information from the second
table for each unique individual that has been entered from the first table,
which seems to be what your question implies.
 
Back
Top