Splitting Records

[Guest]

Many years ago I created an employment application tracking database when I
was still discovering the power of Access. Inexperienced as I was, I did not
make the REFERENCE table one-to-many with my APPLICANT table; I made it
one-to-one with REFERENCE1NAME, REFERENCE1PHONE...REFERENCE2NAME,
REFERENCE2Phone, etc for each record. (we only needed to check two
references at the time)

Since I am finally getting to pass this database on to a co-worker(lucky
him!) to use and we now have to check more than two references on each
potential employee, what would be the easiset way to normalize my REFERNECE
table without losing any of my previuosly enetered data going forward? (data
goes all the way back to 2001 and we have a 95% turn-over rate, so you can
imaging how many incorrectly related records there are in the REFERENCE
table...)

 

[Douglas J. Steele]

You can use a UNION query.

SELECT Field1, Field2, 1 As ReferenceNumber,
Reference1Name, Reference1Phone
FROM MyTable
UNION ALL
SELECT Field1, Field2, 2 As ReferenceNumber,
Reference2Name, Reference2Phone
FROM MyTable

 

[Guest]

Perfect, sir! Thank you. I knew it was something easy, but I couldn't quite
get my head around it.

Thank you again!!