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.