[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

Adodc Access Query

What is the correct syntax to query a access database that will sum one field and sum the same field if a certain date is is contained in that field
my code is

Dim mannasum As Currency
Dim mannaredeemed As Currency
Dim ddate As Date
ddate = "1/1/2000"

With Adodc1
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            "\test\Database\Manna Gold.mdb;Persist Security Info=False"
      .RecordSource = "select sum([Amount]) as TotalMannaSum from [Manna Gold] & WHERE [exchanged date]>#" & ddate & "# "
      .Refresh
End With

This query works but I want to also want to get the sum of all [Amount]. Can this be in the same query or do I need a second query?


0
whiwex
Asked:
whiwex
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
You need to do it as a second query.
0
 
iHadiCommented:
You can do it in two ways:

This query will return a one column two rows recordset. The first is the conditional sum and the second is the full sum:
"select sum([Amount]) as TotalMannaSum from [Manna Gold]  WHERE [exchanged date]>#" & ddate & "# " & "UNION select sum([Amount]) as TotalMannaSum from [Manna Gold]"

This query will return a two column one row recordset. the first column is the condtional sum and the second is the full sum:
"SELECT T1.TotalMannaSum as ConSum, T2.TotalMannaSum as AllSum FROM (select sum([Amount]) as TotalMannaSum from [Manna Gold]  WHERE [exchanged date]>#" & ddate & "# ) AS T1, (select sum([Amount]) as TotalMannaSum from [Manna Gold])"
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now