Sum values from a date range from a specific record

[britesynth]

hello everyone,

i'm working on a loan database for my personal use
and obviously i'm new to access

i would like to ask for help on this: see attachment


thanks in advance! =)

 

[pwbrown]

Is this something that will happen often? You could just create a simple query with criteria being the persons id/number and a date range. Then have a sum function in the value column.
It also depends how you want to input the id / date. Is it going to be the same person every time?

 

[CJ_London]

It will depend on how you are using the data but for a query you would have

"SELECT Sum([B][I]payments[/I][/B]) from[B][I] paymentT[/I][/B] where [B][I]client[/I][/B]='[B][I]" & NameofClient[/I][/B] & "' AND [B][I]Tdate[/I][/B] between #" & [B][I]startdate[/I][/B] & "# AND #" & [B][I]enddate[/I][/B] & "#")

and in a form you would put the following in the controlsource of a text box

=dsum("[B][I]Payments[/I][/B]","[B][I]PaymentT[/I][/B]","[B][I]client[/I][/B]='[B][I]" & NameofClient & "[/I][/B]' AND [B][I]Tdate[/I][/B] between #" & [B][I]startdate [/I][/B]& "# AND #" & [B][I]enddate[/I][/B] & "#")

Highlighted items need to be replaced with whatever you have called tables, fields and values.

Dates between #'s need to be in american format of mm/dd/yyyy

Note: avoid using reserved words for field names etc - e.g.date, name

 

[CJ_London]

sorry forgot to include the loan id

"SELECT Sum(payments) from paymentT where client='" & NameofClient & "' AND TLoan='" & LoanID & "' AND Tdate between #" & startdate & "# AND #" & enddate & "#")"

 

[britesynth]

Is this something that will happen often? You could just create a simple query with criteria being the persons id/number and a date range. Then have a sum function in the value column.
It also depends how you want to input the id / date. Is it going to be the same person every time?

hello,

no sir there are different persons with different loan folders and payments

 

[britesynth]

sorry forgot to include the loan id

"SELECT Sum(payments) from paymentT where client='" & NameofClient & "' AND TLoan='" & LoanID & "' AND Tdate between #" & startdate & "# AND #" & enddate & "#")"

thanks!

i will try your suggestions
and give you guys an update on my project