Append from main table to related table

[BruceM]

I posted a question related to this topic several days ago, to which Duane
Hookom responded. Since several days has elapsed I am starting a new
thread.

I have a database for tracking vendor information. I misdesigned elements
of the database several years ago, but now that I have learned some more I
am trying to correct the design. Each vendor listed as Approved (a Yes/No
field) needs a certificate. Some have more than one. In the original
design Cert1 and Cert2 were fields in the main table. There was no Cert3,
but there needs to be. There was more information about Cert1 (the main
cert) than Cert 2. For instance DateReturned is a field for Cert1, but not
for Cert2; same for the Yes/No field CertificateRequested.

Now I have a new related Certs table. Duane Hookum suggested I use a union
query as the source for an append query which would be used to populate the
related table. Based on his response to my question (and with very little
assistance from Help) I came up with the following:

SELECT VendorID, CertificateType as CertType, ExpirationDate as CertExp,
DateReturned, CertificateRequested as CertRequested
FROM tblVendor
WHERE Approved = True
UNION ALL
SELECT VendorID, Cert2 as CertType, Cert2Date as CertExp
FROM tblVendor
WHERE Approved = True;

DateReturned is the same field name in both the current table and the new
related table; the other fields have different names. Note that these are
not the actual field names, but rather simplified versions for purposes of
this inquiry.
When I attempt to run the query, I receive this error message:
"The number of columns in the two selected tables or queries of a union
query do not match."
I expect this is because the second SELECT contains fewer fields, but I
don't know what to do about that.

 

[Edward Reid]

SELECT VendorID, CertificateType as CertType, ExpirationDate as CertExp,

DateReturned, CertificateRequested as CertRequested
FROM tblVendor
WHERE Approved = True
UNION ALL
SELECT VendorID, Cert2 as CertType, Cert2Date as CertExp
FROM tblVendor
WHERE Approved = True;

Bruce,

Though the help file doesn't show it, you can use expressions in the
Select list, at least with Jet. So you should be able to change the
second SELECT to something like

SELECT VendorID, Cert2 as CertType, Cert2Date as CertExp,
#1/1/1990# as DateReturned, Null as CertRequested

More likely you'll just make both items null, but I showed the date
constant as an example.

Edward

 

[BruceM]

Thanks, that was the missing piece. I supplied False as the value for
Yes/No fields, Null as the value for dates as needed, and everything went
just as it should. I used the union query as the source for an append
query, and the records are neatly in place just where they should be. Now
that you have showed me how this works it seems obvious. Maybe if I was
working on this at some time other than Friday afternoon I would have been
able to guess at what I needed to do and conduct an experiment with just a
few fields. As it is, my Monday got off to a good start. Thanks again, and
thanks to Duane Hookom for pointing me toward a union query.