How to insert a column with certain values

  • Thread starter Thread starter Amin
  • Start date Start date
A

Amin

So this is my query:

SELECT COUNT(REGBASE) AS Occurences
FROM WFLOW
WHERE REGBASE > 100000 AND STARTQUEUE IN("4ADIV","4BDIV","4CDIV","4DDIV",)
UNION
SELECT COUNT(REGBASE) AS Occurences
FROM WFLOW
WHERE REGBASE BETWEEN 100000 AND 1 AND STARTQUEUE
IN("4ADIV","4BDIV","4CDIV","4DDIV",)
UNION
SELECT COUNT(REGBASE) AS Occurences
FROM WFLOW
WHERE REGBASE = 0 AND STARTQUEUE IN("4ADIV","4BDIV","4CDIV","4DDIV",);

So this produces a table that has one Field--called Occurences) and three
records with different numbers. What I would like to do is to insert another
field to the left with three records with values "A", "B" and "C". Is this
possible without creating another table?

Thanks!
Amin
 
Try this --
SELECT "A" AS Occurences, COUNT(REGBASE) AS Occurences
FROM WFLOW
WHERE REGBASE > 100000 AND STARTQUEUE IN("4ADIV","4BDIV","4CDIV","4DDIV",)
UNION
SELECT "B" AS Occurences, COUNT(REGBASE) AS Occurences
FROM WFLOW
WHERE REGBASE BETWEEN 100000 AND 1 AND STARTQUEUE
IN("4ADIV","4BDIV","4CDIV","4DDIV",)
UNION
SELECT "C" AS Occurences, COUNT(REGBASE) AS Occurences
FROM WFLOW
WHERE REGBASE = 0 AND STARTQUEUE IN("4ADIV","4BDIV","4CDIV","4DDIV",);
 
That might have a small problems since you've aliased both fields to the same
name. You might change the first Occurences to CategoryType in each of the
queries.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
You are correct. I first used field named Table but then only glanced at the
post and saw 'Field--called Occurences' without reading the whole sentence.
 
Back
Top