searching in 3 tables ?

  • Thread starter Thread starter Michal \(Czapla\)
  • Start date Start date
M

Michal \(Czapla\)

Hi, i was wondering if anyone could tell me how to search
for the same record in 3 different tables. Here is what i
mean:

I have 3 tables.. The only columns they have in common are
the First and Last name column. Each table gathers
different information.

I want to be able to find all the records from each table
that match the FIRST and LAST name inputed by the user.


I would be greatfull for any kind of help.

Thanks

Michal (Czapla)
(e-mail address removed)
 
You could combine the three tables using a union query and then searching
the union query.
SELECT FirstName, LastName, "tblA" as TableName
FROM tblA
UNION ALL
SELECT FirstName, LastName, "tblB"
FROM tblB
UNION ALL
SELECT FirstName, LastName, "tblC"
FROM tblC;
 
Thanks a lot.. But each table has other columns.. for
example.. oine table represents klients that bought a
computer, the other represents clients that bought a car,
and the third clients that bought a bike. I want to be
able to type the name and last name of a client and see
everything that he has bough (all the records that have
his name and last name in all 3 tables)

CCan u tell me how and where can I find a union query ,
does that query create a new table ??

Thanks a lot once again

Michal (czapla)
 
Maybe you need to suggest how you want to "see" them. If they all have
different fields that you want to display, did you have an idea how you
expect to view the different fields?

You could create an unbound main form with a combo box built from the union
query. Add three subforms (one based on each table) that are linked to the
combo box by the first name and last name.

I kinda question why all purchases aren't in the same table but I assume you
know your data better than I do.
 
Exactly.. I fully agree. My first though was that they
should be in one table, but unfortunatlny each table was
created at a different time.. one was created about 3
moths after the first.

So i guess i could combine the tables into one, or like u
suggested use a combo box and connect the combo box with
subforms..

I want to see the results in one form (ofcourse using sub-
forms). Selecting form a combo box would be ok.. but is
there a different way (the total number of last and first
names is about 3500 :(.

I would be very greatful if you could tell me how to
connect the query with a combo box or a text field that
would be used for typing the last name of the customer
before displaying all the records from the 3 tables that
match his last and first name.
The most important is that all the records be displayed on
one screen (form).

Thanks again for so much help :)

Michal (Czapla)
 
Make a union query
SELECT LastName & FirstName as LastFirst, FirstName, LastName
FROM tblA
UNION
SELECT LastName & FirstName, FirstName, LastName
FROM tblB
UNION
SELECT LastName & FirstName, FirstName, LastName
FROM tblC
ORDER BY LastFirst;

Use this as the row source of a combo box. Create three queries that can be
used as the record sources of three subforms:
Select LastName & FirstName as LastFirst, *
FROM tblA
The same for the others.

You can then add the subforms to the main form (possibly on three tabs of a
tab control). Set all the link master child properties to LastFirst.
 
Thanks AGAING :).. it's all working almost, all i need is
just one more thing . When I combine First and Last name..
they combine as for example : JohnSmith .. what do i do so
that they combine as "John Smith" with a space between the
name and last name ??

Thanks a lot ,

Michal (czapla)
 
I have not been following the thread, but try this
[FirstName] & " " & [LastName]
Bob
 
Back
Top