Grouping using multiple fields

[bbulla]

Hi,

I'm trying to group based on two fields. The FROMMH and the TOMH contain ID values for manholes and I want all of the results for (as an example) MH-G10-0001 to show up in the same group, regardless whether they are the FROMMH or the TOMH.

I've tried to set my grouping expression to [FROMMH] OR [TOMH] but that doesn't seem to work and just puts everything into one group.

Thanks,

 

[plog]

Do you want records to show up multiple times on the report? For example suppose you have this record:

FROMMH, TOMH
MH-G10-001, AA-B20-003

Would you want it to show up in both the MH-G10-001 and AA-B20-003 groupings? Or should it just show up in one? If so, which one?

 

[vbaInet]

(as an example) MH-G10-0001

What part is ToMH and what part is FromMH?

 

[bbulla]

Here is an example for manholes MH-E11-0002 and MH-E11-0003:

Date - ToMH - FromMH

27/10/2011 - MH-E11-0001 - MH-E11-0002
27/11/2011 - MH-E11-0002 - MH-E11-0003
27/10/2011 - MH-E11-0058 - MH-E11-0003
27/10/2011 - MH-E11-0003 - MH-E11-0077

This should produce two 'groups'. One for MH-E11-0002 with these records in it:

27/10/2011 - MH-E11-0001 - MH-E11-0002
27/11/2011 - MH-E11-0002 - MH-E11-0003

And another group for MH-E11-0003 with these records in it:

27/11/2011 - MH-E11-0002 - MH-E11-0003
27/10/2011 - MH-E11-0058 - MH-E11-0003
27/10/2011 - MH-E11-0003 - MH-E11-0077

 

[plog]

Now that I look at your ID number, I think this shouldn't be all one field but 3 different fields because it contains 3 discrete pieces of data (MH, E11, 0001). Does each part of the ID number mean something?


I followed the first example group, but the second one doesn't make sense:

And another group for MH-E11-000 with these records in it:

27/11/2011 - MH-E11-0002 - MH-E11-0003
27/10/2011 - MH-E11-0058 - MH-E11-0003
27/10/2011 - MH-E11-0003 - MH-E11-0077

None of those have MH-E11-0000 in them. Using your example data, I see 5 unique groupings: ...0001, ...0002, ...0003, ...0058, ...0077

 

[bbulla]

Sorry....there was a typo. The second ID should read MH-E11-0003.

 

[plog]

Okay. That means you do want records to appear multiple times if they have different ToMH and FromMH values. What you are going to need to do is create a UNION query to generate all the records that will be displayed. This is that SQL:

SELECT *, ToMH AS GroupingField FROM YourTableNameHere
UNION ALL
SELECT *, FromMH AS GroupingField FROM YourTableNameHere WHERE ToMH<>FromMH;

Replace YourTableNameHere with the name of the table/query the data is in. Then use that query as the basis of your report and group on the field called 'GroupingField'.

 

[bbulla]

OK, the this seems to be working but with some strange results.

The first time I created this and ran it, all worked perfectly. I got the results I was looking for. Then I saved it. Ever since then it is now asking me for a Parameter Value for GroupingField everytime I run it.

Why is it doing this? If I enter nothing, it works, but it shouldn't be asking me for anything.

 

[vbaInet]

You've most likely changed something somewhere.

* What parameter does it ask for?
* Show us the SQL statement as you currently have it.

 

[bbulla]

OK, so I recreated the same query, saved it with the default name, and then renamed it to what I wanted and now it works.

Weird.

 

[vbaInet]

Good to hear bbulla.