I am trying to summarize activity across several fields of an Access database. The setup is as follows:
There are 8 possible gifts to be given to clients. These are in a Lookup menu in each of 5 fields (GiftName1, GiftName2, etc) and each has a corresponding date field (DateGiven1, etc). The first column (and date) is always filled because that is how an entry gets created. The other four are sporadically filled in as someone gets more than one.
I didn't design the database originally. What I am asked to do is to summarize how many of each gift was given away for each month of the year. The gift may be in any of the 5 columns. I can do 5 separate queries and add up the totals for each column, but that seems cumbersome.
Another option that comes to mind is to create a query that stacks all the five columns into one, with the date fields still associated in the correct order. Then I can sort and group and summarize with no further difficulties.
Am I missing something elementary here? I have about 5 months Access experience so far, so it is very likely.

The problem of course as you have realized is the table design. There is a one-to-many relationship between clients and gifts. This should have been implemented as two tables and a subform should have been used to add the gifts to each client. Putting the many-side fields in the one-side table is how people who don't know any better (and some lazy ones who do) choose to store this data. On the surface it seems to be a simple solution. However, it is very cumbersome to work with and not expandable. What happens if you want to increase the gifts to 9 or do some analysis as you have been asked to do?

My recomendation is as always, FIX the table design. It will take you a few hours to clean up the mess but you'll have an easier go later.

The steps required to clean up the mess are:

1. Back up your database before starting.
2. Create the new many-side table
3. Create 8 queries to take the data from the one-side table and add it to the many-side table.
4. Create a subform to maintain the gift data.
5. Change the current form to delete the fixed gift fields and replace them with the subform.
6. Delete the now extraneous columns from the one-side table
7. Verify that everything worked.

To work with the data as it is, you need to "normalize" the structure on the fly. You can do this with a union query that includes 8 select queries (one for each gift/date column). Then build a totals query that uses the union query as its recordsource.

Good Luck http://www.access-programmers.co.uk/ubb/smile.gif

Thanks for the prompt response. I inherited the database management from my well-meaning boss. It's a flat database with about 40 columns and 1500 entries (so far). Yargh!
I already devised a union query in order to get her some fast and dirty numbers.
However I've now run into a new snag. In a report, I can GroupOn a certain number of prefix digits, but that will only work for one given year. Minor consideration. Worse is that some are entered as 08/xx/01 and some are 8/xx/01. (Yes, they're text fields, because sometimes the value is "Deliver").

How can I summarize in a query for "Like 0n/*/01" OR "Like n/*/01"? (Since report seems limited in number manipulation)

I answered this question unter another thread. Now for some unsolicited advice - Discourage your boss from designing tables http://www.access-programmers.co.uk/ubb/smile.gif

I've already done so fairly successfully, since arriving...I just can't help what was done before I got there.