Advanced dropdown list

[neez]

Hi,

I'm trying to create a dropdown box, that is half a field list and
half Table/Query.

I'm working on a database for a conglomerate of labs. Four of these
are major labs, the other 22 are branch labs.

At the moment, I just have a dropdown box that has a query and pulls
in all of the labs, and this works fine. The problem is, that the four
major labs, are in the list but not at the top.

Ideally I would like to move these 4 labs to the top of the list, have
a space, and then show the rest of the 22. I have created an SQL
statement to remove the 4 labs. Does anyone know how to manually put
in the 4 labs I want at the top, and then the 22 branch labs ?

I could just do a value list, and type all the labs in the order I
want, but the branch labs drop off/on, so I want something dynamic.
The 4 major labs will never leave the list, so I'm happy to manually
put them in at the top of the list.

Alternatively, can you make a custom "Order By" in SQL ?

Thanks

 

[Hunter57]

Hi,

I'm trying to create a dropdown box, that is half a field list and
half Table/Query.

I'm working on a database for a conglomerate of labs. Four of these
are major labs, the other 22 are branch labs.

At the moment, I just have a dropdown box that has a query and pulls
in all of the labs, and this works fine. The problem is, that the four
major labs, are in the list but not at the top.

Ideally I would like to move these 4 labs to the top of the list, have
a space, and then show the rest of the 22. I have created an SQL
statement to remove the 4 labs. Does anyone know how to manually put
in the 4 labs I want at the top, and then the 22 branch labs ?

I could just do a value list, and type all the labs in the order I
want, but the branch labs drop off/on, so I want something dynamic.
The 4 major labs will never leave the list, so I'm happy to manually
put them in at the top of the list.

Alternatively, can you make a custom "Order By" in SQL ?

Thanks

I was intrigued by your question so I did a little experimenting and
found that the following SQL did the job for me.

SELECT LastName, FirstName, Suffix
FROM tblMembers
ORDER BY [LastName]="Quinn", [LastName]="Boyette",
tblMembers.LastName;

The [LastName]="Quinn" and [LastName]="Boyette" show up in the grid
like this: Expr1: [LastName]="Quinn" and in another column: Expr2:
[LastName]="Boyette". Also, the the Show box in these columns must be
not checked because if they are it adds two collumns full of numbers.

Thanks to you I now have learned something new.
Hope this helps.

Hunter57
http://churchmanagementsoftware.googlepages.com

 

[Guest]

you coud rank these labs in your table , then you would be able to sort them
by rank in your dropdown.

 

[neez]

I was intrigued by your question so I did a little experimenting and

found that the following SQL did the job for me.

SELECT LastName, FirstName, Suffix
FROM tblMembers
ORDER BY [LastName]="Quinn", [LastName]="Boyette",
tblMembers.LastName;

The [LastName]="Quinn" and [LastName]="Boyette" show up in the grid
like this: Expr1: [LastName]="Quinn" and in another column: Expr2:
[LastName]="Boyette". Also, the the Show box in these columns must be
not checked because if they are it adds two collumns full of numbers.

Thanks to you I now have learned something new.
Hope this helps.

Awesome, thanks Hunter,

That worked a treat.
I just changed my existing query to have the order by with the 4 labs
I wanted first, and then the rest.

Basically went from this

SELECT Panel.lab
FROM Panel
GROUP BY Panel.lab
ORDER BY [Panel].[lab];

to this

SELECT Panel.lab
FROM Panel
GROUP BY Panel.lab
ORDER BY [Panel].[lab]="FTH",[Panel].[lab]="PCP",[Panel].[lab]="RPH",
[Panel].[lab]="WCP",[Panel].[lab];

 

[Guest]

I'm glad it worked for you. Thanks for answering back. That lets others
know that the method works and they can use it for themselves.

Hunter57
http://churchmanagementsoftware.googlepages.com/home

I was intrigued by your question so I did a little experimenting and
found that the following SQL did the job for me.

SELECT LastName, FirstName, Suffix
FROM tblMembers
ORDER BY [LastName]="Quinn", [LastName]="Boyette",
tblMembers.LastName;

The [LastName]="Quinn" and [LastName]="Boyette" show up in the grid
like this: Expr1: [LastName]="Quinn" and in another column: Expr2:
[LastName]="Boyette". Also, the the Show box in these columns must be
not checked because if they are it adds two collumns full of numbers.

Thanks to you I now have learned something new.
Hope this helps.

Awesome, thanks Hunter,

That worked a treat.
I just changed my existing query to have the order by with the 4 labs
I wanted first, and then the rest.

Basically went from this

SELECT Panel.lab
FROM Panel
GROUP BY Panel.lab
ORDER BY [Panel].[lab];

to this

SELECT Panel.lab
FROM Panel
GROUP BY Panel.lab
ORDER BY [Panel].[lab]="FTH",[Panel].[lab]="PCP",[Panel].[lab]="RPH",
[Panel].[lab]="WCP",[Panel].[lab];