Union Queries using WHERE

[Guest]

I have just started using Union Queries and have set up one combining 3 files
with exactly the same layout. - And yes, I am split into FE and BE, the
files are the main DB and various temp files holding the data as it is
entered and checked. At entry the users MUST know if the record is already in
- or in progress to - the main DB (paper forms are duplicated, triplicated,
etc) I have 3 UNION queries in subforms to check the files. One by DOB, one
by FULLNAME, and one by a LOOKUP field. I would like to just have 1 subform
and so one UNION query (for speed and clarity) and would like to know if I
can do this by using the WHERE clause. The form has clients DOB, FULLNAME and
LOOKUP on it so can the UNION be something like

Select 'fields from INPUT FILE'
where DOB = DOB on entry form

Union Select 'fields from MAIN FILE'
where DOB = DOB on entry form

Union Select 'fields from INPUT FILE'
where FULLNAME = FULLNAME on entry form

Union Select 'fields from MAIN FILE'
where FULLNAME = FULLNAME on entry form

If this is possible how do I set it up, I have tried but so far with no
success.

Thanks for any help or any links to information -
Yours -
Dika (Kernow Girl)

 

[Guest]

What you are trying to do should be achievable, all you need to do is make
sure that the number of fields and their data type in each part of the UNION
are identical/compatible so :

SELECT fieldA, fieldB, fieldC
FROM INPUT FILE
WHERE DOB = DOB on entry form
UNION
SELECT fieldX, fieldY, fieldZ
FROM MAIN FILE'
WHERE DOB = DOB on entry form

If fieldY is not present in, for example, MAIN FILE you can include a dummy
field so that the number of columns is the same (assuming fieldB is text),
e.g.

SELECT fieldX, "" as fieldY, fieldZ

 

[aaron.kempf]

MDB is a joke.

split front ends and backends-- i mean.. what a joke!!

start usign a real db SERVER like MSDE or SQL Server
with Access Data Projects; you can keep lots of your existing forms and
reports; and you dont have queries that crap out every 5 minutes

 

[Guest]

Hi Pete - thanks, at least I know I'm on the right track. Do you know - is it
better to make several UNIONs, and then combine them in a UNION, or just
create one big UNION query? Currently the 5 subforms seem to take a long time
painting the screen each time the user enters start entering a record, so I
was hoping that by doing it this way it would work a bit faster.
thanks for your help - Dika