Append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello experts:

I have tblTitle with primary key field CaseNo, and fields Owner1, Owner2 . .
.. Owner10. I would like to create a new table tblTitleOwner from data in
tblTitle, but with just one Owner field and assign a secondary key to each
unique owner within that record.

An append query will not work as far as I can tell, since the owner data in
tblTitle does not always have a unique value (ie, Owner1 and Owner2 will have
the same CaseNo).

Any suggestions?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The combination of the CaseNo and the Owner creates a Primary Key in the
new table (a way to uniquely ID the row [aka Record]). Something like
this:

tblTitleOwner
- ---------
CaseNo - PK
Owner - PK
< other columns >

Then write an append query like this, using the UNION query:

INSERT INTO tblTitleOwner (CaseNo, Owner, <other columns>)
FROM (
SELECT CaseNo, Owner1, < other columns >
FROM tblTitle
WHERE Owner1 IS NOT NULL

UNION ALL

SELECT CaseNo, Owner2, < other columns >
FROM tblTitle
WHERE Owner2 IS NOT NULL

UNION ALL

SELECT CaseNo, Owner3, < other columns >
FROM tblTitle
WHERE Owner3 IS NOT NULL

UNION ALL

SELECT CaseNo, Owner4, < other columns >
FROM tblTitle
WHERE Owner4 IS NOT NULL

....

UNION ALL

SELECT CaseNo, Owner10, < other columns >
FROM tblTitle
WHERE Owner10 IS NOT NULL

) AS A

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRByEQ4echKqOuFEgEQKrzgCfestgxA0CgYW99Y/78kSaZma2VnEAnj8D
F9QNHdaxzLBEaG0MAmQ+7QhG
=SV7t
-----END PGP SIGNATURE-----
 
Back
Top