fiscal year query, fredg maybe you can help?

[Sharyn]

Regarding my post below on creating 12 queries for
subforms on a form - I forgot to mention I am doing it
this way so I can have a printed report that runs in
columns, not rows.

I have 44 data fields for each month, and they need to be
displayed in columns next to each other. I was
considering your question of what will happen in 25 years
etc. Due to this company's fiscal year being October
through September, I was just going to have them start
with a fresh database each Oct.1. Is there a way to make
the queries so that they come up with the current fiscal
year's report - for this year it would be October 2003-
04.

Could I use some kind of year-1 criteria in the dates for
Oct,Nov,Dec queries, and have the user choose the fiscal
year in a combo box or something elsewhere? Do you have
any idea how I would go about making that kind of query
for oct,nov,dec?

 

[Andrew Smith]

Sharyn,

I've read this message and your previous ones.

Could you post back a list of your tables and the fields in them (not all
the fields if there are 44, just some examples). I ask this as I'm concerned
about your idea of starting a new database for each year. You don't have a
separate table for each month, do you?

 

[Sharyn]

Hi Andrew,

I have only one table for data. There are only 8
recruiters to track, with each recruiter having one weekly
record. My husband just told me today that he needs to be
able to see about three years worth of data for regression
analysis purposes, so I will be adding a year field to the
table so as to make the year-to-date forms work properly.
Data does not need to be kept for more than three years,
and I will add a delete query to remove old records and
keep the database a manageable size. I have to do
the "reports" using forms as the report functions are so
limited and he needs it to look a certain way.

Some of the 44 fields are calculated, but here is a
sampling of the fields in the main Recruiter Statistics
table:

ID
RIC
YEAR
MONTH
WEEK
#CALLS ATTEMPTED
CONTACTS
APPOINTMENTS OBTAINED
RGM SENT
RGM RETURNED
APPOINTMENTS FROM RGM
TELEPHONE
NATIONAL LEADS
PERPETUATED
SCHOOL VISITS
WALK-IN/CALL-IN
OTHER

The only other table I have is for recruiter personal data
which only includes:

RIC
LAST NAME
FIRST NAME

Sharyn

 

[Andrew Smith]

Sharyn,

I don't understand what you mean by "the report functions are so limited". I
believe that Access has one of the best reporting tools around (I only know
Access so can't comment on this, but I've certainly been able to create some
complicated reports fairly easliy with Access). You certainly should be able
to do anything you need with Access reports, and do it much better than you
can on forms.

There is no reason why you can't create a report in the tabular format you
need, with groupings by recruiter and/ or month and with summaries based on
each group. So if you group by recruiter and month you could display totals
for each month for a particular recruiter and the total for the recruiter.

You can also apply a filter to reports when you open them, so you could
choose to filter the report to a single recruiter and a specific time period
if you want, or to all recruiters over a particular time period. You don't
need to change the query behind the report to do this, although you do need
a bit of simple VBA code.

Given the size of the database you should not need to remove any records
over any conceivable time frame, so I wouldn't bother worrying about the
database getting too big.

 

[Sharyn]

The problem is that I need the field labels stacked on the
left and the 44 data fields running down the page instead
of across. I need to see the 12 months, quarterly, and
yearly totals , in columns next to each other on the same
page. I basically need it to look like a spreadsheet but
want the functionality of Access to manipulate the data.
When I try to create this in a report, it doesn't allow
anything but putting the headers aross the page. Do you
know of a way I could do this? I say it is limited
because it seems to me you can only get your labels to go
across the page with data listed below it. I have used
Crystal Reports where you can change that aspect, but
using it is not an option here.

 

[Andrew Smith]

Right, now I think I understand.

No, I don't think you can do it with your current table structure. However,
it could be done if you are prepared to redesign your tables. You currently
do not have a normalised table design, and the fact that you have 44 fields
should be a clue that this is the case. You are actually storing data in
field names - if you stored it in table records then you could create a
query that was "the right way round" for your report.

Are you prepared to consider redesigning the tables?

 

[Sharyn]

Yeah I know the table is not normalized. This project
started out small, "I just need this one report", and grew
fast as my husband realized what could be accomplished
with Access vs. the Excel spreadsheets he currently uses.
The forms are built and I can use criteria as a work-
around for my fiscal year problem, so in the interests of
his time and sanity I think I will just make it work. The
complicated Excel workbooks that were passed down to him
have so many incorrect formulas in them he can no longer
rely on the monthly and quarterly totals data.

My only problem now is the VBA project in the database on
the laptop is corrupt, and that is where I have created
the queries and "report" form. Should I just make a new
blank database and import objects, or will that import the
corrupted project as well?