Complex sorting and grouping

[rofferer]

I have done a report that shows one line for each customer together with the total number of sold items.

I now have to change this report so that it shows the different product groups for the items. The items table has a field that links to a product groups table.

Have done it, but only so that every actual group gets its own row. I.e. one spcific customer is displayed on 3 rows if he has bought items from 3 product groups.

Bu the intention is still to show all the totals on one row for each customer. Example:
Customer Grp1 Grp2 Grp3
Jones 12 0 6
Smith 10 9 7

How can I put these numbers in group fields?
If not possible in an Access report direct, is there a way to make a file of the report to Excel or perhaps to another Access table that will be emptied each time before population? Or any other suggestion?

Thanks / Rolf

 

[dhookom]

Have you considered using a crosstab query as the report's record source?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rofferer]

No I had not. And your suggestion was very good and it works as wanted. Many thanks for that.

There came though a little problem: now I cannot use the expression "[forms]![myform]![mycontrol]" as condition in the crosstab query. There came an error message that said it is not a valid fieldname or expression in Microsoft Jet. The referred form is open and populated. And with a normal query it works.

I need it to choose the wanted customer. Is there another way to specify the condition, as it seems to be a difference between crosstab and normal querys?
/ Rolf

 

[dhookom]

You can use the control on the form as a criteria if you set the data types of the parameters. Select Query->Parameters and enter something like:

[forms]![myform]![mycontrol] Date/Time



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rofferer]

Sorry to say, there is a problem when I use the crosstab query as the report's source.

If the data gives nothing (or zero) for one group, that means that the field list does not state this group and therefore the report cannot generate this group and generates an error instead. That said, I cannot have column headers or the fields in the detail section for all the groups in the report. But it works in the query as that expands to all groups when ther is data for it.

How should the report be designed in such a case?

/ Rolf

 

[dhookom]

You might be able to add all of your groups into the Column Headings property of the crosstab query. If you can't do this then there are some crosstab report solutions at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rofferer]

The Select Query->Parameters you said earlier made that part work fine. I will test the tip in your last posting tomorrow, as it is so late here now. I will return with response. Thanks!
/ Rolf

 

[BMDE]

When I need some sort of generic field to group data where there is no data field to accomodate, I create an added field and populate it to flag each group. Run an update query to flag the categories you need.

 

[BMDE]

As stated in an earler reply:
When I need some sort of generic field to group data where there is no data field to accomodate, I create an added field and populate it to flag each group. Run an update query to flag the categories you need in this added field.

I wanted to add that the group data becomes the Row Heading in your crosstab query.

 

[rofferer]

Duane,
I have found that I can't have all the column headings and fields in the report, as when some groups have no values, I get an error.

When I looked at your example I saw that there was a possibility to choose whether I wanted 2, 3, 4... columns, but I don't think this is useable in my case, as the user does not make this choise, as he does not know how many columns there has to be. That is decided upon how the sales for that specific day has been. (Must admit that I did not fully understand your complex programming with the levels)

One more strange thing here was that when I managed to make a crosstab query that worked, I could not use it to make a report with the guide, as no fields were shown after I chose this crosstab query. But when I made another simplified test database, it worked. Maybe something with my tables, or ....?


BMDE,
Can you explain how you mean by 'added field' and the populating?

Thanks to you both for looking into this.
/ Rolf

 

[dhookom]

In my sample, you wouldn't normally ask the user how many columns. You would design your report with the maximum number of columns that would fit and the code would do the fitting of your data into the columns. The programming is a little complex but your needs are complex.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]