How to position specific record(s) at top of list
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am working with all currency types and rates in a query. Because they are the most popular, is it possible to have USD and EUR positioned at the top of a query list before displaying the rest of the currency codes in alphabetical order?
ms-access access-vba ms-access-2016
asked Nov 22 '18 at 21:32
John CJohn C
278
add a comment |
I am working with all currency types and rates in a query. Because they are the most popular, is it possible to have USD and EUR positioned at the top of a query list before displaying the rest of the currency codes in alphabetical order?
ms-access access-vba ms-access-2016
asked Nov 22 '18 at 21:32
John CJohn C
278
add a comment |
I am working with all currency types and rates in a query. Because they are the most popular, is it possible to have USD and EUR positioned at the top of a query list before displaying the rest of the currency codes in alphabetical order?
ms-access access-vba ms-access-2016
asked Nov 22 '18 at 21:32
John CJohn C
278
I am working with all currency types and rates in a query. Because they are the most popular, is it possible to have USD and EUR positioned at the top of a query list before displaying the rest of the currency codes in alphabetical order?
ms-access access-vba ms-access-2016
ms-access access-vba ms-access-2016
asked Nov 22 '18 at 21:32
John CJohn C
278
asked Nov 22 '18 at 21:32
John CJohn C
278
asked Nov 22 '18 at 21:32
John CJohn C
278
asked Nov 22 '18 at 21:32
John CJohn C
278
asked Nov 22 '18 at 21:32
John CJohn C
278
278
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Let's say your table is called tblRate having two columns, Rate and Curr.
Create a query that uses the Switch function to assign a ranking order to USD (ranking order 1), EUR (raking order 2).
Assign ranking order 3 to all other currencies using the Nz function.
In your query you order by rank, next by currency.
SQL view:
SELECT Rate, Curr
FROM tblRate
ORDER BY Nz(Switch(Curr = "USD",1, Curr = "EUR",2),3), Curr
answered Nov 22 '18 at 21:54
ReneRene
9001514
add a comment |
If you have a table providing the source data for the list, add a 'OrderNum' column to the table, then put 1 and 2 into USD and EUR, respectively. Then, in everything else, put a larger number, such as 3. If they all need to follow alphabetical order after that, make the sort command have two variables in the query that provides data to the list box or drop down. If your table containing the currency codes is called 'tblCurrencyCode', and the field containing the codes is called 'CurrencyCode', your code would look something like this:
SELECT CurrencyCode
FROM tblCurrencyCode
ORDER BY OrderNum ASC, CurrencyCode ASC
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
add a comment |
An alternate method would be:
Create a query for most used currencies from your currency table and then join the list with your currency list query.
Something like.
Select currency
from QryMostUsedCurrencies
Union all
Select currency from CurrencyTable
where currency not in (select currency from QryMostUsedCurrencies)
In this way, you can keep a list of most used currencies in a separate query. Either by dynamically looking at past transactions or simple select query from Currency Table for those items you want to appear on top.
This helps you to change your most used currencies in one place rather than having ranking sql in multiple places.
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53438188%2fhow-to-position-specific-records-at-top-of-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Let's say your table is called tblRate having two columns, Rate and Curr.
Create a query that uses the Switch function to assign a ranking order to USD (ranking order 1), EUR (raking order 2).
Assign ranking order 3 to all other currencies using the Nz function.
In your query you order by rank, next by currency.
SQL view:
SELECT Rate, Curr
FROM tblRate
ORDER BY Nz(Switch(Curr = "USD",1, Curr = "EUR",2),3), Curr
answered Nov 22 '18 at 21:54
ReneRene
9001514
add a comment |
Let's say your table is called tblRate having two columns, Rate and Curr.
Create a query that uses the Switch function to assign a ranking order to USD (ranking order 1), EUR (raking order 2).
Assign ranking order 3 to all other currencies using the Nz function.
In your query you order by rank, next by currency.
SQL view:
SELECT Rate, Curr
FROM tblRate
ORDER BY Nz(Switch(Curr = "USD",1, Curr = "EUR",2),3), Curr
answered Nov 22 '18 at 21:54
ReneRene
9001514
add a comment |
Let's say your table is called tblRate having two columns, Rate and Curr.
Create a query that uses the Switch function to assign a ranking order to USD (ranking order 1), EUR (raking order 2).
Assign ranking order 3 to all other currencies using the Nz function.
In your query you order by rank, next by currency.
SQL view:
SELECT Rate, Curr
FROM tblRate
ORDER BY Nz(Switch(Curr = "USD",1, Curr = "EUR",2),3), Curr
answered Nov 22 '18 at 21:54
ReneRene
9001514
Let's say your table is called tblRate having two columns, Rate and Curr.
Create a query that uses the Switch function to assign a ranking order to USD (ranking order 1), EUR (raking order 2).
Assign ranking order 3 to all other currencies using the Nz function.
In your query you order by rank, next by currency.
SQL view:
SELECT Rate, Curr
FROM tblRate
ORDER BY Nz(Switch(Curr = "USD",1, Curr = "EUR",2),3), Curr
answered Nov 22 '18 at 21:54
ReneRene
9001514
answered Nov 22 '18 at 21:54
ReneRene
9001514
answered Nov 22 '18 at 21:54
ReneRene
9001514
answered Nov 22 '18 at 21:54
ReneRene
9001514
9001514
add a comment |
add a comment |
If you have a table providing the source data for the list, add a 'OrderNum' column to the table, then put 1 and 2 into USD and EUR, respectively. Then, in everything else, put a larger number, such as 3. If they all need to follow alphabetical order after that, make the sort command have two variables in the query that provides data to the list box or drop down. If your table containing the currency codes is called 'tblCurrencyCode', and the field containing the codes is called 'CurrencyCode', your code would look something like this:
SELECT CurrencyCode
FROM tblCurrencyCode
ORDER BY OrderNum ASC, CurrencyCode ASC
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
add a comment |
If you have a table providing the source data for the list, add a 'OrderNum' column to the table, then put 1 and 2 into USD and EUR, respectively. Then, in everything else, put a larger number, such as 3. If they all need to follow alphabetical order after that, make the sort command have two variables in the query that provides data to the list box or drop down. If your table containing the currency codes is called 'tblCurrencyCode', and the field containing the codes is called 'CurrencyCode', your code would look something like this:
SELECT CurrencyCode
FROM tblCurrencyCode
ORDER BY OrderNum ASC, CurrencyCode ASC
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
add a comment |
If you have a table providing the source data for the list, add a 'OrderNum' column to the table, then put 1 and 2 into USD and EUR, respectively. Then, in everything else, put a larger number, such as 3. If they all need to follow alphabetical order after that, make the sort command have two variables in the query that provides data to the list box or drop down. If your table containing the currency codes is called 'tblCurrencyCode', and the field containing the codes is called 'CurrencyCode', your code would look something like this:
SELECT CurrencyCode
FROM tblCurrencyCode
ORDER BY OrderNum ASC, CurrencyCode ASC
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
If you have a table providing the source data for the list, add a 'OrderNum' column to the table, then put 1 and 2 into USD and EUR, respectively. Then, in everything else, put a larger number, such as 3. If they all need to follow alphabetical order after that, make the sort command have two variables in the query that provides data to the list box or drop down. If your table containing the currency codes is called 'tblCurrencyCode', and the field containing the codes is called 'CurrencyCode', your code would look something like this:
SELECT CurrencyCode
FROM tblCurrencyCode
ORDER BY OrderNum ASC, CurrencyCode ASC
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
answered Nov 23 '18 at 4:30
Destin ConsultingDestin Consulting
765
765
add a comment |
add a comment |
An alternate method would be:
Create a query for most used currencies from your currency table and then join the list with your currency list query.
Something like.
Select currency
from QryMostUsedCurrencies
Union all
Select currency from CurrencyTable
where currency not in (select currency from QryMostUsedCurrencies)
In this way, you can keep a list of most used currencies in a separate query. Either by dynamically looking at past transactions or simple select query from Currency Table for those items you want to appear on top.
This helps you to change your most used currencies in one place rather than having ranking sql in multiple places.
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
add a comment |
An alternate method would be:
Create a query for most used currencies from your currency table and then join the list with your currency list query.
Something like.
Select currency
from QryMostUsedCurrencies
Union all
Select currency from CurrencyTable
where currency not in (select currency from QryMostUsedCurrencies)
In this way, you can keep a list of most used currencies in a separate query. Either by dynamically looking at past transactions or simple select query from Currency Table for those items you want to appear on top.
This helps you to change your most used currencies in one place rather than having ranking sql in multiple places.
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
add a comment |
An alternate method would be:
Create a query for most used currencies from your currency table and then join the list with your currency list query.
Something like.
Select currency
from QryMostUsedCurrencies
Union all
Select currency from CurrencyTable
where currency not in (select currency from QryMostUsedCurrencies)
In this way, you can keep a list of most used currencies in a separate query. Either by dynamically looking at past transactions or simple select query from Currency Table for those items you want to appear on top.
This helps you to change your most used currencies in one place rather than having ranking sql in multiple places.
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
An alternate method would be:
Create a query for most used currencies from your currency table and then join the list with your currency list query.
Something like.
Select currency
from QryMostUsedCurrencies
Union all
Select currency from CurrencyTable
where currency not in (select currency from QryMostUsedCurrencies)
In this way, you can keep a list of most used currencies in a separate query. Either by dynamically looking at past transactions or simple select query from Currency Table for those items you want to appear on top.
This helps you to change your most used currencies in one place rather than having ranking sql in multiple places.
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
edited Nov 23 '18 at 20:49
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
answered Nov 22 '18 at 22:00
krish KMkrish KM
4,5021728
4,5021728
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53438188%2fhow-to-position-specific-records-at-top-of-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
