UPDATE a table using results of a UNION query

• Keith Walter-210080

  Valued Member

  Points: 50

  More actions

  May 18, 2005 at 6:36 am

  #67512

  Hi,  I am having trouble figuring out how to do this, I am a bit green so there may be a better way than my approach.  I have a series of queries (2) in an Access Front-End that are quite slow.  The first is a UNION that joins 2 views, the second is an update that sets a flag in a table.  I first tried to duplicate the UNION query in a view like so, but apparently views do not support Unions(?)

  SELECT     Name, MaxOfDateQC

  FROM         vw_vendor_current_mech

  UNION ALL

  SELECT     Name, MaxOfDateQC

  FROM         vw_vendor_current_elec;

   

  I then tried the above in Query builder, and it works fine.  The second query, however, is an UPDATE that uses this UNION as its source.    Ideally I could put this all in a stored proceedure that could run when my form opens.  This is in essencse what I want to do, but it doesn't work:

  UPDATE TblVendors SET TblVendors.Vendor = 1

  WHERE TblVendors.Name IN (SELECT [Name] FROM 

    (SELECT     Name, MaxOfDateQC

    FROM         vw_vendor_current_mech

    UNION ALL

    SELECT     Name, MaxOfDateQC

    FROM         vw_vendor_current_elec);

  Thanks for your help.

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  May 18, 2005 at 7:04 am

  #559556

  You can create a view to hold the output of the two queries as

  CREATE VIEW MYUNIONDATE AS

  (SELECT     Name, MaxOfDateQC

    FROM         vw_vendor_current_mech

    UNION ALL

    SELECT     Name, MaxOfDateQC

    FROM         vw_vendor_current_elec)

  GO

  UPDATE TblVendors SET TblVendors.Vendor = 1

  WHERE TblVendors.Name IN (SELECT [Name] FROM MYUNIONDATE)

  Thanks

  Prasad Bhogadi
  www.inforaise.com

• Keith Walter-210080

  Valued Member

  Points: 50

  More actions

  May 18, 2005 at 7:18 am

  #559564

  Perfect, Thanks!

  -Keith