C*******U 发帖数: 475 | 1 关于microsoft access 2007的一个问题
一个query 计算的是
Balance on Hand: [Quantity on Hand at Count Date]+[Quantity Received]-[
Quantity Used]
我另外有两个table 一个usage table 还有order table, 但是 并不是每一个part 都
会有数据 , 比如算inventory stock number 3的时候, at count date 是 10 但是
quantyty received 还有 quantity used 都没有数据, 如果有的话我可以设置为
null 但是这两个在任何表里面都没有数据的情况下 我如何写expressiong, 可以让系
统去找, 找不到的时候就把这两个数据设置为0 |
n********6 发帖数: 1511 | 2 Short answer: ISNULL()
LONG ANSWER:
In SQL Server environment, the main logic might be this:
Define
@count int
select @count = sum(...) from ... where ...
SELECT ISNULL (@count, 0) /* output */
If the @count contains value, the output will be the value
If the @count IS NULL, the output will be 0
【在 C*******U 的大作中提到】 : 关于microsoft access 2007的一个问题 : 一个query 计算的是 : Balance on Hand: [Quantity on Hand at Count Date]+[Quantity Received]-[ : Quantity Used] : 我另外有两个table 一个usage table 还有order table, 但是 并不是每一个part 都 : 会有数据 , 比如算inventory stock number 3的时候, at count date 是 10 但是 : quantyty received 还有 quantity used 都没有数据, 如果有的话我可以设置为 : null 但是这两个在任何表里面都没有数据的情况下 我如何写expressiong, 可以让系 : 统去找, 找不到的时候就把这两个数据设置为0
|
C*******U 发帖数: 475 | 3 有没有人access用的很熟练呀, 在这里很难形容我的问题,如果可以, 我可以把文件
发给高人看看 ,谢谢了 |
C*******U 发帖数: 475 | 4
刚刚看到, 我试试看 谢谢了
【在 n********6 的大作中提到】 : Short answer: ISNULL() : LONG ANSWER: : In SQL Server environment, the main logic might be this: : Define : @count int : select @count = sum(...) from ... where ... : SELECT ISNULL (@count, 0) /* output */ : If the @count contains value, the output will be the value : If the @count IS NULL, the output will be 0
|
C*******U 发帖数: 475 | 5 SELECT Parts.[Inventory Stock Number], Parts.Description, Parts.Vendor,
Parts.[Cost per unit], [Quantity on Hand at Count Date]+[Quantity Recieved]-
[Quantity Used] AS [Balance on Hand]
FROM (Parts INNER JOIN [Inventory Orders] ON Parts.[Inventory Stock Number]
= [Inventory Orders].[Inventory Stock Number]) INNER JOIN [Inventory Usage]
ON Parts.[Inventory Stock Number] = [Inventory Usage].[Inventory Stock
Number]
WHERE (((Parts.[Inventory Stock Number])=[Desired Inventory Stock Number]));
我输入 in |
C*******U 发帖数: 475 | 6 SELECT Parts.[Inventory Stock Number], Parts.Description, Parts.Vendor,
Parts.[Cost per unit], [Quantity on Hand at Count Date]+[Quantity Recieved]-
[Quantity Used] AS [Balance on Hand]
FROM (Parts INNER JOIN [Inventory Orders] ON Parts.[Inventory Stock Number]
= [Inventory Orders].[Inventory Stock Number]) INNER JOIN [Inventory Usage]
ON Parts.[Inventory Stock Number] = [Inventory Usage].[Inventory Stock
Number]
WHERE (((Parts.[Inventory Stock Number])=[Desired Inventory Stock Number]));
我输入 in |
C*******U 发帖数: 475 | 7 这是query需要调数据的三个table截屏图
在order 哪里 一个是 quantity order 一个 quantity received, 有点看不清楚 |
C*******U 发帖数: 475 | |
C*******U 发帖数: 475 | |
n********6 发帖数: 1511 | 10 IF EXISTS(SELECT Parts.InventoryStockNumber FROM ...)
(Your select statement)
google 'sql, exists'
there are other solution.
]-
]
]
);
table
【在 C*******U 的大作中提到】 : SELECT Parts.[Inventory Stock Number], Parts.Description, Parts.Vendor, : Parts.[Cost per unit], [Quantity on Hand at Count Date]+[Quantity Recieved]- : [Quantity Used] AS [Balance on Hand] : FROM (Parts INNER JOIN [Inventory Orders] ON Parts.[Inventory Stock Number] : = [Inventory Orders].[Inventory Stock Number]) INNER JOIN [Inventory Usage] : ON Parts.[Inventory Stock Number] = [Inventory Usage].[Inventory Stock : Number] : WHERE (((Parts.[Inventory Stock Number])=[Desired Inventory Stock Number])); : 我输入 in
|
|
|
B*****g 发帖数: 34098 | 11 http://office.microsoft.com/en-us/access/HA010345551033.aspx
记住,下次急的时候要发包子。hehe
【在 C*******U 的大作中提到】 : 关于microsoft access 2007的一个问题 : 一个query 计算的是 : Balance on Hand: [Quantity on Hand at Count Date]+[Quantity Received]-[ : Quantity Used] : 我另外有两个table 一个usage table 还有order table, 但是 并不是每一个part 都 : 会有数据 , 比如算inventory stock number 3的时候, at count date 是 10 但是 : quantyty received 还有 quantity used 都没有数据, 如果有的话我可以设置为 : null 但是这两个在任何表里面都没有数据的情况下 我如何写expressiong, 可以让系 : 统去找, 找不到的时候就把这两个数据设置为0
|
n********6 发帖数: 1511 | 12 Beijing is a good teacher. Not only provides fish, but also teaches fishing.
【在 B*****g 的大作中提到】 : http://office.microsoft.com/en-us/access/HA010345551033.aspx : 记住,下次急的时候要发包子。hehe
|
B*****g 发帖数: 34098 | 13 其实我就是google
fishing.
【在 n********6 的大作中提到】 : Beijing is a good teacher. Not only provides fish, but also teaches fishing.
|
n********6 发帖数: 1511 | 14 In addition, beijing also teaches googling.
【在 B*****g 的大作中提到】 : 其实我就是google : : fishing.
|
w*******e 发帖数: 1622 | 15 hehehe...
【在 n********6 的大作中提到】 : In addition, beijing also teaches googling.
|