Forcing a datatype in MS Access make table query

I have a query in MS Access which creates a table from two subqueries. For two of the columns being created, I'm di开发者_如何转开发viding 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?

---

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.

---

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.

---

You can use CDbl around the columns.

---

An easy way to do this is to create an empty table with the correct field types and then to an Append-To query and Access will automatically convert the data to the destination field.

---

I had a similar situation, but I had a make-table query creating a field with NUMERIC datatype that I wanted to be short text.

What I did (and I got the idea from Stack) is to create the table with the field in question as Short Text, and at the same time build a delete query to scrub the records. I think it's funny that a DELETE query in access doesn't delete the table, just the records in it - I guess you have to use a DROP TABLE function for that, to purge a table...

Then, I converted my make-table query to an APPEND query, which I'd never done before... and I just added the running of the DELETE query to my process.

Thank you, Stack Overflow !

Steve

---

I add a '& ""' to the field I want to make sure are stored as text, and a ' *1 ' (as in multiplying the amount by 1) to the fields I want to store as numeric.

Seems to do the trick.

---

To get an Access query to create a table with three numeric output fields from input numeric fields, (it kept wanting to make the output fields text fields), had to combine several of the above suggestions. Pre-establish an empty output table with pre-defined output fields as integer, double and double. In the append query itself, multiply the numeric fields by one. It worked. Finally.