Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Union Queries using WHERE

Discussion in 'Information Technology' started by Kernow Girl, Nov 15, 2005.

  1. Kernow Girl

    Kernow Girl
    Expand Collapse
    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)
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh Union Make Special Visit To Houses Of Parliament To Showcase Their Community Work - Breaking News Jul 30, 2016
    Heritage India's Union Sports Ministry To Promote Gatka History of Sikhism Oct 23, 2013
    Hi-Tech Unites the World's Sikhs in Daily Communion Sikh Sikhi Sikhism Jun 29, 2013
    The Holy Union of Anand Karaj New to Sikhism Mar 16, 2013
    General Union with God (BoKSD 6) Hard Talk Dec 4, 2012

  3. Pete

    Pete
    Expand Collapse
    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

    --
    Peter Schmidt
    Ross-on-Wye, UK


    "Kernow Girl" wrote:

    > 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)
     
  4. Kernow Girl

    Kernow Girl
    Expand Collapse
    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

    "Pete" wrote:

    > 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
    >
    > --
    > Peter Schmidt
    > Ross-on-Wye, UK
    >
    >
    > "Kernow Girl" wrote:
    >
    > > 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)
     

Share This Page