Totaling like items for certain period of time

[znibk]

I have created a sub-report which should total a grand total of various types
of income for example a month. (According to the printout of the daily
activity, it is not doing that correctly). I need the query to combine all
the sales for a particular period of time, ie one month, the cash donations,
etc. The report seems to do that, except, when compared to the income
tickets, and the report of daily activities, I come out with a different
total.

In my query, I have the
Criteria for the Date As: Forms!frmDates!txtStart And Forms!frmDates!txtEnd
Expression for my SalesTot: Sum([SS]+Nz([More],0)-Nz([Less],0))
Sum for my donations: TCD:CashDonations
Sum for other income: TOI:OtherIncome
Expression for the total TxtIncomeSum:
Nz([SalesTot],0)+Nz([TCD],0)+Nz([TOID])+Nz([TOID],0)

What this seems to be achieving is the total sales made on one particular
day, the total cash donations for one particular day etc. It is not returning
the total of each catagory for the entire month.

What adjustments do I need to make? Any help would be really appreciated as
that mistake was made in January and I have not yet figured out what I've
done wrong and it is almost time for my February report.

Thanks for all your help in the past and for what is to come.

K

 

[KARL DEWEY]

Post the SQL of your query.

 

[znibk]

Good evening Karl,
As I do everything in the design view of the query, I viewed it in the SQL
view of the query. I hope this is what you want and tell you where I need to
alter the query so that at the end of the time period, ie, month, I will be
able to have a sum total of "sales," "cashdeposits," "otherincome," etc.

Thank you so much for your help.

K

Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little

I have created a sub-report which should total a grand total of various types
of income for example a month. (According to the printout of the daily
activity, it is not doing that correctly). I need the query to combine all
the sales for a particular period of time, ie one month, the cash donations,
etc. The report seems to do that, except, when compared to the income
tickets, and the report of daily activities, I come out with a different
total.

In my query, I have the
Criteria for the Date As: Forms!frmDates!txtStart And Forms!frmDates!txtEnd
Expression for my SalesTot: Sum([SS]+Nz([More],0)-Nz([Less],0))
Sum for my donations: TCD:CashDonations
Sum for other income: TOI:OtherIncome
Expression for the total TxtIncomeSum:
Nz([SalesTot],0)+Nz([TCD],0)+Nz([TOID])+Nz([TOID],0)

What this seems to be achieving is the total sales made on one particular
day, the total cash donations for one particular day etc. It is not returning
the total of each catagory for the entire month.

What adjustments do I need to make? Any help would be really appreciated as
that mistake was made in January and I have not yet figured out what I've
done wrong and it is almost time for my February report.

Thanks for all your help in the past and for what is to come.

K

 

[znibk]

Karl,

I write everything in the design view of the query as I don't know how to
write code. I hope this tells you what you need to know for me to be able to
obtain a sum total for a certain period of time, ie month, for "sales,"
"otherincome," "cashdonations," etc.

the SQL view,

SELECT tbl_Income.IncDate, tblBANK.Bank, tblINCOMETYPE.IncType,
Sum([SS]+Nz([More],0)-Nz([Less],0)) AS ASalesDep, Sum(tbl_Income.ACDDep) AS
ACDDep, Sum(tbl_Income.AOSDep) AS AOSDep, Sum(tbl_Income.AOIDep) AS AOIDep,
Nz([ASalesDep],0)+Nz([ACDDep],0)+Nz([AOSDep])+Nz([AOIDep],0) AS ATotInc
FROM tblMONTHYEAR INNER JOIN (tblINCOMETYPE INNER JOIN (tblBANK INNER JOIN
tbl_Income ON tblBANK.BANKS = tbl_Income.Bank) ON tblINCOMETYPE.INCOMETYPE =
tbl_Income.IncType) ON tblMONTHYEAR.MONTH_YEAR = tbl_Income.RefDate
GROUP BY tbl_Income.IncDate, tblBANK.Bank, tblINCOMETYPE.IncType
HAVING (((tbl_Income.IncDate) Between [Forms]![frmDates]![txtStart] And
[Forms]![frmDates]![txtEnd]) AND ((tblBANK.Bank)="Arvest") AND
((tblINCOMETYPE.IncType)<>"InKind Donation"));

Thank you so much for your help.

K

Post the SQL of your query.
--
KARL DEWEY
Build a little - Test a little

I have created a sub-report which should total a grand total of various types
of income for example a month. (According to the printout of the daily
activity, it is not doing that correctly). I need the query to combine all
the sales for a particular period of time, ie one month, the cash donations,
etc. The report seems to do that, except, when compared to the income
tickets, and the report of daily activities, I come out with a different
total.

In my query, I have the
Criteria for the Date As: Forms!frmDates!txtStart And Forms!frmDates!txtEnd
Expression for my SalesTot: Sum([SS]+Nz([More],0)-Nz([Less],0))
Sum for my donations: TCD:CashDonations
Sum for other income: TOI:OtherIncome
Expression for the total TxtIncomeSum:
Nz([SalesTot],0)+Nz([TCD],0)+Nz([TOID])+Nz([TOID],0)

What this seems to be achieving is the total sales made on one particular
day, the total cash donations for one particular day etc. It is not returning
the total of each catagory for the entire month.

What adjustments do I need to make? Any help would be really appreciated as
that mistake was made in January and I have not yet figured out what I've
done wrong and it is almost time for my February report.

Thanks for all your help in the past and for what is to come.

K