SQL代码效率&Microsoft Access 的编译问题 [英] SQL code efficiency & compiling issues with Microsoft Access

问题描述

有没有办法以更有效的方式设置此代码?我正在尝试过滤几个不同的标准，甚至编译代码也很困难.同样，所有 IS NULL 的原因是我想让它在没有输入信息的情况下只接受所有数据.

Is there a way to setup this code in a more efficient way? I'm trying to filter through several different criteria and I'm having a hard time even compiling the code. Also the reason for all the IS NULL is that I want to make it so that if no information is inputted, for it to just accept all data.

有人对我如何优化此代码有任何提示吗?特别是 WHERE 部分.是否有我可以使用的 if then 语句?还是索引?

Anyone got any tips for how I can optimize this code? Specifically the WHERE section. Is there a if then statement that I could use? Or an Index?

SELECT [Table Material Label].Serial, [Table Material Label].[Date Recieved], [Table Material Label].MaterialDescription, [Table Material Label].MaterialCode, [Table Material Label].Supplier, 

[Table Material Label].[Lot Number], [Table Material Label].Weight, [Table Material Label].Quantity, [Table Material Label].[Purchase Order Number], [Table Material Label].[Received By], [Table Material Label].[Checked in By], [Table Material Label].[Total Weight]

FROM [Table Material Label]
WHERE (([Table Material Label].[Date Recieved])>=[Forms]![Report Generator]![Text6] 
    AND ([Table Material Label].[Date Recieved])<=[Forms]![Report Generator]![Text7]) 

    AND ([Table Material Label].MaterialDescription = [Forms]![Report Generator]![repMaterial] 
     OR [Forms]![Report Generator]![repMaterial] IS NULL)

    AND ([Table Material Label].MaterialCode = [Forms]![Report Generator]![repItem] 
     OR [Forms]![Report Generator]![repItem] IS NULL)

    AND ([Table Material Label].[Lot Number] = [Forms]![Report Generator]![repLot] 
     OR [Forms]![Report Generator]![repLot] IS NULL)

    AND ([Table Material Label].Weight = [Forms]![Report Generator]![repWeight] 
     OR [Forms]![Report Generator]![repWeight] IS NULL)

    AND ([Table Material Label].Quantity = [Forms]![Report Generator]![repQuantity] 
     OR [Forms]![Report Generator]![RepQuantity] IS NULL)

    AND ([Table Material Label].[Purchase Order Number] = [Forms]![Report Generator]![repPurchaseOrder] 
     OR [Forms]![Report Generator]![repPurchaseOrder] IS NULL)

    AND ([Table Material Label].[Received By] = [Forms]![Report Generator]![repRecBy] 
     OR [Forms]![Report Generator]![repRecBy] IS NULL)

    AND ([Table Material Label].[Checked in By] = [Forms]![Report Generator]![repCheckBy] 
     OR [Forms]![Report Generator]![repCheckBy] IS NULL)

    AND ([Table Material Label].[Total Weight] = [Forms]![Report Generator]![repTotalWeight] 
     OR [Forms]![Report Generator]![repTotalWeight] IS NULL)

    AND ([Table Material Label].Supplier = [Forms]![Report Generator]![Supp] 
     OR [Forms]![Report Generator]![Supp] IS NULL)


ORDER BY [Table Material Label].[Date Recieved], [Table Material Label].MaterialDescription, [Table Material Label].MaterialCode, [Table Material Label].Supplier;

推荐答案

你的 WHERE 代码是最简单的，至少我不知道有更简单的解决方案.每个条件有两个条件，如果您需要显示空条件的所有记录，这是最低要求.此代码只有一个问题 - 您应该将查询保留为 SQL 文本格式.如果您切换到查询构建器，它将变得一团糟，几乎无法阅读.由于 SQL 文本并不复杂，因此运行此查询不会有任何问题，它不会被编译.当然，您应该为 WHERE 子句中的每个字段设置索引以获得更好的性能.

Your code for WHERE is simplest, at least I don't know any simpler solutions. You have two conditions per each criteria, this is minimum if you need to show all records for empty criteria. Only one problem with this code - you should keep the query in SQL text format. If you switch to query builder, it will make total mess, almost unreadable. As SQL text it's not complicated, you should not have any problems with running this query, it won't be compiled. Of course you should have indexes for each field in WHERE clause for better performance.

这篇关于SQL代码效率&Microsoft Access 的编译问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！