query problem

M

Martin

Here is an example of what I currently have as a table.

LNAME FNAME EMAIL FRA TCHR MARK
DOE JON [email protected] Y Y
SMITH ROY [email protected] Y Y

This is how I would like to reformat the table.

LNAME FNAME EMAIL FRA TCHR MARK
DOE JON [email protected] Y
DOE JON [email protected] Y
SMITH ROY [email protected] Y
SMITH ROY [email protected] Y

If this can be accomplished in a query this would take away plenty work. I'm
thinking it can be run as a SQL statement maybe?

Your help is appreciated,

Mark
 
K

KARL DEWEY

Use these three queries to build another table and put the data as you
wanted. Run in the order listed as the first is a make-table and the others
append queries.

SELECT MartinTable.LNAME, MartinTable.FNAME, MartinTable.EMAIL,
MartinTable.FRA, "" AS TCHR, "" AS MARK INTO [MartinTable-1]
FROM MartinTable
WHERE (((MartinTable.FRA) Is Not Null) AND ((MartinTable.TCHR) Is Not Null)
AND ((MartinTable.MARK) Is Null)) OR (((MartinTable.FRA) Is Not Null) AND
((MartinTable.TCHR) Is Null) AND ((MartinTable.MARK) Is Not Null)) OR
(((MartinTable.FRA) Is Not Null) AND ((MartinTable.TCHR) Is Null) AND
((MartinTable.MARK) Is Null));

INSERT INTO [MartinTable-1] ( LNAME, FNAME, EMAIL, TCHR )
SELECT MartinTable.LNAME, MartinTable.FNAME, MartinTable.EMAIL,
MartinTable.TCHR
FROM MartinTable
WHERE (((MartinTable.TCHR) Is Not Null) AND ((MartinTable.FRA) Is Not Null)
AND ((MartinTable.MARK) Is Null)) OR (((MartinTable.TCHR) Is Not Null) AND
((MartinTable.FRA) Is Null) AND ((MartinTable.MARK) Is Not Null)) OR
(((MartinTable.TCHR) Is Not Null) AND ((MartinTable.FRA) Is Null) AND
((MartinTable.MARK) Is Null));

INSERT INTO [MartinTable-1] ( LNAME, FNAME, EMAIL, MARK )
SELECT MartinTable.LNAME, MartinTable.FNAME, MartinTable.EMAIL,
MartinTable.MARK
FROM MartinTable
WHERE (((MartinTable.MARK) Is Not Null) AND ((MartinTable.FRA) Is Not Null)
AND ((MartinTable.TCHR) Is Null)) OR (((MartinTable.MARK) Is Not Null) AND
((MartinTable.FRA) Is Null) AND ((MartinTable.TCHR) Is Not Null)) OR
(((MartinTable.MARK) Is Not Null) AND ((MartinTable.FRA) Is Null) AND
((MartinTable.TCHR) Is Null));
 
D

Duane Hookom

You can use a union query:
SELECT LNAME, FNAME, EMAIL, FRA, Null as TCHR, Null as MARK
FROM table
WHERE FRA is not Null
UNION ALL

SELECT LNAME, FNAME, EMAIL, Null, TCHR, Null as MARK
FROM table
WHERE TCHR is not Null
UNION ALL

SELECT LNAME, FNAME, EMAIL, Null, Null, MARK
FROM table
WHERE MARK is not Null;
 
Top