Union query? "All Sites"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello everyone
I have a problem with a union query I would like to use in my data access project. I have made a single form that contains comboboxes that allow users to choose which report they would like and the criteria, e.g. date range, site, region, customer, they click a button and they report runs using those input parameters, this all works , but for each of these I would also like an "All" option. I have read that I need to use a Union Query, but I cannot get one to work using examples I have found. Any advice would be greatly appreciated, and ease my headache. The row source for one of my comboboxes i

SELECT dbo.Location.loc_id, dbo.Company.comp_name + ', ' + REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10), ' ') AS Expr1,
dbo.Location.loc_postcod
FROM dbo.Location INNER JOI
dbo.Company ON dbo.Location.loc_company = dbo.Company.comp_i
ORDER BY dbo.Company.comp_name + ', ' + REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10), ' ')
 
Try something along the following lines

SELECT "ALL" AS Company,"" AS PostCode, 1 AS RecType FROM
dbo.Location
UNION
SELECT dbo.Location.loc_id, dbo.Company.comp_name + ',
' + REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10),
' '), dbo.Location.loc_postcode, 2
FROM dbo.Location INNER JOIN
dbo.Company ON
dbo.Location.loc_company = dbo.Company.comp_id
ORDER BY RecType, Company

Hope This Helps
Gerald Stanley MSCD
-----Original Message-----
Hello everyone,
I have a problem with a union query I would like to use in
Click to expand...
my data access project. I have made a single form that
contains comboboxes that allow users to choose which report
they would like and the criteria, e.g. date range, site,
region, customer, they click a button and they report runs
using those input parameters, this all works , but for each
of these I would also like an "All" option. I have read
that I need to use a Union Query, but I cannot get one to
work using examples I have found. Any advice would be
greatly appreciated, and ease my headache. The row source
for one of my comboboxes is
SELECT dbo.Location.loc_id, dbo.Company.comp_name + ',
Click to expand...
' + REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10),
' ') AS Expr1,
dbo.Location.loc_postcode
FROM dbo.Location INNER JOIN
dbo.Company ON
Click to expand...
dbo.Location.loc_company = dbo.Company.comp_id
ORDER BY dbo.Company.comp_name + ', ' +
Click to expand...
REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10), ' ')
 
Back
Top