Results 1 to 5 of 5
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    After Update to Insert Into statement problems

    PersonTest - Copy.zip

    HI all,
    I ran into a problem with my AddressFrm and don't know how to resolve it. I believe I need an Insert Into statement but not sure?
    IN an attempt not to save duplicate files in tables, I made some changes to how to store the CSZ stuff, such as city, state, zip, and county to just saving the CSZID but ran into an issue.



    So, when I go into my AddressFrm, and I update the City, Note: some cities have mutipul names for same zipcode and they are set by a checkbox called primary and is set by a class module. Once I enter a zipcode it auto populates the City, State and County. Example, if I enter 51334 Estherville, IA Emmett comes up. Well, 51334 has an additional city called Gruver, and if I select from combo, gruver, then it wont let me change back. So that is problem one. I assume that is due to problem 2 maybe? I need to update my AddressTbl with the CSZID depending on the selection of the City so it puts the correct ID in for the CSZID into that table. I am not sure just how to create such an update or even if that is what I actually need. The zipcode comes from the ZipCodeTable, I tried using the CSZTbl but that gave me a dropdown with mutipul zips of the same number. I don't think this is real complicated but neverless, I have been working on it all day today and no success in figuring it out. Please help....

    I have attached a mock up db and had to remove a lot of zipcodes and CSZ,s from the tables to get it small enough to upload so zip codes are limited.

    Thanks
    Dave

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dave,
    I think the issue revolves around:
    Note: some cities have mutipul names for same zipcode and they are set by a checkbox called primary and is set by a class module.

    This seems to be the underlying fact:
    A zip code may relate to 1 or many City(s)

    So entering a(single) Zip code does not identify a single City in all cases.
    It appears your structure does not deal with this business fact.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Yes, it works if i put the Zip, City, State, and County in the address table but if i try to just put in the CSZiD then i have issues. Just didnt want to store all that two times or more and want to share addresses by combo.
    thanks

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm. Not sure what to suggest. But here are a few things to consider.

    Some city names exist in more than 1 state. So City by itself is not unique
    Some zip codes contain multiple municipalities. So Zip code by itself is not unique

    You may need to identify location in a hierarchical fashion.
    State-->City-->Zip (State and City should give unique zip; but Zip will not give unique City State as you have found)
    OR

    Zip-->then State, then Cities to resolve duplicate (states and/or Cities)

    If you go with your CSZiD approach, it seems you will need to store records with a specific CSZiD. And since CSZiD is local to you, it doesn't seem useful to others. But that may be ok depending on the scope of the database.

    Can you describe who will use this, for what exactly? Even provide an example and run through the steps to capture/record data; and to search for data and possibly to identify and update data.

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi and Thanks,
    I didn't think it would be that complicated. Some Zipcoeds have more than one city. A Primary City and Secondary(s) cities. If you choose like 51334 which is Estherville, IA then the CboCity should also show Gruver, IA as that town shares the same zipcode. If I take the zipcode from the CSZID, then it puts several of the same Zipcodes in it by the city. Basically its not filtering the city, its filtering the ZipCodes. The basic function of this is when I fill in a zipcode when adding an address, then it auto fills in the city, state, and county and if the zipcode has more than one city you can select it from the CboCity. What I am trying to do is get it to put the CSZID into a table AddressTbl, when it is updated. So when I select a zipcode, it puts in the rest of the info, then I want it to put in the CSZID into the other table, if I change the City from the filtered drop down list, then it updates the CSZID.

    It worked just fine as long as I was putting the Zip, City, State, County into the actual table however that duplicates info I all ready have so I made some changes to this method in hopes of just storing the CSZID in that table. I can then add an address from a combo when I use the same address for other people and companies. If I have an address that I all ready put in, I can combo it for like my wife. Of if its somewhere I am staying, I can copy that address by combo to a responding company. That also will not allow duplicate records. If my wife and I share the same of the same addresses, or my brother and his wife, if I add them into my db, I can just type in the address and its all there. If this makes any sense.

    I don't know why its reacting this way with the city combo, but has something to do with not being stored directly into a table. If I cant figure it out I will revert back and accept that I have to duplicate info. I think there is a way to do this but just don't know. I paid a hundred bucks for that code module so wanting to use it. There should be a way for it to work and then insert the actual CSZID of that zip into the table?
    Thanks
    Dave

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-10-2019, 10:40 PM
  2. Replies: 3
    Last Post: 08-17-2015, 03:06 PM
  3. Replies: 6
    Last Post: 08-17-2015, 02:22 PM
  4. Replies: 1
    Last Post: 10-29-2014, 10:19 AM
  5. Replies: 2
    Last Post: 03-07-2014, 09:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums