Union query? "All Sites"

[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), ' ')

 

[Gerald Stanley]

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

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 + ',

' + REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10),
' ') AS Expr1,

dbo.Location.loc_postcode
FROM dbo.Location INNER JOIN
dbo.Company ON

dbo.Location.loc_company = dbo.Company.comp_id

ORDER BY dbo.Company.comp_name + ', ' +

REPLACE(dbo.Location.loc_address, CHAR(13) + CHAR(10), ' ')