ms-access - 带有 Sum 和额外条件的 msAccess 查询

Question

我需要在特定月份获取 Dog 的所有 Costs 值。当我将此代码与 Access 一起使用时，它说不支持加入操作。在 MS Access 中是否有更好的方法来实现这一点？我需要所有狗的名字都回来，即使它们在特定月份没有与之相关的费用

Select Dog.DogName, Dog.DogOwner, Sum(Costs.CostAmount)
From 
(Dog Left join Costs on Dog.DogName = Costs.DogName and Costs.CostMonth = 10)
Group by Dog.DogName, Dog.OwnerName

标签： ms-access

Answer 1

尝试这个：

Select 
    Dog.DogName, Dog.DogOwner, Sum(Costs.CostAmount) As TotalAmount
From 
    Dog 
Left join 
    Costs 
    On 
       (Dog.DogName = Costs.DogName) 
Where
    Costs.CostMonth <= Month(Date())
    Or
    Costs.CostMonth Is Null
Group by 
    Dog.DogName, Dog.OwnerName