组合选择查询会产生欺骗 [英] Combining select queries produces dupes

问题描述

我是MS Access新手，需要帮助。我已经构建了多个选择的quieries，当我将它们加入到一个查询中时，我得到了欺骗。问题：如何消除关节上的欺骗？


以下是其中一个选择查询的示例：


SVP帐户名称DOB CountOfEmpID

名称帐户P¥FFN-N 13

名称账号P¥FFN-Y 19354


另一个选择查询：


SVP账户名PFName CountOfEmpID

姓名账户P¥FSN-Y 3482

姓名账户P¥FSN-N 15885



以下是我加入他们时发生的事情：


SVP账户DOB CountDOB PFName CountofEmpID

名称账户P¥DOB-N 19354 P¥FFN-N 15885

名称账户P¥DOB-Y 13 P¥FFN-N 15885

名称账户P¥DOB-N 19354 P¥FFN-Y 3482

名称账号P¥DOB-Y 13 P¥FFN-Y 3482

I am new to MS Access and need help. I have built multiple select quieries and when I am joining them into one query I get dupes. Question: how do I eliminate dupes on joint?

Here is a sample of one of the select query:

SVP Account Name DOB CountOfEmpID
Name Account P￥FFN-N 13
Name Account P￥FFN-Y 19354


Another select query:

SVP Account Name PFName CountOfEmpID
Name Account P￥FSN-Y 3482
Name Account P￥FSN-N 15885


Here is what happenes when I join them together:

SVP Account DOB CountDOB PFName CountofEmpID
Name Account P￥DOB-N 19354 P￥FFN-N 15885
Name Account P￥DOB-Y 13 P￥FFN-N 15885
Name Account P￥DOB-N 19354 P￥FFN-Y 3482
Name Account P￥DOB-Y 13 P￥FFN-Y 3482

推荐答案

如果您可以发布SQL实际上是SELECT查询和你试图加入它的那个查询会有所帮助我们看看究竟发生了什么。


通常情况下，如果你加入两个选择查询和重复结果，你的查询中就会缺少一个连接。


由于您列出的行中不包含测试值，我无法判断您发布的内容 - 每行都重复名称和帐户，而不是帮助我们知道它们代表什么，根据标题（DOB）可能的出生日期是不可识别的（P¥DOB-Y）。

-Stewart

If you could post the SQL you are actually for both SELECT queries and the one you are trying to get to JOIN it would help us to see what is actually going on.

Normally, if you are joining two select queries and duplicates result you have a missing join in your query.

I can''t tell from what you''ve posted as the lines you list do not contain test values within them - Name and Account are repeated on each line, which does not help us to know what they represent, and what might be a date of birth according to the heading (DOB) is not recognisable in the way you''ve listed it (P￥DOB-Y).

-Stewart

谢谢。以下是PDOB查询的SQL：


SELECT [AccountName查询]。[Ops SVP]，[AccountName查询]。帐户，[帐户名查询] .PDOB，计数（[AccountName]查询] .EmpID）AS CountOfEmpID

FROM [AccountName查询]

GROUP BY [AccountName查询]。[Ops SVP]，[AccountName查询]。帐户，[AccountName查询] .PDOB;


这是PFirstName查询的SQL：


SELECT [AccountName查询]。[Ops SVP]，[AccountName查询] ] .Account，[AccountName Query] .PFirstName，Count（[AccountName Query] .EmpID）AS CountOfEmpID

FROM [AccountName查询]

GROUP BY [AccountName查询]。 [Ops SVP]，[AccountName查询]。帐户，[帐户名称查询] .PFirstName;


这是联合查询的SQL：


SELECT [P-DOB]。[Ops SVP]，[P-DOB] .Account，[P-DOB] .PDOB，[P-DOB] .CountOfEmpID，[P-FirstName] .PFirstName，[P-FirstName ] .CountOfEmpID

FROM [P-DOB]，[P-FirstName]，[P-LastName]，[P-MiddleName];

Thank you. Here is the SQL for the PDOB query:

SELECT [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PDOB, Count([AccountName Query].EmpID) AS CountOfEmpID
FROM [AccountName Query]
GROUP BY [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PDOB;

Here is the SQL for PFirstName query:

SELECT [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PFirstName, Count([AccountName Query].EmpID) AS CountOfEmpID
FROM [AccountName Query]
GROUP BY [AccountName Query].[Ops SVP], [AccountName Query].Account, [AccountName Query].PFirstName;

Here is the SQL for the joint query:

SELECT [P-DOB].[Ops SVP], [P-DOB].Account, [P-DOB].PDOB, [P-DOB].CountOfEmpID, [P-FirstName].PFirstName, [P-FirstName].CountOfEmpID
FROM [P-DOB], [P-FirstName], [P-LastName], [P-MiddleName];

请参阅上面的回复。对不起混淆

Please see the response above. Sorry for confusion

这篇关于组合选择查询会产生欺骗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！