custom group by in report

[y2k]

I don't know if this is possible, but I hope it is!! I have a table, generated by a make table query. The table is then the source for a report. Basicly, I want to be able to do a custom group by in the report. Each record in the table has a field called group#. I don't want to just group by group#, I need to be able to group by certain group numbers. For example, any records with group 0005 & 0019 would be treated the same, and any records with 9999 & 0014 & 0022 might be treated the same. Then I want to say, group all other records together. how can I do this? I thought about using append queries instead, and specifying the group#'s as criteria, but I also want a group header to say, the below records are for group 0005 & 0019.

anybody know if this is possibe? I'm sure it is!! I don't mind having to change from a make table query to append queries, that wouldn't be a problem, 'cause it'd still be just a once off - once I've done it once, I won't have to do it again.

 

[pdx_man]

You need to create a query that has this logic in it, then group by this logic. So, then we have all of the fields from our make-table query and we need to add one more column, let's call it Gr_By:

Gr_By: Iif(MyID = "0005" Or MyID = "0019",1,Iif(MyID = "9999" or MyID = "0014" Or MyID = "0022",2,3))

Then you base your report on this query and group by the Gr_By field. You will have to have the reverse logic in the TextBox in the Group Header ... =Iif(Gr_By = 1,"Groups 0005 And 0019" ....

HTH

 

[Pat Hartman]

If you have more than a few groups or you want to make it possible for the users to change the grouping without you, use a table. Then change the report's recordsource query to join to the grouping table to obtain the group code.

 

[y2k]

Pat,

Actually, after I posted this question I thought of exactly the same thing myself!! (of course it's easy to claim the credit now, isn't it!!)

Some groups numbers won't fall under any heading. They still get grouped together, but I want to put a heading on the report for each group of groups - have I lost you yet!!?? Basicly, what I want to know is, when I create the table, I enter the group# and the "group name" (eg 0019 & 0005 would be say Generic Groups and 9999, 0014 & 0022 would be a different name). What I want to know is, how can I put an entry in the table so that it'll put a title on all the remaining group numbers, say "default groups". i've tried entering a * and I've tried entering ???? using wildcards, but neither of these worked. When I group by group name in the report, I want to have a heading over each of these.

Thank you so much. And thank you for your reply too pdx man!!

 

[Pat Hartman]

You can handle an "incomplete" grouping table by using a left join. Join your main table to the grouping table on the key to the group table using a left join. Then use an IIf() to get the grouping value.

Select ..., IIf(IsNull(tblB.GroupField),"fixedvalue",tblB.GroupField) As MyGroup
From tblA Left join tblB ON tblA.SomeField = tblB.SomeField
Order by MyGroup;


If the order by clause doesn't work (it doesn't in all versions of Access), you'll need to repeat the IIf() in the order by clause -

Order By IIf(IsNull(tblB.GroupField),"fixedvalue",tblB.GroupField);