Query to order lines a certain way

[cappiel]

I have a table that contains the following data:

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field9 Field10
(null) H (null) SLD (null) next 1/11/08
test1 N (null)
(null) H (null) SLD (null) next 1/31/08
test1 N (null)
1/11/08 (null) L (null) SLD next (null)
acct (null) 1
1/11/08 (null) L (null) SLD next (null)
acct (null) -1
1/31/08 (null) L (null) SLD next (null)
acct (null) 4
1/31/08 (null) L (null) SLD next (null)
acct (null) -4


I want the query results ( or whatever means necessary) to look like
this: For each "H" line, underneath it, should be the "L" lines for
that date:

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field9 Field10
(null) H (null) SLD (null) next 1/11/08
test1 N (null)
1/11/08 (null) L (null) SLD next (null)
acct (null) 1
1/11/08 (null) L (null) SLD next (null)
acct (null) -1
(null) H (null) SLD (null) next 1/31/08
test1 N (null)
1/31/08 (null) L (null) SLD next (null)
acct (null) 4
1/31/08 (null) L (null) SLD next (null)
acct (null) -4

Any help would be greatly appreciated. I'm not surehow my data table
will appear in the post so hopefully it's clear.

Thanks!!

 

[KARL DEWEY]

Try this ---
SELECT cappiel.*
FROM cappiel
ORDER BY IIf([Field2]="H",[Field7],[Field1]), IIf([Field2]="H",1,2);

 

[J_Goddard via AccessMonster.com]

Hi -

If your table structure is consistent with what you have shown, i.e. "H"
records have the date in field 7 with no data in Field 2, and "L" records
have the corresponding data in field 1 with no data in Field 7, i.e. the two
fields are mutually exclusive, then:

Make a query with the first field being a calculated date:

iif (isnull([field 7]),[field 2], [field 7])

This will give you the date of whichever of Field 7 or Field 2 is not null.

Make the second field another calculated field:

iif(isnull[Field 2]), [field 3], [field 2])

This whill give you "H" or "L".

Sort the query on fields 1 and 2.

John

 

[cappiel]

Thanks guys! Both worked great...