Excel like grid reports? (1 Viewer)

[jadeIT]

Hi.

I have a report spec. which requires customers listed from top to bottom, then product colours they have ordered across the top of the page horizontally.

then the qty they have ordered should appear lined up with the relevant customer and colour. like a matrix i think?

So if customer 1 ordered 15 of "blue", you could should be able to line up customer 15 and blue to find the value 15.

from what i see however, reports only group things vertically, so i cant get the values for [colour] to appear across the page.

i tried using the pivot chart thing but they suck and and too complicated to use (for the client).

if anyone knows a way to do something like this i would love to know. otherwise im guessing i might have to use visual basic 6 and use a ms flex grid and do it all programmatically, but that will take AGES.

 

[SteveM]

Hi,

The solution I would use for this particular problem would be to base the report on a Cross-Tab Query.

Access has a wizard for creating Cross-Tabs.

Steve

 

[jadeIT]

i started using a cross-table query and at first it seemed good.

However, I need more grouping options. For example, i can have customer by colour, but i also need to then group the results so that they only show the results for a certain product.

So for example, the user choose a product "Product A", and the cross tab query runs, but only showing the colours and customers for the selected product.

I tried manually adding more fields, but it wont let me.

 

[SteveM]

You could try using a standard select query first to filter the products, and then base the cross-tab on this select query.

Not an ideal solution but it should work.

Hope this Helps.

Steve

 

[jadeIT]

The main problem is interface... I can get the results, but not in a user-friendly way, and the clients are completely computer illiterate...

But I have started coding in Visual Basic and it looks like I can get the results I need that way.

 

[Pat Hartman]

You can't get two different "levels" of summary from the same query. One crosstab can produce color by customer another can produce color by customer and product.

I haven't seen anything in your problem statement which would lead me to belive that you cannot do this with a query.