calculations

[justme43 via AccessMonster.com]

could someone please tell me where and what I should put to:
1. Count -1 in report column (coming from yes/no entry)
2. sum Column which includes nil or blank amounts.
3. how to count the number of dates in period
4. then how to count the number of these dates that are in the current Fiscal
year.
explaination: I have client files with opened date these could start some
years ago I want to know how many dates in the period and how many of these
are new clients.
5. How can I work out how many times the same client came during period

Thanking you for any help you can supply

 

[Jeff Boyce]

You're asking a "how" question (several, actually), but have not told us
"what", as in what your data structure looks like.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

You need to supply some specifics on your table and field structure if you
want a precise answer. In general:

1) To Count -1 you can use an expression like the following in an aggregate
(Totals) query.
Abs(Sum([SomeField]))
2) If the column (field) is a number field use an expression like the
following in an aggregate (totals) query
SUM([SumField])
3) and 4) It kind of depends on the query, but you might be able to use an
expression like the following
Count(IIF([DateField] Between #2009-01-01# and #2009-01-31#,1,Null)
5) Depends on the data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[justme43 via AccessMonster.com]

You need to supply some specifics on your table and field structure if you
want a precise answer. In general:

1) To Count -1 you can use an expression like the following in an aggregate
(Totals) query.
Abs(Sum([SomeField]))
2) If the column (field) is a number field use an expression like the
following in an aggregate (totals) query
SUM([SumField])
3) and 4) It kind of depends on the query, but you might be able to use an
expression like the following
Count(IIF([DateField] Between #2009-01-01# and #2009-01-31#,1,Null)
5) Depends on the data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

could someone please tell me where and what I should put to:
1. Count -1 in report column (coming from yes/no entry)

[quoted text clipped - 8 lines]

Thanking you for any help you can supply

Sorry I am not sure what I should post
this is what I have done
I have 3 tables they are Client details, Assistance, other details
In client details is the field "dateopened" this is the date the orginal file
was started it is a date field. (it tells me if they are new or old clients)
In Assistance the fields are date of assistance (datefield), ele,water,gas,
rates,etc (10 different) currency fields here, also from otherdetials and
assistance are several yes/no fields like assistDenied, Food supplied,
english speaking, caseworker, (these come up with a -1 in query and report)
there is also a field that lists what benefits the client is receiving this
is a lookup field in the org table. (appears as number field in query and
report)
I have ran a query and made a new table combining all this information.
Then I ran another query on the new table and made a report from this query.
This is a select query
If I do the calculations on the report (same as I do in forms) I get "error"
in the total. ie =sum([gas]) If I do this in the query it ask me for
parameters when I open the query or report.
with the date calculations these are required for fiscal year from july to
june but we also do a monthly for inhouse information. I was hoping that I
could find a calculation that only counted current fiscal year dates and then
I could count all dates. this would give me new clients and total clients.
I also need to know how many times a client comes I thought I could use
clientref no for this (count number of times during fiscal year period)
I have set dates from and to so i only get the report for the period required
and this is working for me.
i accomplish all this in excel, In access I am not sure where I should be
doing the cals, in the query or on the report and the cals are different. Nor
do I know if I should be grouping in the query or just using criteria.
Is this the information you require.
I am also having trouble with another report which is a stats report so if
you could teach me what exactly I should post to receive information I would
be extremely grateful.