Reports that compare month/year totals

[Guest]

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.

 

[Guest]

All of the information is stored in one table.
Post the field names that this data is stored in.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

Thanks Karl,

Requests for Materials is the Holds figure. Information for this report is
entered by each of our branch locations and then we compile a report based on
the fields I noted. I'm not sure if I understand clearly about not putting
the data into different fields as the data is unique to each field. I
definitely see the benefits of having a single field for the month and year
instead of two. This is an old database and may need to be completely
revamped.

 

[Guest]

It appears you are using a spreadsheet type of table instead of a normalized
structure of a relational database.
I envision your table like this --
TransActID - Autonumber - primary key
TransDate - DateTime
Branch - text
Type - text - will have the identifier of the trans action
TransDollar - number - single

An example of how I see your data --
Circ fields
Month Year Branch Circ Holds Cards ILL
JAN 2007 1 15 25
FEB 2007 1 10 6 8

This is how I think it should be --
TransActID TransDate Branch Type TransDollar
1 1/1/07 1 Cir 15
2 1/1/07 1 Holds 25
3 2/1/07 1 Holds 10
4 2/1/07 1 Cards 6
5 2/1/07 1 ILL 8

Use a union query to rearrange the data in an append query. Then use a
crosstab to do your display.