Forcing a datatype in MS Access make table query

Question

I have a query in MS Access which creates a table from two subqueries. For two of the columns being created, I'm dividing one column from the first subquery into a column from the second subquery.

The datatype of the first column is a double; the datatype of the second column is decimal, with scale of 2, but I want the second column to be a double as well.

Is there a way to force the datatype when creating a table through a standard make-table Access query?

Answer 1

One way to do it is to explicitly create the table before putting anything into it.

Your current statement is probably like this:

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
WHERE FirstName = 'Alistair'

But you can also do this:

----Create NewTable
CREATE TABLE NewTable(FirstName VARCHAR(100), LastName VARCHAR(100), Total DOUBLE)
----INSERT INTO NewTableusing SELECT
INSERT INTO NewTable(FirstName, LastName, Total)
SELECT FirstName, LastName, 
FROM Person p
INNER JOIN Orders o
ON p.P_Id = o.P_Id
WHERE p.FirstName = 'Alistair'

This way you have total control over the column types. You can always drop the table later if you need to recreate it.

Answer 2

You can use the cast to FLOAT function CDBL() but, somewhat bizarrely, the Access Database Engine cannot handle the NULL value, so you must handle this yourself e.g.

SELECT first_column, 
       IIF(second_column IS NULL, NULL, CDBL(second_column)) 
          AS second_column_as_float
  INTO Table666
  FROM MyTest;

...but you're going to need to ALTER TABLE to add your keys, constraints, etc. Better to simply CREATE TABLE first then use INSERT INTO..SELECT to populate it.