ppdPrintPreview combining two Access queries

[VB.Goofy]

I would like to print all sales by user and region then print the user and regions with no sales (in one report). I use the code below to successfully print each one seperately but I would like the results in one report. I'm probably missing something easy.

Short of combining the data into a hidden grid then printing the grid is there a way to write the query? Or erhaps exiting out with the hasMorePages somehow?

g_strMySQL = "Select * From EMPL Where Sales > 0 Order By Lname, FName, Region"
g_Connect_To_Reader()
If g_objReader.HasRows Then
ppdPrintPreview.Document = Me.prtDocument
Me.prtDocument.Dispose()
ppdPrintPreview.WindowState = FormWindowState.Maximized
ppdPrintPreview.FormBorderStyle = FormBorderStyle.Fixed3D
ppdPrintPreview.ShowDialog()
End If
g_strMySQL = "Select * From EMPL Where Orders < 1 Order By Lname, FName, Region"
g_Connect_To_Reader()
If g_objReader.HasRows Then
ppdPrintPreview.Document = Me.prtDocument
Me.prtDocument.Dispose()
ppdPrintPreview.WindowState = FormWindowState.Maximized
ppdPrintPreview.FormBorderStyle = FormBorderStyle.Fixed3D
ppdPrintPreview.ShowDialog()
End If

 

[InertiaM]

I dont know the Access syntax, but essentially it's something like :-

SELECT
  CASE
    WHEN EMPL.Sales > 0 THEN 1
    WHEN EMPL.Orders < 1 THEN 5000
    ELSE 9999 END AS SALESEXIST
  *
FROM
  EMPL
WHERE
  Sales > 0
OR
  Orders < 1
ORDER BY
  CASE
    WHEN EMPL.Sales > 0 THEN 1
    WHEN EMPL.Orders < 1 THEN 5000
    ELSE 9999 END,
  Lname,
  FName,
  Region

This will give you all the records from both of your queries. The "Order by" section is the most important. All the records with "Sales > 0" will be first (because they have been classified as 1). Then all the records with "Orders < 1", as they have been classified as 5000. Any other records that have been retrieved, that have not been classified above, will be at the bottom (with classification 9999).

Please note - the Access syntax will need to be verified

 

[VB.Goofy]

Thanks alot. You pointed me in the perfect direction. Here is the VB.Net/Access version:

g_strMySQL = "Select *,iif(Sales > 0,1,0) As chkSales From EMPL Order By iif(Sales > 0,2) desc, LName, FName, Region"