If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append from main table to related table
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. |
#2
|
|||
|
|||
Append from main table to related table
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 |
#3
|
|||
|
|||
Append from main table to related table
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. "Edward Reid" wrote in message oups.com... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Confussed as to which control to reference subform | melwester | Using Forms | 5 | June 30th, 2005 06:49 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Duplicate data | Rob Green | Database Design | 3 | November 7th, 2004 03:08 AM |