query problem

[Guest]

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

LNAME FNAME EMAIL FRA TCHR MARK
DOE JON (e-mail address removed) Y Y
SMITH ROY (e-mail address removed) Y Y

This is how I would like to reformat the table.

LNAME FNAME EMAIL FRA TCHR MARK
DOE JON (e-mail address removed) Y
DOE JON (e-mail address removed) Y
SMITH ROY (e-mail address removed) Y
SMITH ROY (e-mail address removed) 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

 

[Guest]

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));

 

[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;