query that will sum all payments (1 Viewer)

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
i am trying to write a query that will sum all payments for a variable time frame, I'm kinda lost here.... thanks!!


Code:
SELECT DISTINCTROW Sum([dbo_Payments].[amount]) AS [Sum Of amount], Count(*) AS [Count Of dbo_Payments], dbo_Payments.transactionDate
FROM dbo_Payments
WHERE (((dbo_Payments.transactionDate) Between "StartDate" And "EndDate"));
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
Create a regular query that includes those fields. On the ribbon, click the Totals icon. On the appropriate fields, change "Group By" to Sum or Count as appropriate. Once that's working, add a criteria on the date field.
 

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
Like this mate?:
Code:
SELECT DISTINCTROW Sum([dbo_Payments].[amount]) AS [Sum Of amount], Count(*) AS [Count Of dbo_Payments], dbo_Payments.transactionDate
FROM dbo_Payments
GROUP BY Sum([dbo_Payments].[amount]), Count(*), dbo_Payments.transactionDate
HAVING (((dbo_Payments.transactionDate) Between "StartDate" And "EndDate"));
 

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
Aggregate concepts are something I need to understand better.... This close:

Code:
SELECT DISTINCTROW Count(dbo_Payments.transactionDate) AS CountOftransactionDate, Sum(dbo_Payments.amount) AS SumOfamount
FROM dbo_Payments
HAVING (((Count(dbo_Payments.transactionDate)) Between "start" And "end"));
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
You must not have started from scratch, that would never have been created. Try

SELECT Sum([dbo_Payments].[amount]) AS [Sum Of amount], Count(*) AS [Count Of dbo_Payments], dbo_Payments.transactionDate
FROM dbo_Payments
GROUP BY dbo_Payments.transactionDate

I'm guessing you want to remove the grouping on date, but I'm trying to walk you through the process.
 

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
That give me data finally, but I need the date range to pop up and the total revenue between those dates
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
Yes, I mentioned that. Change Group By to Where on the date field and add the criteria

Between [Start] And [End]

though most of us would use a form to gather user input.
 

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
SELECT Sum(dbo_Payments.amount) AS [Sum Of amount], dbo_Payments.transactionDate
FROM dbo_Payments
WHERE dbo_Payments.transactionDate
HAVING (((dbo_Payments.transactionDate)=[between startdate] And (dbo_Payments.transactionDate)=[enddate]));


Gives and error that "Your query does not include transactiondate as part of an aggregate.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
I can't imagine how you're coming up with the SQL you're posting.

SELECT Sum(dbo_Payments.amount) AS [Sum Of amount]
FROM dbo_Payments
WHERE dbo_Payments.transactionDate between [startdate] And [enddate]
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
No problem! As I mentioned, most of us use forms to gather user input. It would allow you to validate proper dates where entered, etc. Basically gives you more control.
 

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
I tried to put what worked into a VBA statement, but it errored:


Code:
Private Sub setBilling_Click()
DoCmd.OpenQuery ("SELECT Sum(dbo_Payments.amount) AS [Sum Of amount] FROM dbo_Payments WHERE dbo_Payments.transactionDate between [startdate] And [enddate]")
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
I wonder what the error was... I would open a form/report based on the query. I've never tried to open a select query with OpenQuery. What are you trying to accomplish?
 

BennyLinton

Registered User.
Local time
Today, 09:30
Joined
Feb 21, 2014
Messages
263
I was trying from a button on the form to display this successful query with two prompts:

Code:
SELECT Sum(dbo_Payments.amount) AS [Sum Of amount] FROM dbo_Payments WHERE dbo_Payments.transactionDate between [startdate] And [enddate]
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:30
Joined
Aug 30, 2003
Messages
36,118
You could open a recordset on that SQL and put up a message box with the result. You could use DSum() instead of the query, but the dates would need to come from a form, not the popups. First option probably needs them on a form too now that I think about it. Or save that query and try a DLookup() against it. I think it will throw the parameter prompts, though I'm not sure since I never use them.
 

Users who are viewing this thread

Top Bottom