Results 1 to 3 of 3
  1. #1
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,853

    Similar to DUAL in Oracle

    There are times when you want to add a Name, or name and DOB etc to a list of values from a table but you don't necessarily want to add it to the table. In Oracle there is a "special table*" called DUAL, where you can "SELECT SysDate from Dual" and similar, but nothing in Access.



    There may be other ways to do this, but this is fairly simple technique that came to mind in responding to a poster. The issue was to include a temporary Vendor to a List of Vendors from the VendorTable for use with a combo. And they did not want to add this temp vendor to their VendorTable.

    The approach is to create a query and save it. I saved it as Dual (to keep the concept).

    The query does nothing but provides the proper syntax for Access sql. It does nothing with data since 1 is never equal to 2. But it does provide a reference to an existing table that is always available.

    The sql for query DUAL:
    Code:
      select count(*) from MSysobjects where 1 = 2
    Usage becomes: (typical example)

    Code:
    SELECT ancestor.name, ancestor.birthdate
    FROM ancestor
    UNION 
    Select "Ezra",#6/11/2013# from DUAL;
    My sample result
    Code:
    name birthdate
    bob 31/10/1922
    Ezra 11/06/2013
    jim 22/03/1944
    ken 09/09/1999
    sam 01/11/1922
    I realize you could do this by including the sql for DUAL as a subquery, but thought this (using DUAL query) was a little easier to remember.

    Code:
    SELECT ancestor.name, ancestor.birthdate
    FROM ancestor
    UNION 
    Select "Ezra",#6/11/1913# from (select count(*) from MSysobjects where 1 = 2);
    My regional settings (Canada) display dates in DD/MM/YY format. Dates are still entered in US MM/DD/YY format.
    Last edited by orange; 04-29-2016 at 08:41 PM.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,073
    I saw the vendor thread you refer to, and the idea of a union query seems like a good one to me. The first row in the table below is the data in a source table. Using your idea, the second row is the result of the union statement, which seems to produce the result required.
    Code:
    SELECT tblFieldNames.* FROM tblFieldNames
    UNION SELECT "Sam","Spade","Test" FROM tblFieldNames;
    Name1 Name2 Name3
    apple orange lemon
    Sam Spade Test

    I'm not understanding the need for the Dual part. Could you elaborate?
    Thanks.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,853
    The UNION select ... will work as long as the table referenced is available. The DUAL approach is very common with Oracle users. I was looking for similar set up.
    You often see requests to include 'All', along with entries in a table, in a combo. This is another way to do it.

    SELECT "All" from dual

    There is nothing special about what I posted. It is just one way to achieve the inclusion of an additional value(s) with values in an existing table without updating the table.

    Where in Ontario?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-07-2018, 04:59 PM
  2. Filter dual-column combo box
    By lisa071 in forum Forms
    Replies: 1
    Last Post: 03-31-2015, 12:18 PM
  3. Dual language message box
    By Derrick T. Davidson in forum Programming
    Replies: 1
    Last Post: 07-10-2013, 07:11 AM
  4. Replies: 1
    Last Post: 12-03-2011, 01:26 AM
  5. Grouping dual keys in Access 2007 Report
    By bengineer in forum Reports
    Replies: 4
    Last Post: 02-01-2011, 02:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums