Shutting off sorting in a Union Query 2

[rcoutts]

I have a Union Query that groups the records of a few different queries into a single Query. It works great, except that I would like the order of the records to be the same order as in the SQL code. Alas, they are being sorted in the resulting Query by the values of the first field. How can I shut this off? Below is the code.

Thanks!
Rich

SELECT qryInvoiceFixedFee1.Category, qryInvoiceFixedFee1.Txt, qryInvoiceFixedFee1.Amount
FROM qryInvoiceFixedFee1;

UNION SELECT qryInvoiceFixedFee2.Category, qryInvoiceFixedFee2.Txt, qryInvoiceFixedFee2.Amount
FROM qryInvoiceFixedFee2;

UNION SELECT qryInvoiceFixedFee3.Category, qryInvoiceFixedFee3.Txt, qryInvoiceFixedFee3.Amount
FROM qryInvoiceFixedFee3;

[\code]

 

[Sawatzky]

Dear rcoutts,

I do not believe it is possible to hinder this.

Bytheway are you aware that union suppresses double data? i.e. if ever (in your statement) categorie and text and amount are the same in several records only one of them would be shown. Just asking because I saw Invoice in your table names.

regards Astrid

 

[rcoutts]

Thanks for the post. Being new to Access, maybe I should back up and ask a more general question. I would like to create a Report that is an Invoice. The format looks something like this:

   Balance
      Previous Balance              $1000
      Amount Received                  $0
      Interest @ 1.5%/month           $10
      Balance                       $1010

   Hours Fees
      Principal: 10 hrs@$100/hr.    $1000
      Draftsperson: 50 hrs@$40/hr.  $2000
         .
         .
         .

I'm curently using several Queries to collect the data and then combining them into a single Union Query whose output looks something like this:

   Balance        Previous Balance              $1000
   Balance        Amount Received                  $0
   Balance        Interest @ 1.5%/month           $10
   Balance        Balance                       $1010
   Hourly Fees    Principal: 10 hrs@$100/hr.    $1000
   Hourly Fees    Draftsperson: 50 hrs@$40/hr.  $2000
         .
         .
         .

I created a report, using the wizard, that groups the output of the Union Query into the format that I want. The unwanted side effect that I'm getting is the sorting of all of the columns, so my output looks like this:

   Balance
      Amount Received                  $0
      Balance                       $1010
      Interest @ 1.5%/month           $10
      Previous Balance              $1000

   Hours Fees
      Draftsperson: 50 hrs@$40/hr.  $2000
      Principal: 10 hrs@$100/hr.    $1000.
         .
         .

Where the order of the rows has been changed. Is there an alternative to the Union Query that I should look into? Also, I tried adding a 4th field to my Union Query that was a simply a number was used to sort the rows, which worked fine when shown. But when this field was hidden in the report, Access went back to its old sorting tricks.

Thanks again,
Rich

 

[Sawatzky]

dear rcoutts ,

normally these fields are only sorted when you told the report to do so.

there is an icon called groupin and sorting, where all the fields are listed, that are used for grouping and sorting.
Check whether your field is listed and if delete it there.

tell me whether this helped you out.

regards astrid

 

[tlbroadbent]

Use the ALL predicate with the UNION to stop sorting and elimination of duplicates. By default, Access eliminates duplicates in a UNION query. In order to do that, it must sort the data. Use of the ALL predicate inhibits that functionality.

Example:

Select col1, col2 From table1
Union All
Select col1, col2 From table2
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.