Month Parameter to pull only that data

[Guest]

The scenario is this.

A user enters a month and gl year to evaluate against another month and
year. (Beg balance=Jan 2005 and End balance=Jan 2006)

The resulting report would group the data according to section and
subcategory and list the following information as columns: Acct #, Desc,
Dept, glyear, Beg balance, End balance, and the difference between the
beginning and ending balances for the parameters entered.

The data in the table looks like this:
DEPT,ACCT#,GLYEAR, JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
97, 1101, 2005, 2288, 2288, 2288, 2288, 2288, 2288, 2288, 2288, 2288, 2288,
0, 0
97, 1101, 2006, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

 

[Jeff Boyce]

The scenario is this.

A user enters a month and gl year to evaluate against another month and
year. (Beg balance=Jan 2005 and End balance=Jan 2006)

The resulting report would group the data according to section and
subcategory and list the following information as columns: Acct #, Desc,
Dept, glyear, Beg balance, End balance, and the difference between the
beginning and ending balances for the parameters entered.

The data in the table looks like this:
DEPT,ACCT#,GLYEAR, JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
97, 1101, 2005, 2288, 2288, 2288, 2288, 2288, 2288, 2288, 2288, 2288,
2288,
0, 0
97, 1101, 2006, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

 

[Jeff Boyce]

(sorry for the previous empty response, "fat fingers", you know...<g>)

Based on your example data, you have a ... spreadsheet! Setting up
repeating fields (e.g., JAN, FEB, MAR, ...) (these are repeating because
they all cover months, just different ones) is about the only way you can do
this in Excel, but you posted a question in an Access newsgroup.

Your data would benefit from further normalization before you go any
further. If this doesn't seem worth the effort, I'll suggest that you use
Excel instead of Access.

If you want to get the full benefit of what Access can do, you can't feed it
spreadsheets.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the feedback. It all looks like a spreadsheet but that's what the
table that I'm working with has. The table is being populated from an
application. I just couldn't figure why it was structured that way but either
way, I have to try to get the information that the user wants. I just tried
different things that doesn't seem to work.

Amy

 

[Guest]

Thanks. I got my CASE statement to work finally to produce exactly what I need.

 

[Jeff Boyce]

FYI

Even though the data originally comes from (or looking like) a spreadsheet,
you won't get the best out of Access unless you parse that "raw" data into a
table structure that lets Access work with it.

If you used a SELECT CASE statement to work around the repeating (month)
fields, you (and Access) are still working much too hard.

Take a look at normalization and relational database design ... Jeff Conrad
has an excellent set of resources at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html


Regards

Jeff Boyce
Microsoft Office/Access MVP