Sum Query

Neo-fite

Registered User.
Local time
Today, 04:11
Joined
May 17, 2011
Messages
60
I have a database and I want to create a query (using SQL) that just gives me the Sum of 1 field for the records in question. For Instance, I want my query to sum the total of [Amount] for all records that [DateYr1] = 2008 and [DateYr2] = 2010 and [Amount] > 0.

Is that possible? I'm not having any luck.:confused:
 
For clarification; do you have two date fields? DateYr1 and DateYr2?
 
Think that you need two queries to accomplish this. Try this:

First query:

Select YourTable.Amount, YourTable.DateYr1, YourTable.DateYr2
From YourTable
Where Amount <> 0;

Second Query is based upon first query

Select Sum(FirstQuery.Amount) As SumofAmount, FirstQuery.DateYr1, FirstQuery.DateYr2
From FirstQuery
Group By FirstQuery.DateYr1, FirstQuery.DateYr2;

I did not re-create your db, so this is my best guess without seeing your file.
 
Actually, it can be done in one query. But we will need to know whether DateYr1 and DateYr2 are real Date/Time fields and not just years (with a data type of Number)?
 
Actually, it can be done in one query. But we will need to know whether DateYr1 and DateYr2 are real Date/Time fields and not just years (with a data type of Number)?

They're text fields.

I'm hoping that I would be able to use the SQL in excel/ADO to populate a variable w/the sum/number. ;)
 
Ok, here you go:
Code:
SELECT Sum([Amount]) As SumOfAmount 
FROM [COLOR=Red]TableName[/COLOR]
WHERE [DateYr1] = '2008' AND [DateYr2] = '2010' AND [Amount] <> 0;
Amend the bit in red.

By the way, seeing that you are storing year as text it's a suspect that your tables aren't properly normalized.
 
Ok, here you go:
Code:
SELECT Sum([Amount]) As SumOfAmount 
FROM [COLOR=Red]TableName[/COLOR]
WHERE [DateYr1] = '2008' AND [DateYr2] = '2010' AND [Amount] <> 0;
Amend the bit in red.

By the way, seeing that you are storing year as text it's a suspect that your tables aren't properly normalized.

Agreed. The table is actually populated from a Lotus Notes db via an ODBC connection...currently all the fields are Text. ;)
 
I've not used Lotus Notes so I can't really attest to it ;)

Happy developing! :)
 
Happy developing! :)

Do you know if it's possible to populate a excel variable/cell with the "Sum" from the query using VBA/ADO/SQL? I have the following code that I use to get "Count", but don't know how/if I can get the Sum w/o extracting all the records and calculating a Sum and then deleting the extract.

Code:
[FONT=monospace]
[/FONT]Private Function Record_Count(sSQL As String)
    Set cnn = New ADODB.Connection
    With cnn
            .CommandTimeout = 0
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & FolderName & TARGET_DB
            .Open
    End With
     
    Set cmd = New ADODB.Command
        With cmd
            .CommandTimeout = 0
            .CommandType = adCmdText
            .CommandText = sSQL
            .ActiveConnection = cnn
        End With
     
    Set rst = New ADODB.Recordset
        rst.Open cmd, , adOpenStatic, adLockReadOnly
        Record_Count = rst.RecordCount
     
    rst.Close
    cnn.Close
    Set cnn = Nothing
    Set cmd = Nothing
    Set rst = Nothing
End Function
 
Look into DoCmd.TransferSpreadsheet. That will allow you to export to Excel into a specific cell.
 
Look into DoCmd.TransferSpreadsheet. That will allow you to export to Excel into a specific cell.

Maybe I'm not looking in the right area, but doesn't that command transfer from excel into access?
 
There's acImport which is Excel -> Access, and there's acExport for Access -> Excel.

DoCmd.Transferspreadsheet acExport, ... etc
 

Users who are viewing this thread

Back
Top Bottom