RE: Reports that compare month/year totals

You should use a single DateTime field instead of Month & Year fields.
I think your Logins, Searches, and Renewals field probably should be name
Type and then indicate the type in the field. Then have a field for your data
number rather than putting the numbers under the three different fields.

I think the same for Circ, Holds, Cards, and ILL.

The same for CVPN, CVPA, CVCN, CVCA, CVTN, CVTA, PHSN, PHSA, CPN, CPA,
PMN,ILLB, ILLL, HDSF, and HDSH.

With these changes I think a crosstab query would pull your data.

As you have it now where would "Requests for Materials" information be stored?

--
KARL DEWEY
Build a little - Test a little


"SSD" wrote:

The fields for the first example are:
Month
Year
Logins
Searches
Renewals

The other fields are similar so I'm just listing a few.

The fields for the second example follow. There are two tables: Circ and
Stats.

Circ fields: Month, Year, Branch, Circ, Holds, Cards, ILL
Stats fields: Month, Year, Branch, CVPN, CVPA, CVCN, CVCA, CVTN, CVTA, PHSN,
PHSA, CPN, CPA, PMN,ILLB, ILLL, HDSF, HDSH

Thank you.



"SSD" wrote:

I need to create reports that compare our current fiscal year activity for
the current month with the previous year same month. I have tried creating a
table for the previous year information and a query for the current year but
that gives the same information for every month. The first report is more
basic than the others. Here is an example of the fields we are using and the
totals we are looking for:

2007/2008 2006/2007 Difference
%Difference
Logins 21,786 12,919 8,867
68.64%
Searches 146,836 39,737 107,099
269.52%
Renewals 20,649 14,828 5,821
39.26%

I get a message telling me that I cannot combine information when I try
doing a query for each year and then combining in one report.

The other reports are much more complicated as they combine information from
about 15 locations and some of those locations are combined, while others are
not. All of the information is stored in one table. Here is an example:
2007/2008 2006/2007
Circulation
Main branch 95,515 92,771
Other branches 105,646 99,115
ILL 117 150
Total 201,278

Requests for Materials
Main branch 14,420 12,876
Other branches 6,089 4,015

Any help or suggestions you have would be greatly appreciated.
.

Relevant Pages

  • Re: Report is making too many numbers
    ... from the $'s being listed more than once in the query. ... And you want to display the sum of in the Vendor header or footer ... My report is based on a query. ... those records--you could create a Totals query by depressing the ...
    (microsoft.public.access.reports)
  • Re: Can I add a summary in the report footer to do this...?
    ... The report's source is the Audit Details Query. ... There is another text box with a similar calculation, ... As the report runs through each BU it does the calcs, sums them up in the BU ... >>> Create a totals query similar to your report's record source. ...
    (microsoft.public.access.reports)
  • Re: Page totals error in report - doesnt add correctly
    ... display it on a form or report. ... query based on the Import Table. ... All Totals text boxes properties are set to General number with 2 decimal ...
    (microsoft.public.access.reports)
  • Re: Report with vertical columns
    ... As for the report, I have used a pivottable instead. ... use totals queries to get the total supplied and sold: ... Create a query using the Supply table. ... In the Field row, enter an expression that represents your date period. ...
    (microsoft.public.access.reports)
  • Re: Report is making too many numbers
    ... My report is based on a query. ... I put the running sum text box in there. ... My report is grouped by Vendor, then Project, ... I've got a problem with Sum Totals at the end of a report, ...
    (microsoft.public.access.reports)