retrieving unique record from identical tables

[tolis]

I have 3 identical (having same fields) tables with different names (FAST_1,
FAST_2, FAST_3).
I need to create a query (using SQL) in order to search the tables for a
specific record and when found to display all the fields of that record FROM
THAT PARTICULAR TABLE.

What I did so far is:

SELECT FAST_1.*, FAST_2.*, FAST_3.*
FROM FAST_1, FAST_2, FAST_3
WHERE (((FAST_1.NUMB_SUBSCR)=[NUMB])) Or (((FAST_2.)NUMB_SUBSCR=[NUMB])) Or
(((FAST_3.NUMB_SUBSCR)=[NUMB]));

but it returns all the records.

Anybody knows how to proceed?

Thank you for your help and time

 

[Douglas J. Steele]

Since you've got no code to join the 3 tables, you've got what's known as a
cartesian product.

Try:

SELECT *
FROM FAST_1
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_2
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_3
WHERE NUMB_SUBSCR=[NUMB]

 

[Steve]

My suggestion is to append FAST_2 to Fast_1 And FAST_3 to Fast_1. and then
you can use a simple query setting the criteria of the Subscr field =
[Numb].

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[tolis]

thank you very much. it was very helpful.
How can I retrieve the name of the table as well?

Since you've got no code to join the 3 tables, you've got what's known as a
cartesian product.

Try:

SELECT *
FROM FAST_1
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_2
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_3
WHERE NUMB_SUBSCR=[NUMB]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have 3 identical (having same fields) tables with different names
(FAST_1,
FAST_2, FAST_3).
I need to create a query (using SQL) in order to search the tables for a
specific record and when found to display all the fields of that record
FROM
THAT PARTICULAR TABLE.

What I did so far is:

SELECT FAST_1.*, FAST_2.*, FAST_3.*
FROM FAST_1, FAST_2, FAST_3
WHERE (((FAST_1.NUMB_SUBSCR)=[NUMB])) Or (((FAST_2.)NUMB_SUBSCR=[NUMB]))
Or
(((FAST_3.NUMB_SUBSCR)=[NUMB]));

but it returns all the records.

Anybody knows how to proceed?

Thank you for your help and time

 

[John Spencer]

Add the name into the union query.
SELECT *, "Fast_1" as TblName
FROM FAST_1
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *, "Fast_2" as TblName
FROM FAST_2
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *, "Fast_3" as TblName
FROM FAST_3
WHERE NUMB_SUBSCR=[NUMB]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

thank you very much. it was very helpful.
How can I retrieve the name of the table as well?

Since you've got no code to join the 3 tables, you've got what's known as a
cartesian product.

Try:

SELECT *
FROM FAST_1
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_2
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_3
WHERE NUMB_SUBSCR=[NUMB]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have 3 identical (having same fields) tables with different names
(FAST_1,
FAST_2, FAST_3).
I need to create a query (using SQL) in order to search the tables for a
specific record and when found to display all the fields of that record
FROM
THAT PARTICULAR TABLE.

What I did so far is:

SELECT FAST_1.*, FAST_2.*, FAST_3.*
FROM FAST_1, FAST_2, FAST_3
WHERE (((FAST_1.NUMB_SUBSCR)=[NUMB])) Or (((FAST_2.)NUMB_SUBSCR=[NUMB]))
Or
(((FAST_3.NUMB_SUBSCR)=[NUMB]));

but it returns all the records.

Anybody knows how to proceed?

Thank you for your help and time

 

[tolis]

Thank you. It helped me a lot.

Add the name into the union query.
SELECT *, "Fast_1" as TblName
FROM FAST_1
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *, "Fast_2" as TblName
FROM FAST_2
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *, "Fast_3" as TblName
FROM FAST_3
WHERE NUMB_SUBSCR=[NUMB]


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

thank you very much. it was very helpful.
How can I retrieve the name of the table as well?

Since you've got no code to join the 3 tables, you've got what's known as a
cartesian product.

Try:

SELECT *
FROM FAST_1
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_2
WHERE NUMB_SUBSCR=[NUMB]
UNION
SELECT *
FROM FAST_3
WHERE NUMB_SUBSCR=[NUMB]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have 3 identical (having same fields) tables with different names
(FAST_1,
FAST_2, FAST_3).
I need to create a query (using SQL) in order to search the tables for a
specific record and when found to display all the fields of that record
FROM
THAT PARTICULAR TABLE.

What I did so far is:

SELECT FAST_1.*, FAST_2.*, FAST_3.*
FROM FAST_1, FAST_2, FAST_3
WHERE (((FAST_1.NUMB_SUBSCR)=[NUMB])) Or (((FAST_2.)NUMB_SUBSCR=[NUMB]))
Or
(((FAST_3.NUMB_SUBSCR)=[NUMB]));

but it returns all the records.

Anybody knows how to proceed?

Thank you for your help and time