jwcolby
jwcolby at colbyconsulting.com
Wed Mar 12 09:52:54 CDT 2008
Charlotte, I could add an autonumber PK but then I would have to do it all over again when the update comes in. Additionally it would be likely to be used as a FK somewhere and then when the new update comes in (with more / different Zips) the PKs would point to the wrong zip etc. Not a good idea. I implemented the county subform idea and that works. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Wednesday, March 12, 2008 10:39 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Query becomes uneditable I think you'r stuck if you use that table because it has to have a PK to be updateable. One non-updateable table in a query makes the whole thing not updateable. Any way to give that wretched table an autonumber or something? Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, March 12, 2008 6:52 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Query becomes uneditable I have a fairly simple query, with a people table, with outer joins to tlkpState on stateID etc. It is editable as it sits. I need to provide a county population of the county for the zip code in the address. I have a table of zip code information which includes this information, however the zip codes are not unique, i.e. there are potentially multiple rows for a given zip, for reasons like the name of suburbs inside of a "town" etc. Because the zips are not unique, the table has a "primary" field which holds a "P" in the field for the "primary" zip record. Don't look at me like that, I did not do this!!! This table does not have a PK of any kind, probably because it would be extremely difficult to discover a natural key, and an autonumber is less than useful anyway. To make matters worse, we get an update to this table periodically. I have a query that pulls a single record per zip based on a "P"rimary field which holds that "P" in that field for exactly one record for each zip. If I join that query to my updateable query on the zip, my query becomes non-updateable. If I join the table itself on the zip all hell breaks loose as you might imagine. Is there an answer to my quandary? Is there a way to get a zip / county name out of this table, joined to my query while still having the query updateable? The county field should not be updateable anyway. I think I could just put a subform in that displays the county but I am just wondering if there is a "cleaner" way. John W. Colby Colby Consulting www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com