Showing a sum total in a query for fields based on the year - JCW

[JohnW]

I have four fields that I what to show the sum total of for any given year.
I already have a query that will list all of the individual transactions for
these fields per any given year. The fields are
TuitionPayment
RegisterPayment
MeetPayment
CoachPayment
LatePayment

Any suggestions? Thanks

 

[Allen Browne]

Create a query, and type this into the Field row in query design:
Payments: Nz([TuitionPayment],0) + Nz([RegisterPayment],0) +
Nz([MeetPayment],0) + Nz([CoachPayment],0) + Nz([LatePayment],0)

That's all on one line.

 

[JohnW]

This shows a total for this column of each pay transaction. What I need is
when I type in the year for the prompt, it will give me a sum total of all
the TuitionPayments for that year, and a sum of all of the RegisterPayments
for that year, and a sum of all of the MeetPayments for that year, etc. The
year comes from a PayDate field that is associated with each payment.

Thanks
--
JCW

Create a query, and type this into the Field row in query design:
Payments: Nz([TuitionPayment],0) + Nz([RegisterPayment],0) +
Nz([MeetPayment],0) + Nz([CoachPayment],0) + Nz([LatePayment],0)

That's all on one line.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have four fields that I what to show the sum total of for any given
year.
I already have a query that will list all of the individual transactions
for
these fields per any given year. The fields are
TuitionPayment
RegisterPayment
MeetPayment
CoachPayment
LatePayment

Any suggestions? Thanks

.

 

[Allen Browne]

Okay:

1. In query design view, depress the Total button on the toolbar (upper
sigma icon.) Access adds a Total row to the query design grid.

2. Drag your date field into the grid.
In the Total row under this field, choose Where.
In the Criteria row below that, enter this as one line:

= DateSerial([WhatYear],1,1) And < DateSerial([WhatYear]+1,1,1)

3. Drag the other fields into the grid.
In the Total row under each one, choose Sum.

When you run the query, it pops up a parameter value asking for the year,
and then yields the sum of each field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This shows a total for this column of each pay transaction. What I need
is
when I type in the year for the prompt, it will give me a sum total of all
the TuitionPayments for that year, and a sum of all of the
RegisterPayments
for that year, and a sum of all of the MeetPayments for that year, etc.
The
year comes from a PayDate field that is associated with each payment.

Thanks
--
JCW

Create a query, and type this into the Field row in query design:
Payments: Nz([TuitionPayment],0) + Nz([RegisterPayment],0) +
Nz([MeetPayment],0) + Nz([CoachPayment],0) + Nz([LatePayment],0)

That's all on one line.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have four fields that I what to show the sum total of for any given
year.
I already have a query that will list all of the individual
transactions
for
these fields per any given year. The fields are
TuitionPayment
RegisterPayment
MeetPayment
CoachPayment
LatePayment

Any suggestions? Thanks

.

 

[JohnW]

Allen

Perfect. Exactly how I needed it to work. Thanks again.
--
JCW

Okay:

1. In query design view, depress the Total button on the toolbar (upper
sigma icon.) Access adds a Total row to the query design grid.

2. Drag your date field into the grid.
In the Total row under this field, choose Where.
In the Criteria row below that, enter this as one line:

= DateSerial([WhatYear],1,1) And < DateSerial([WhatYear]+1,1,1)

3. Drag the other fields into the grid.
In the Total row under each one, choose Sum.

When you run the query, it pops up a parameter value asking for the year,
and then yields the sum of each field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

This shows a total for this column of each pay transaction. What I need
is
when I type in the year for the prompt, it will give me a sum total of all
the TuitionPayments for that year, and a sum of all of the
RegisterPayments
for that year, and a sum of all of the MeetPayments for that year, etc.
The
year comes from a PayDate field that is associated with each payment.

Thanks
--
JCW

Create a query, and type this into the Field row in query design:
Payments: Nz([TuitionPayment],0) + Nz([RegisterPayment],0) +
Nz([MeetPayment],0) + Nz([CoachPayment],0) + Nz([LatePayment],0)

That's all on one line.
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I have four fields that I what to show the sum total of for any given
year.
I already have a query that will list all of the individual
transactions
for
these fields per any given year. The fields are
TuitionPayment
RegisterPayment
MeetPayment
CoachPayment
LatePayment

Any suggestions? Thanks

.