SQl Access - Sort output data

[WitsendSolutions]

Hi Team,

In need of your expertise....

I have a query that I'm running against a access 97 database.

code
SELECT mergeddatabase.Roomno, Count(*) AS Boxesscanned, Sum(mergeddatabase.Currentweight) AS RoomsTotalweight, mergeddatabase.Currentdate AS RequestedDate
FROM mergeddatabase WHERE (((mergeddatabase.Currentdate) Between [Forms]![FrmDateRange]![TxtStartDate] And [Forms]![FrmDateRange]![TxtEndDate]))
GROUP BY mergeddatabase.Roomno, mergeddatabase.Currentdate;/code

It is currently grouping the output by the RoomNo & CurrentDate. The resulting output is correct but I need to change the order it comes out as.

We have 12 growing sheds which are grouped in 3's. Meaning Sheds 1,5,6 are one group, 2,9,10 are another etc..



This query currently displays the output as:-

RoomNo1 nnnnnnn yyyyyy
RoomNo2 nnnnnnn YYYYYY
RoomNo3 nnnnnnn YYYYYY etc..

I need:-

RoomNo1 nnnnnnn yyyyyy
RoomNo5 nnnnnnn YYYYYY
RoomNo6 nnnnnnn YYYYYY

I do not want a prompt to enter the room numbers as I want it hardcoded in the query.

Look forward to your help.

[JoeM]

Create a "reference" table that lists each shed and what group it is in.
Then link the other table in with this table, and sort by group first, then shed.

[WitsendSolutions]

Thanks JoeM.

I'll give that a shot.

[WitsendSolutions]

Sorry JoeM...

I dont want the easy way out.. but I've searched for "reference" table in google, but can not find an example how to do this. I'm competent in joins, but missing the structure how to define it in a access table. And how to get the output displayed in the correct manner

[JoeM]

Don't get caught up in nomenclature! All that it really is is a simple two column table that you will creating (sort of a "lookup" table, if you prefer that term), that lists each shed and which group that they belong to. Once you set up this table, you should rarely ever need to edit it (unless you add new shed numbers down the road or change how they are grouped).

Then in your query, you just link your other table to this table on the shed number. Return the same fields to the query view as your did before, but now also add the group number, and sort by that.