Problems with two tables and there information

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello I am creating a search page for two sets of the same
data (when I say the same I actually mean for two sets of
diffrent bases. So I would like to be able to search by
both of them on the one form as its the same information.
When I try to get a query together that shows all the
information it seems to show the data about 20 or so times
for each record.

Does anyone know how I can solve this?

Many Thanks

James
 
Hi,




SELECT a.f1, a.f2 FROM a

UNION ALL

SELECT b.f1, b.f2 FROM b




will merge, vertically, fields f1 and f2 from both tables a and b, without
amplifying or removing any records.



SELECT a.f1, a.f2, b.f1, b.f2
FROM a, b


will merge horizontally fields f1 and f2 from both tables, a and b, but if
there is n records in a, and m records in b, you will get n * m records in
the result, since any record in a will be associated to each record in b.


You probably used something like the second case, while you want something
like in the first case.



Hoping it may help,
Vanderghast, Access MVP
 
Hello I am slightly confused as to your answer I just want
to get all the records from one table and the records from
another table together so that I can create my search form.

Is there an easy way of doing this?

Many Thanks

James
 
Hi,

Yes. Use the first method. Make a query, go in SQL view, you have to,
since graphically, you can't make that kind of query, and then, type


SELECT f1, f2 FROM firstTable
UNION ALL
SELECT g1, g2 FROM secondTable


and save that query. Here, I just use two fields, but you can use more (but
the same number, for each table). Use that query, instead of the table, as
"source".



Hoping it may help,
Vanderghast, Access MVP
 
Hello I think this is where the problem lies as I have
more fields in one table than I do another. Yet I need all
of the fields to runin the query as I need to be able to
display all the results.

Many Thanks

James
 
Hi,


Use the table with the largest number of fields first, "fill" the second
select with Null:


SELECT f1, f2, f3, f4, clientID
FROM table1

UNION ALL

SELECT g1, g2, NULL, NULL, clientID
FROM table2


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top