SQL 在 MS Access 中强制使用数据类型进行表查询

Forcing a datatype in MS Access make table query

提问by Alistair Knock

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.

我在 MS Access 中有一个查询，它从两个子查询创建一个表。对于正在创建的两列，我将第一个子查询中的一列划分为第二个子查询中的一列。

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.

第一列的数据类型是双精度；第二列的数据类型是十进制，小数位数为 2，但我希望第二列也是双精度数。

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

在通过标准的 make-table Access 查询创建表时，有没有办法强制使用数据类型？

回答by Rap

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.

这样您就可以完全控制列类型。如果您需要重新创建它，您可以随时删除该表。

回答by onedaywhen

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

您可以使用强制转换来FLOAT运行，CDBL()但有些奇怪的是，Access 数据库引擎无法处理该NULL值，因此您必须自己处理，例如

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 TABLEto add your keys, constraints, etc. Better to simply CREATE TABLEfirst then use INSERT INTO..SELECTto populate it.

...但您将需要ALTER TABLE添加您的键、约束等。最好先简单地CREATE TABLE然后使用INSERT INTO..SELECT来填充它。

回答by Scott Bainbridge

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.

一种简单的方法是创建一个具有正确字段类型的空表，然后创建一个附加到查询，Access 会自动将数据转换为目标字段。

回答by Steve R

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.

我遇到了类似的情况，但我有一个生成表查询，创建了一个我想成为短文本的 NUMERIC 数据类型的字段。

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...

我所做的（并且我从 Stack 得到了这个想法）是将有问题的字段创建为短文本表，同时构建一个删除查询来清理记录。我认为有趣的是，访问中的 DELETE 查询不会删除表，只会删除其中的记录 - 我猜你必须为此使用 DROP 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.

然后，我将我的 make-table 查询转换为 APPEND 查询，这是我以前从未做过的……我只是将 DELETE 查询的运行添加到我的进程中。

Thank you, Stack Overflow !

谢谢你，堆栈溢出！

Steve

史蒂夫

回答by LateAnswer

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.

我在要确保存储为文本的字段中添加了一个 '& ""'，在要存储为数字的字段中添加了一个 ' *1 '（如将数量乘以 1）。

Seems to do the trick.

似乎可以解决问题。

回答by Adriaan Stander

You can use CDblaround the columns.

您可以在列周围使用CDbl。