Hi, could anyone teach me how to do this? I need some urgent help here! Experts please help! Thanks in advance!

Here is the result of a query I have:

Person Email Product Amount Category
A [email protected] Apple 10 Fruit
A [email protected] Orange 20 Fruit
A [email protected] Pen 30 Office
B [email protected] Apple 15 Fruit
B [email protected] Orange 25 Fruit
B [email protected] Pen 35 Office
C [email protected] Apple 18 Fruit
C [email protected] Orange 28 Fruit
C [email protected] Pen 38 Office

What I need to do is the develop a report or other format, in a format like:

Person: A

Category
Fruit Amount Office Amount
Apple 10 Pen 30
Orange 20
Total 30 Total 30



Do this for A,B,and C and send the individual report in via email to them. To me it looks like a mail merge, but I don't know how to manipulate the data and put it into the layout. Also, how to get total for each category in the report? My real data has a lot of data, about 100 person, and about 60 product that falls into two categories. Is there anyway easy and fast way to do this? Please help! Thank you very much!

--------------------------------------------------------------------------------

Elain,
Here is how I have done this type of thing...(if there is an easier way, someone please share.)

For what you have provided you will need to do this for each person:
Create a query for the table and add all the fields.
Add a query field "FruitTotal: IIf (Category = Fruit, Sum(YourTableNameHere!Amount), "0")"

Add a query field "OfficeTotal:IIf (Category = Office, Sum(YourTableNameHere!Amount), "0")" (my note: if you will always only have one "office" for each person, you will not need this field in the query.

Then when you apply this to a form you can either have a "WhereCondition" on the open event so you can specify a person, or create a query & report like this for each person with the criteria set in the query.

Hope I understood what you are trying and was a help.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!