Eliminating unnecessary data in a report

  • Thread starter Thread starter MIchel Khennafi
  • Start date Start date
M

MIchel Khennafi

Good morning.

I created a report in access 2002 that print monthly financial information.
I have up to 24 columns to print (2 months). I create a page split by
account number. For some account numbers, I do not have to display 24
columns, maybe 10... While for other I could display 14 columns...

Is there a possibility to create a report to print up to 24 columns for a
given account but just print 10 columns for another because there is no data
to print?

Thanks so much

Happy holidays everyone!

Michel
 
You'd probably have to explain what is in the columns. and how you have
titled them. If your column headings match your field names, and the record
description runs vertically down the page, fields with no data would simply
be blank. If one record displays some columns (fields) and the next
displays different fields (column) it seems like it would be very hard to
read.

More details/specific example please.
 
Each column represent a day...

So assume I have 20 days in a month --> I will potentially have 20 columns
in my report

Now assume I only have 5 days in that month where activity occured, I would
not like to print 15 empty columns, just the 5 columns where I have had
activity.

Report page 1 : all the columsn are printed (Current report)

Account# D1 d2 d3 d4 d5 d6 d7 d8...
1 1 2 3 5

Report page 1: only the columns where I have a value are printed (Desired
result)

Account# D1 d2 d3 d7
1 1 2 3 5
 
Do you actually have data stored in fields named for a particular day, or is
this a crosstab query?

Access is a relational database. Naming fields "day1" "day2" "day3" etc, is
not appropriate in a database. It appears that you are trying to build a
spreadsheet in Access.

To do what you want in a DATABASE, you would create a new record in a table
for each day that included activity. If there were no activity on a
particular day, there would be no record for that day. Your structure would
look something like this...

TblAccounts
AccountNumber
AccountName
AccountType
etc.


TblActivity
Date
AccountNumber (related to the other table 1-to-many relationship)
Amount
etc.


So, a normalized database would include one record for each account in your
account table and one record for each account and each date that included
activity.

Once you get your data normalized, then you could tackle how to build a
report.

Hope that helps. For more details, do a search and read the previous posts
on "normalization" and "table structure" and "relationships".
 
One other question....

There are 12 months in the year. How are you handling that in your current
setup? Do you only store one month of data in your database? Hopefully you
don't build a new table for every month. That would be a sure sign that you
are not normalized. Anytime a table name or field name contains data (month
names, dates, etc.) you can be sure you are going down the wrong path.

Normalizing as previously mentioned would allow you to enter as many day's
worth of data as needed since you are entering a specific date for each
account with activity on that date.
 
Back
Top