473,320 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with Union Query

PhilOfWalton
1,430 Expert 1GB
Here is a Union Query to give an <All> option in a Combo Box
Expand|Select|Wrap|Line Numbers
  1. SELECT "<All>" as CCenter,  0 as BranchID,  1 AS SortOrder FROM TblBranch
  2. UNION SELECT  CCenter , BranchID,  2 AS SortOrder
  3. FROM TblBranch
  4. WHERE (((Nz([CCenter]))<>0))
  5. ORDER BY SortOrder, CCenter;
  6.  
The CCenter is a Long Number. The problem appears to be that once I use the <All>, CCenter is regarded as a text field, and the sort order is wrong.

Here are the first few results. As you see the 100 is the first (text value). It should be the last value
Expand|Select|Wrap|Line Numbers
  1. CCenter    BranchID    SortOrder
  2. <All>    0    1
  3. 100    9    2
  4. 63    26    2
  5. 69    27    2
  6. 70    6    2
  7. 72    19    2
  8. 74    11    2
  9. 75    12    2
  10.  

If I replace the <All> with 0 it works as expected

How do I get over this?

Thanks

Phil
Jul 28 '16 #1

✓ answered by jforbes

Maybe this would work?
Expand|Select|Wrap|Line Numbers
  1. SELECT "<All>" as CCenter,  0 as BranchID,  1 AS SortOrder FROM TblBranch
  2. UNION SELECT  CCenter , BranchID,  CCenter AS SortOrder
  3. FROM TblBranch
  4. WHERE (((Nz([CCenter]))<>0))
  5. ORDER BY SortOrder;

4 940
jforbes
1,107 Expert 1GB
Maybe this would work?
Expand|Select|Wrap|Line Numbers
  1. SELECT "<All>" as CCenter,  0 as BranchID,  1 AS SortOrder FROM TblBranch
  2. UNION SELECT  CCenter , BranchID,  CCenter AS SortOrder
  3. FROM TblBranch
  4. WHERE (((Nz([CCenter]))<>0))
  5. ORDER BY SortOrder;
Jul 28 '16 #2
nico5038
3,080 Expert 2GB
The problem is a "text sort". Only way to solve this is to force the numbers to have prefix zero's. Something like:
RIGHT('000000'&[fieldname],5)
would suffide when 5 digits will be the max length.

Nic;o)

BTW the first row in a union sets the data type...
for the number field you could also use:
RIGHT(100000 + [fieldname],5)
Jul 28 '16 #3
PhilOfWalton
1,430 Expert 1GB
Thanks jforbs. Worked perfectly other than the initial SortOrder should be 0, not 1

Thanks again

Phil
Jul 28 '16 #4
NeoPa
32,556 Expert Mod 16PB
Nice J. Where's the thumbs-up button when you need it?

Nico's post explains the original problem.
Jul 29 '16 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID ...
0
by: s_gregory | last post by:
The mdb is considerable size 70 +- mb. A complex union query was working well, but when an additional union select... was added into the query, selecting identical fields from a different source,...
4
by: Missy | last post by:
We’ve recently upgraded our computer system to XP. My union query (which was working perfectly for years) now returns hieroglyphics instead of invoice numbers. When I run the 2 queries...
5
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed...
2
by: Thomas | last post by:
Hi All. I think that if I explain the database layout first it may be easier to ask my question. Table Name Field Name Test TestCode Description SpecimenRequirements FeeSchedule TestCode...
2
by: MLH | last post by:
I have a saved UNION query named qryAuthsOwnersAddnlOwnersLienholders4OneCar. Here it is... SELECT & " " & & " " & & " " & & ", " & & " " & & " " & "(" & & " county) " &...
1
by: ebasshead | last post by:
Hi Everyone I have four queries that Ive joined in a union query and want to add a date criteria ei between and including date A and date B. Ive tried a few things but getting errors. Can someone...
3
by: paulquinlan100 | last post by:
Hi I'm trying to tweak the query below so that the second SELECT only pulls out one record per "Site_Ref", i.e. the record that has the maximum "appraisal_date" out of the 4 Appraisal_types. Is...
1
greeni91
by: greeni91 | last post by:
Hi, I am currently trying to create a report that list all the Features that have been checked and the metrology used to check said feature. I have created a UNION query that will combine the...
7
by: ittechguy | last post by:
I'm not sure if I have a SQL problem or a vba problem so if I've posted in the wrong section, I'm sorry. I have a search form which uses a union query as its RecordSource. I need to use a union...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.