想要查询不要求字段中的数据。 [英] Want query not to require data in fields.

问题描述

我正在使用Access 2000

我有三个表用于创建将用于数据输入表单的查询。表一 - 客户列表，表二 - 服务，表三 - 截止日期。

我想创建一个表格，最多可以在同一表格上输入12项服务，所以我创建了Services表，其中包含12个服务的字段，包括与每个服务一起提供的其他信息的字段（保证日期，截止日期，分机日期）。每个服务字段和相应字段的编号为1 - 12。服务字段是链接到截止日期表的查找字段截止日期表只有两个字段 - 它列出了每个不同的服务以及该服务的截止日期（如果有）。

我遇到的麻烦是，除非在所有 12服务字段中输入值，否则记录将不会显示在查询中，即使我已为每个服务字段设置了表属性必需：否和允许零长度：是


有些客户可能只需要一项服务 - 如何让这个bug工作？

有人能帮帮我吗？谢谢

I am using Access 2000

I have three tables I am using to create a query that will be used for a data entry form. Table one - Client List, Table two - Services, Table three - Due Dates.

I want to create the form with room for up to 12 services to be entered on the same form, so I created the Services table with fields for 12 services including fields for other information that would go along with each of the services (promise date, due date, ext date) Each of the service fields and corresponding fields are numbered 1 - 12. The Service fields are look-up fields that are linked to the Due Dates table The due dates table only has two fields - it lists each of the different services and the due date for that service if there is one.

The trouble I am having is that unless a value is entered in all 12 service fields the record will not show in the query, even though I have set the table properties for each service field as Required:No and Allow Zero Length: Yes

Some clients may require only one service - how do I get this bugger to work?
Can some one help me? Thank You

推荐答案

KayCee再次来到这里 -


我从未使用过SQL，但我想从中添加SQL查询是否有帮助。这仅适用于4个服务而不是全部12个。


选择服务。[客户端＃]，[客户端列表]。[客户端名称]，[客户端列表] .Type，[客户端列表]。合作伙伴，服务。[服务1]，服务。[承诺日期1]，[到期日期]。[到期日1]，服务。[OUT 1]，服务。[Ext DONE 1]，服务。[发布日期] 1]，服务。[e文件1]，服务。[注1]，服务。[服务2]，服务。[承诺日期2]，[到期日_1]。[到期日2]，服务。[OUT 2] ，服务。[Ext DONE 2]，SERVICES。[Ext Date 2]，SERVICES。[e file 2]，SERVICES。[Notes 2]，SERVICES。[Service 3]，SERVICES。[Promise Date 3]，[Due Dates_2] ]。[到期日3]，服务。[OUT 3]，服务。[Ext DONE 3]，SERVICES。[Ext Date 3]，SERVICES。[e file 3]，SERVICES。[Notes 3]，SERVICES。[Service 4]，服务。[承诺日期4]，[截止日期_3]。[截止日期4]，服务。[OUT 4]，服务。[分机4]，服务。[发布日期4]，服务。[e档案4]，服务。[注4]

FROM（（（[客户名单] [客户名单]内部联合服务。[客户＃] =服务。[客户＃]）内部联接[截止日期]服务。[服务1] = [截止日期]。服务）内部联接[截止日期] AS [到期日期_1]服务。[服务2] = [到期日期_1]。服务）内部联接[截止日期] AS [到期日_2]服务。[服务3] = [到期日_2]。服务）内部联接[到期日期] AS [到期日_3]服务。[服务4] = [到期日_3]。服务;



再次感谢

KayCee here again -

I have never used the SQL, but I wanted to add the SQL from the query if that helps. This is for just 4 services not all 12.

SELECT SERVICES.[Client #], [CLIENT LIST].[Client Name], [CLIENT LIST].Type, [CLIENT LIST].Partner, SERVICES.[Service 1], SERVICES.[Promise Date 1], [Due Dates].[Due Date 1], SERVICES.[OUT 1], SERVICES.[Ext DONE 1], SERVICES.[Ext Date 1], SERVICES.[e file 1], SERVICES.[Notes 1], SERVICES.[Service 2], SERVICES.[Promise Date 2], [Due Dates_1].[Due Date 2], SERVICES.[OUT 2], SERVICES.[Ext DONE 2], SERVICES.[Ext Date 2], SERVICES.[e file 2], SERVICES.[Notes 2], SERVICES.[Service 3], SERVICES.[Promise Date 3], [Due Dates_2].[Due Date 3], SERVICES.[OUT 3], SERVICES.[Ext DONE 3], SERVICES.[Ext Date 3], SERVICES.[e file 3], SERVICES.[Notes 3], SERVICES.[Service 4], SERVICES.[Promise Date 4], [Due Dates_3].[Due Date 4], SERVICES.[OUT 4], SERVICES.[Ext DONE 4], SERVICES.[Ext Date 4], SERVICES.[e file 4], SERVICES.[Notes 4]
FROM (((([CLIENT LIST] INNER JOIN SERVICES ON [CLIENT LIST].[Client #] = SERVICES.[Client #]) INNER JOIN [Due Dates] ON SERVICES.[Service 1] = [Due Dates].Service) INNER JOIN [Due Dates] AS [Due Dates_1] ON SERVICES.[Service 2] = [Due Dates_1].Service) INNER JOIN [Due Dates] AS [Due Dates_2] ON SERVICES.[Service 3] = [Due Dates_2].Service) INNER JOIN [Due Dates] AS [Due Dates_3] ON SERVICES.[Service 4] = [Due Dates_3].Service;


Thanks again

KayCee，


使用RDBMS的方法不是处理多个单个记录中的条目 - 虽然看起来是可能的。

这一切都会以泪水结束。


...但问题是你所有的JOIN都是INNER JOINS。我希望LEFT JOIN可能适合你。

KayCee,

The way to use an RDBMS is not to handle multiple entries in a single record - although it seems possible.
It will all end in tears.

...But your problem is that all your JOINs are INNER JOINS. I would expect that LEFT JOINs may work for you.

谢谢NeoPa和我一起看这个。对不起，我不知道RDBMS代表什么。设置它的最佳方式是什么 - 我不需要任何眼泪。


我尝试了LEFT JOIN，但它填充了我对所有客户端及其信息的查询来自客户列表表。

Thank you NeoPa for looking at this with me. Sorry I do not know what RDBMS stands for. What is the best way to set this up - I don''t need any tears.

I tryed the LEFT JOIN, but it populates my query with all of the Clients and thier information from the ''Client List'' Table.

这篇关于想要查询不要求字段中的数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！