“表达不正确或太复杂". [英] "Expression is either incorrect or too complex"

问题描述

我认为我需要学习如何更优雅地编写以下代码.问题是我正在处理一堆表(不是由我创建这些表的，否则我会将其简化为一个表.)我需要一次针对所有查询信息.我正在使用MS Access，目前这是我的SQL语句:

I believe I'm in need of learning how to write the following code more elegantly. The issue is that I'm working with a bunch of tables (Wasn't me who created these or I would have simplified it down to one.) & I'm needing to query information against all of them at once. I'm working with MS Access and this is what my SQL statement looks like at the moment:

SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From [tblReports_16Braiding]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
    Union
    SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From  [tblReports_24Braiding]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
    Union
SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From [tblReports_Cabling]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
Union
SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
 From [tblReports_CompChain]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
Union
SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From [tblReports_Jacketing]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
Union
SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From [tblReports_Primary]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
Union
SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From [tblReports_Printing]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
Union
SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
From [tblReports_Respool]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);
UNION SELECT [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
 From [tblReports_Stranding]
WHERE ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);

简而言之，我只是试图将大量的表合并在一起.全部具有相同的字段，从另外两个字段的简单划分中创建一个新字段，然后使用表单查询联合表.每当我现在运行此命令时，都会收到一个错误，提示我的SQL错误或过于复杂.我猜测太复杂了，因为我正在处理九个表中的20,000多个记录.

In short, I'm just trying to union together a ton of tables. All with the same fields, create a new field from a simple division of two other fields, and then query against the unioned table with a form. Whenever I run this right now, I get an error that my SQL is wrong or too complex. I'm guessing too complex as I'm working with over 20,000 records across the nine tables.

编辑~~~

在我最大的表上删除操作Expression时，大约有8000个条目，代码可以正常工作.进行此操作:

Upon removing the operation Expression on my largest table, about 8000 entries, the code works fine. Taking out this operation:

[Feet Produced]/[Run Time] AS [AVG Prod]
    From  [tblReports_24Braiding]

但是我在查询中需要此字段.我无法在表本身中执行此操作，因为它是链接表，因此无法保存对其的更改.

I need this field however in my query. I'm unable to do this in the table itself, because it's a linked table and therefore I can't save changes to it.

推荐答案

明确声明参数以使Access免受猜测:

Explicitly declare the parameters to free Access from guessing:

PARAMETERS
    [Forms]![AdminProdForm].[PartNumber]) Long,
    [Forms]![AdminProdForm].[StartDate] DateTime,
    [Forms]![AdminProdForm].[EndDate] DateTime;

SELECT 
    [Date (mm-dd-yy)], [Operator], [Operator Number], [Product Part Number], [Feet Produced], [Run Time], [Feet Produced]/[Run Time] AS [AVG Prod]
FROM 
    [tblReports_16Braiding]
WHERE 
    ((([Product Part Number])=[Forms]![AdminProdForm].[PartNumber]) AND [Date (mm-dd-yy)] Between [Forms]![AdminProdForm].[StartDate] And [Forms]![AdminProdForm].[EndDate]);

UNION ALL

SELECT ...  <snip>

这篇关于“表达不正确或太复杂".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！