retrieving unique record from identical tables

[Guest]

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)

 

[StopThisAdvertising]

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

Hey Steve !!
Well... We did know that you are NOT an Access resource at all !!
But .... A REAL resource you are with Excel .... Ha ha ha. !!
Asking questions about looping a spreadsheet, asking about Excel-events, not knowing how to add buttons and code ???
http://groups.google.nl/group/microsoft.public.excel.programming/msg/d7d79687dec86195?hl=nl
http://groups.google.nl/group/microsoft.public.excel.programming/msg/a4a8b9b40fe5c313?hl=nl
http://groups.google.nl/group/microsoft.public.excel.programming/msg/46a46c0dc6dc9c15?hl=nl

Curious about your Word skills... You DO know that you can bold text and such ??

This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html (updated, mainly the 'abuse-reporting' page...)
Until now 3950+ pageloads, 2500+ first-time visitors
(these figures are real and rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==> We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==> Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR

 

[Guest]

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

 

[Guest]

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