Prev: Drag and Drop [Access 2007]
Next: Cutting excess rows
From: Leslie Charles on 7 Apr 2010 16:02
The following example is a simplification to make my
question less complicated.

I have a database of 3 people: Brown, Jones, and
Smith.

Each person in the DB has 2 fields associated with
their name field: Field “A” and “B”. The values in
A and B are either 0 or 1. The following SQL
string extracts a query as shown below.

SELECT tblPeople.LName,tblPeople.A,tblPeople.B FROM tblPeople

Brown 1 0
Jones 1 1
Smith 0 0

But the IT department wants the data as follows:

Brown A 1
Brown B 0
Jones A 1
Jones B 1
Smith A 0
Smith B 0

Is there a way to change the SQL string to provide the needed output?

--
Leslie Charles
From: Jerry Whittle on 7 Apr 2010 16:34
SELECT tblPeople.LName, "A", tblPeople.A
FROM tblPeople
UNION
SELECT tblPeople.LName, "B", tblPeople.B
FROM tblPeople
ORDER BY 1, 2 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Leslie Charles" wrote:

> The following example is a simplification to make my
> question less complicated.
>
> I have a database of 3 people: Brown, Jones, and
> Smith.
>
> Each person in the DB has 2 fields associated with
> their name field: Field “A” and “B”. The values in
> A and B are either 0 or 1. The following SQL
> string extracts a query as shown below.
>
> SELECT tblPeople.LName,tblPeople.A,tblPeople.B FROM tblPeople
>
> Brown 1 0
> Jones 1 1
> Smith 0 0
>
> But the IT department wants the data as follows:
>
> Brown A 1
> Brown B 0
> Jones A 1
> Jones B 1
> Smith A 0
> Smith B 0
>
> Is there a way to change the SQL string to provide the needed output?
>
> --
> Leslie Charles
From: John W. Vinson on 7 Apr 2010 16:59
On Wed, 7 Apr 2010 13:02:04 -0700, Leslie Charles
<LeslieCharles(a)discussions.microsoft.com> wrote:

>The following example is a simplification to make my
>question less complicated.
>
>I have a database of 3 people: Brown, Jones, and
>Smith.
>
>Each person in the DB has 2 fields associated with
>their name field: Field �A� and �B�. The values in
>A and B are either 0 or 1. The following SQL
>string extracts a query as shown below.
>
>SELECT tblPeople.LName,tblPeople.A,tblPeople.B FROM tblPeople
>
>Brown 1 0
>Jones 1 1
>Smith 0 0
>
>But the IT department wants the data as follows:
>
>Brown A 1
>Brown B 0
>Jones A 1
>Jones B 1
>Smith A 0
>Smith B 0
>
>Is there a way to change the SQL string to provide the needed output?

A UNION query will do this:

SELECT tblPeople.LName, "A" AS FromWhat, tblPeople.A FROM tblPeople
UNION ALL
SELECT tblPeople.LName, "B", tblPeople.B FROM tblPeople
ORDER BY 1, 2;
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Drag and Drop [Access 2007]
Next: Cutting excess rows