如何在Access SELECT查询中限制结果

huangapple go评论71阅读模式
英文:

How can I restrict results in an Access SELECT Query

问题

I'm working in Access, querying an assignment table between two tables having a many-to-many relationship. Values from the external Data table are collected together into a group, and that group is given an identifying value from the external ID table. In the target table, the ID value repeats only for each Data item in the group. The Data values may repeat, but never under the same ID value.

IDVal    DataVal
- - - - - - - 
A        100
A        200
A        300
A        400
- - - - - - -
B        200
B        300
B        400
B        500
- - - - - - -
C        100
C        200
C        300
C        400
C        500

From another process, I have an incoming set of Data values (say, 200, 300, 400, 500). I'm trying to write a single query against this table that returns the ID associated with only those incoming values. If I write a regular SELECT query, then I'll get back both B and C as my ID values. How can I make my query return only the ID identifying exactly the data set I'm looking for? I'm not yet proficient enough with SQL to manage this with a single query.

英文:

I'm working in Access, querying an assignment table between two tables having a many-to-many relationship. Values from the external Data table are collected together into a group, and that group is given an identifying value from the external ID table. In the target table, the ID value repeats only for each Data item in the group. The Data values may repeat, but never under the same ID value.

IDVal	DataVal
- - - - - -
A		100
A		200
A		300
A		400
- - - - - -
B		200
B		300
B		400
B		500
- - - - - -
C		100
C		200
C		300
C		400
C		500

From another process, I have an incoming set of Data values (say, 200, 300, 400, 500). I'm trying to write a single query against this table that returns the ID associated with only those incoming values.
If I write a regular SELECT query, then I'll get back both B and C as my ID values.</p> How I can make my query return only the ID identifying exactly the data set I'm looking for? I'm not yet proficient enough with SQL to manage this with with a single query.

答案1

得分: 1

尝试以下查询(可能由查询构造器创建)

SELECT Data.IdVal, Count(Data.DataVal) AS [Count-DataVal]
   ,Count(Test.Data) AS [Count-Data1]
   ,Sum(IIf(IsNull([test].[data]),1,0)) AS sNN
FROM Test RIGHT JOIN Data ON Test.Data = Data.DataVal
GROUP BY Data.Id
HAVING (((Sum(IIf(IsNull([test].[data]),1,0)))=0));

结果

IdVal Count-DataVal Count-Data1 sNN
B 4 4 0

不使用HAVING筛选条件

Id Count-DataVal Count-Data1 sNN
A 4 3 1
B 4 4 0
C 5 4 1

Test表 - 输入数据

Data
200
300
400
500
英文:

Try this query (possible create by Query constructor)

SELECT Data.IdVal, Count(Data.DataVal) AS [Count-DataVal]
   ,Count(Test.Data) AS [Count-Data1]
   ,Sum(IIf(IsNull([test].[data]),1,0)) AS sNN
FROM Test RIGHT JOIN Data ON Test.Data = Data.DataVal
GROUP BY Data.Id
HAVING (((Sum(IIf(IsNull([test].[data]),1,0)))=0));

Result

IdVal Count-DataVal Count-Data1 sNN
B 4 4 0

Without filter by HAVING

Id Count-DataVal Count-Data1 sNN
A 4 3 1
B 4 4 0
C 5 4 1

Test table Test - incoming data

Data
200
300
400
500

huangapple
  • 本文由 发表于 2023年6月9日 03:15:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76435048.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定