Thanks John,
When I ran the query you suggested, it definitely got me close but the cases now appear in the order 1,10,11,…2,20,21 but I then modified your suggestion slightly to
SELECT tblCases.CaseID, tblCases.OutbreakID, Format(tblCases.casenumber, "0000") AS [Case] ….
I used four digits rather than three in case there are > 999 cases for an outbreak.
I thought I had tried that but obviously not.
Many thanks,
Glenn
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, December 7, 2015 11:07 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Union Query Issue
Dear AgreedBlu- (name?)
The problem is a UNION converts the data type of the number to text because "Select Case" is text. You might try this:
SELECT tblCases.CaseID, tblCases.OutbreakID, Format(tblCases.casenumber, "##0") AS [Case]
FROM tblCases
WHERE (((tblCases.OutbreakID)=20))
ORDER BY CaseID,tblCases.Casenumber
UNION SELECT 0, null, " Select Case " from tblCases
ORDER BY 3;
That will force up to three digits to be "right aligned" to get them in the proper sequence.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Dec 7, 2015, at 4:26 PM, argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
My application assigns a case number to tblCases. The case number is not the PK of the table as the table represents cases for multiple Outbreaks (parent table). My issue is that I want my users to select from a combo list of cases for the current outbreak and I want the case numbers to appear in sequential order. I also want the text "Select Case" to appear in the combo before the user picks a case.
The problem is that the Union query does not present the case list in numerical order. The query without the Union does present the list in numerical order.
I have uploaded the file UnionQueryIssue.docx showing the table schema, the standard query sql and datasheet and the Union query sql and datasheet.
I have tried a number of workaround be keep ending up with the same results.
Any suggestions?
Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar