Group by pairs of 2

[marabozu]

Hi,

How can I group by 2? I know how to group by date, datepart, samevalue, but by a pair I don't know...

For example:
table A with the records already sorted:

a b c d e f g h i j

and I want to group (ab) (cd) (ef) (gh) (ij)

how it is possible?

thanks in advance

M. Neto

 

[KenG]

Since this was posted last week I don't know if you've figured something out yet, but this is what I'd try.
Create a query "qryEven" using the following
CharacterNumber: Asc([Letter]) Mod 2
Set the criteria for this field = 0. This will be the even numbered letters. Don't forget to include the original letter field.

Create another query "qryOdd" with the criteria = 1. This will be the odd numbered letters.

Now create a third critera using the previous two queries with the following field
Difference:Asc([qryOdd].[Letter]) - Asc([qryEven].[Letter])
Set the criteria for this field = 1

You can also add another field to get the values you want:
ThisIsWhatIWanted: [qryOdd].[Letter] & " " & [qryEven].[Letter]

I've already done all this, if you want the queries let me know and I can e-mail them to you.
Hope this helps,

 

[Guest_imported]

You could use switch function to group things by first letter. Say you have a product table with a field called ProductName
then enter this switch statement to group names together.
I only went up to f, but you can get the idea.
This works for Column headings in crosstabs too.
Use this as field heading in groupby query, then count or sum some other field in the query.

Switch([ProductName] Like "[a-b]*","A-B",[ProductName] Like "[c-d]*","C-D",[ProductName] Like "[e-f]*","E-F",True,"Other&quot

 

[KenG]

This is why I like this forum. I don't think I'd ever find the Switch function on my own.
Thanks a lot,