Monthly Sales reports with wholesale average

[Yarrrm8e]

Hello all,

So I have a series of sales line items. I have a table with the line items, looks something like this:

CustomerName ItemNumber ItemDescription Quantity UnitPrice Shipdate

Now, I've created what I call a Reference Table that relates to the sales table via ItemNumber that looks like this:

ItemNumber ItemDescription PointValue

So from here I can generate Crosstab Queries that give me useful information, like quantity sold, and overall point value. I use pointvalue because those numbers don't change; we adjust our pricing annually, and this enables me to do apples to apples comparisons across calendar years.

So the pickle I'm in is this: although I can easily make a crosstab query that gives me units sold, and I can make one that gives me total point values for a given customer in a given month, I can't combine the two in a report. I figure I can combine subreports so I get something like this:

CustomerName

1 2 3 4 5 6 7 8 9 10 11 12
Product A 5 3 3 6 3


Product B 8 11 ShiftyFive Thirteenteen



Monthly PointValue Avg 705 835 922 1400 700 ShiftyFive

And yes, I'm so frustrated I'm typing Group X lyrics.

Now, I've gone so far as to create a union query that gives me the following rows:

Customer PointValue 500 400 800 1700
Customer Quantity 3 4 1 15

But a report won't allow me to divide rows like that.

So I'm stuck. Any ideas?

[June7]

How do you want the report organized? By customer then by product? With quantity and point value side by side OR as you show in post?

Can't you sort on the values "PointValue" and "Quantity" within the customer group?

[Yarrrm8e]

How do you want the report organized? By customer then by product? With quantity and point value side by side OR as you show in post?

Can't you sort on the values "PointValue" and "Quantity" within the customer group?

Customer then product.

I think side by side would be confusing.

My problem with sorting by point value and quantity within the group is that I couldn't get the union query to play nicely with the report. That, and id like the detail that you lose with that particular union query.

[June7]

I've never had to base a report on UNION query, didn't know that would be issue. I have UNION queries but they are all used as RowSource for graphs.

Aside from writing records to a 'temp' to achieve the order you want, I don't see any options.