Query and Report Question

[nicolaasjan2005]

I have two linked tables that I'm drawing information from. The first table has donor information, including an identification number, the second table has 46 fields (different donatable categories) and a "donation date" field.

All the donations have default amounts of $0.00

Many donors contribute to one or more of those donatable categories on any given date.

What I'm trying to accomplish is to have Access2003 generate a stepped report that when run by date, displays the donor identification number followed by all of the categories they contributed to and the corresponding amounts.

There may be up to 200 donors who contribute to multiple categories on any given day.

Example:
.............................................................
06/Jan/2008
Donor 135
FoodBank $ 50
Red Cross $ 75

Donor 213
FoodBank $ 300
..............................................................
and so on.

I have no trouble drawing all information from the query, but how do I get it to display only the necessary information and not all category fields? As it is now I would have to generate many, many paper reports, whereas a page or two with only the necessary info would be a far more efficient report that uses less paper.

I tried searching the forum but I was unable to find any help.

If you can offer some assistance or direction - please do.

Thanks

 

[Kiwiman]

Howzit

You want to look at your table structure for table b - what you dont want is 40 odd different fields to hold your separate donation categories - this is against normalisation ( you may want to read up on this), and makes reporting pretty difficult.

For your donations you may want at a minimum the following fields: (you may also wnat to create a new table to hold the donation date and details - and then link to table below

• DonationID - [PK]
• DonCat - [FK] - from DOnation Category table
• DonDate
• DonorID - [FK] - from Donor table
• DonAmt

A donor can then contribute to as many categories as required by simply adding them. Having the categories in one field it makes reporting a lot easier. You can look at the Northwinds example database - re Purchase Orders

 

[nicolaasjan2005]

The categories are "fixed" as are the identification numbers for each donor. There would not be an occasion for requiring any more categories than the ones we already have. The donors are issued a specific identifying number at the start of the year, and that number remains with the donor until the next year.

Perhaps a combobox for selecting the donation category would be in order allowing the user to enter the amount of the donation (which varies constantly).

As you say, that would likely simplify the entire process and that way the query would return and report the results as I require.

Thanks for your help. I'm a "self-taught" Access user and need an awakening at times.