Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9

    How to update multiple values in a field

    Hi all,



    I am a conservation ecologist specializing in bats.
    I have been plugging away at Access for data management for >20 years, but not a power user.
    Please bear with me.

    I have decided it is time to become better informed and learn more details on Access use, so will need a bit of hand holding, nose wiping etc.

    I am running into DB size issues approaching the 2 GB limit and discovered one table had an extrodinary large number of duplicate entries. I could never get "delete dups" to work w/o removing all of the data from any tbl.

    So I have started over for this one tbl and reimported the basic data (using Excel to remove the dupes first). Long convoluted explanation of working in a GIS to extract spatial data distribution data and exporting as Excel tbls, so will not detail that.

    I have BU copies of the original data generated a few years back with all the dupes as well.
    What I am dealing with is a tbl in a DB of bat data including all New World distribution occurrences by country and what ever administrative units are used, states, districts, provinces etc.
    Click image for larger version. 

Name:	Dist_tbl.png 
Views:	21 
Size:	99.7 KB 
ID:	29584

    Each occurrence is documented via a RefID that is linked to a tbl that lists published references for each known speceis. Rather than have hundreds/thousands of duplicate entries each with a different RefID number I assume I can simply add multiple values in this field, correct? It is a simple TEXT field.

    For example thousands of records have a RefID of 41, however there are many records for the same species and location that also are verified by additional references e.g., 42, 85 etc.

    So my question to all of you Access gurus is how can I use the existing data saved with the original tbl and only have the addtional RefID values appended to the new tbl? The link woudl be on the SPEC_CODE (a unique identifier for each species) and unique location codes [ADMIN_code].

    It also occurs to me that I can/should remove all the redundant information [FAMILY, Subfamily, Species] in this tbl as it is already linked to the a Bat_Names table as well as the distribution information [Admin_name, Cntry_code, Cntry_name] as these are linked to the ADMIN_NAMES tbl to via the ADMIN_code field.

    Hopefully not too many typos...


    Cheers,

    Bat Dude
    Attached Thumbnails Attached Thumbnails Dist_tbl.png  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I agree you should remove duplicates of data stored in other tables as you have indicated

    to only add non duplicated data you can do two things

    1. is to create a multifield index for SPEC_CODE and Admin_Code and set it to no duplicates (in table design, on the ribbon, click on the indexes button. add a multifield index by giving it a name in the index name column (e.g. idxUnique) and in the field name column, select the two fields. Then click in the index name again and bottom left, make the Unique Value=Yes

    2. If you are appending data to the table using a query, use a left jon and is null criteria - something like

    Code:
    INSERT INTO myTable (fld1, fld2, fl3.....)
    SELECT source.fld1, source.fld2.....
    FROM Source LEFT JOIN myTable ON Source.SPEC_CODE =myTable.SPEC_CODE AND Source.ADMIN_code=myTable.ADMIN_code
    WHERE myTable.SPEC_Code IS Null

  3. #3
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9

    Tnx

    Tnx for the reply Ajax,

    The multipfieldneeds to tbe the RefID... there are duplicates that shoudl be there for both SPEC_CODE and Admin_codes but perhpas not linked together.

    I assume your suggestion on an append query will work on the the RefID field.

    I will look at that.
    Tnx again

    Bat Dude

    Quote Originally Posted by Ajax View Post
    I agree you should remove duplicates of data stored in other tables as you have indicated

    to only add non duplicated data you can do two things

    1. is to create a multifield index for SPEC_CODE and Admin_Code and set it to no duplicates (in table design, on the ribbon, click on the indexes button. add a multifield index by giving it a name in the index name column (e.g. idxUnique) and in the field name column, select the two fields. Then click in the index name again and bottom left, make the Unique Value=Yes

    2. If you are appending data to the table using a query, use a left jon and is null criteria - something like

    Code:
    INSERT INTO myTable (fld1, fld2, fl3.....)
    SELECT source.fld1, source.fld2.....
    FROM Source LEFT JOIN myTable ON Source.SPEC_CODE =myTable.SPEC_CODE AND Source.ADMIN_code=myTable.ADMIN_code
    WHERE myTable.SPEC_Code IS Null

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It needs to be whatever is supposed to be unique

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Quote Originally Posted by Bat Dude View Post
    Rather than have hundreds/thousands of duplicate entries each with a different RefID number I assume I can simply add multiple values in this field, correct? It is a simple TEXT field.
    You can, but don't advise the use of multi-value field. Before choosing to use, should have thorough understanding of what MVFs are and issues that accompany them. Review https://support.office.com/en-us/art...C-6DE9BEBBEC31

    Access is the only db platform with this functionality and can make migrating to another (such as SQLServer, MySQL, Oracle) frustrating.

    Compiling the multiple RefID values from your current structure into an MVF can possibly be done with SQL, but simply as comma separated string will require VBA.

    Instead, build a conventional parent/child table relationship as already suggested by Ajax, which is what the MVF does hidden in the background.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9
    Tnx for the warning about multi-value fields. I was actually thinking of something like a simple comma delimited string for that field. however I am clueless on VBA coding.
    Going through and manually adding in a thousand or more records would be very tedious. ;-)

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I would take the list of references (RefID) out of the new_distributions table altogether, and make a separate table of references (call it tblRefs). new_distributions will have a one-to-many relationship with tblRefs, and might look like this:

    tblRefs

    Ref_ID (PK, could be autonumber)
    Dist_ID (FK to new_Distributions) (I strongly suggest you NOT use Index as a field name - it is a reserved word in Access)
    Doc_ID (FK to the table of references)
    ... other fields you may want

    You can put a unique index on Dist_ID + Doc_ID to prevent duplicates.

    Doing this will put your data into "proper" database structure. New_distributions to References is a many-to-many relationship, requiring a "junction table", which tblRefs is.
    It also makes modifying the reference data a whole lot easier - to add a reference, all you need to do is add a record to tblRefs, and delete a record to remove a reference.

    Searching and querying are greatly simplified as well.

    Just as an aside - how many records are looking at for new_distribution and the References table? Unless you have hundreds of thousands of record in each I don't think you should be approaching the 2GB limit. Dis you do a compact-and-repair?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Well, CSV string may be worse than MVF. CSV doesn't even emulate a normalized structure, at least MVF does.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9
    Tnx John,

    I will see what I can do with your suggestion. At the moment the distribution table is linked to the table SourceRef that only has 2 fields the RefID is set as the key value with no duplicates and a simple text field Citation.
    I assume I can modify a copy of this table to create what you are suggesting?

    On the DB size - currently the Master copy of my DB is 1.937611 GB in size.
    Click image for larger version. 

Name:	size.png 
Views:	17 
Size:	16.6 KB 
ID:	29589

    The distribution table is only the first part of what I am trying to address. I have read one can link large tables to other Access DB to get around this size issue.
    Although on trying that and linking external tables in a testing version the final size, even after compact/repair does not change appreciably.

    This includes the largest DB of New World bat acoustic call data ever assembled and includes > 25 years of field recording data so I am only "Tweaking" portions of this on copies. :-)

    This DB also includes a huge amount of VBA code I had a person help with over the past 15 years, but he is not available at the moment.
    The actual call data is stroed on a 4 TB external RAID drive. Fortuanlty only 82% full.

  10. #10
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9
    As a side note I installed MS Office 2010 64-bit version however, none of the internal VBA code developed would work in the 64-bit Access so I had to uninstall and re-install the 32-bit version of Office.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Bat Dude View Post
    As a side note I installed MS Office 2010 64-bit version however, none of the internal VBA code developed would work in the 64-bit Access so I had to uninstall and re-install the 32-bit version of Office.
    Probably you did not need to do that. There is a fix for this, but I think it's premature to get into it before you solve your size issue. Do report back on what happened after doing a compact/repair. AFAIK, whether a table contains many, many characters per field (as if it were csv data) or if they're separate records, the effect on the table/db size would be the same, notwithstanding any overhead resulting from complex designs, huge defined relationships, etc. Hopefully, you are not also storing objects/images in your tables.

    You also cannot 100% believe what windows reports as a file size, believe it or not. Sometimes, a file is spread over so many drive sectors that the file occupies more space than it would if the drive was defragged. This usually isn't the case with network drives though.
    Last edited by Micron; 07-23-2017 at 04:41 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I will see what I can do with your suggestion. At the moment the distribution table is linked to the table SourceRef that only has 2 fields the RefID is set as the key value with no duplicates and a simple text field Citation.
    I assume I can modify a copy of this table to create what you are suggesting?
    The SourceRef does not need to be modified. Instead of linking to it from the Distribution table, you would link to it from the new tblRef table (Doc_ID in post #7 above).

    One Distribution has many SourceRef (1-to-many)
    One SourceRef is referred to by many Distribution (also 1-to-many)

    Those two together give you the many-to-many relationship.

    Now, one difficulty in what I am suggesting below is that for one "set" of duplicates in the existing new_distribution table, there is no ONE field that can be used to identify all records in that set. You need the combination of SpecCode + Admin code - two fields. However, that poses no great problem. for the moment, you could change the structure of the junction table to:

    tblRefs

    Dist_Ref_ID (PK, could be autonumber)
    Admin_Code )(these two are a composite FK to new_Distributions)
    Spec_Code )
    RefID (FK to the table of references)
    ... other fields you may want

    Populating the new junction table tbfRefs may not be as daunting as it sounds, because I think you already have the required data on the table that contains all the duplicates (of I understand correctly, the duplicates are due to the multiple references?).

    A simple Append query might do the trick - Admin_Code, Spec_Code and RefID from each record will be a new record in tblRefs (you should change RefID to Numeric if it is not too late)

    Populating a new version of the Distribution table could probably be done with an Append query as well. If all the fields in a set of "duplicates" are the same, except for Index and RefID, you would make a query containing all fields except those two from the current Distribution. Then make the query a totals query, with Group By on all fields. That should result in one record for each unique SpecCode + Admin Code combination. If that works, then use that query as an Append query to a new, unduplicated table (which could have it's own Autonumber PK)

    If this long-winded explanation works, all that would remain would be to replace the two-field FK in tblRefs with a single-field one. Again, it could be done with queries.

    PS: Are the fields CSVRow and CSVColumn used in the Access database? If not, you could delete them - in a really big database, every little bit can help.

  13. #13
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9
    Hi John,

    I just saw your new explanation. Tnx again for your time. I understand a bit more now what you are referring to however, not sure I understand how to do this. Below is a link below to a very stripped down version with what I tried to create as the tblRefs but clearly did not get it.

    I have never worked with one-to-many links before so should learn how to do this.
    So the simple/sample version is in DropBox if you could kindly look at this and perhaps configure the tbls(s) and links I can see how it is supposed to work and try creating the same in the main DB.


    https://www.dropbox.com/s/c2m83ytgmu...ple.accdb?dl=0

    FYI the CSV row & column fields are needed, but would easily be created with a query based the new format the new distribution tables that I have removed all of the redundant information that already is included in other tables.

    Cheers,
    Bat Dude

  14. #14
    Bat Dude is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    9
    Morning John_G,

    After more coffee this AM (05:30) I began working on this issue a bit. I was able to remove the two CSV related fields as they will be generated by a query when needed as well as removing all of the redundant data that is already linked to the Admin_code table by the unique Admin_code for details on locations by country and administrative unit, as well as the bat species identification that, is linked by the unique SPEC_Code.

    I have never looked at or used calculated values in a table before, but was easy (perhaps deceptively so?) to create this as a unique value in the distribution (test) table. Currently, duplicates are still set to OK as there are many records with the same distribution ID (new calculated value but have different Ref_IDs linked to the small table of references. This can be seen when the filter for that column is opened in the screen capture of the table below.

    I will need to update distributions and adding a lot of related references once I get this part of the DB working to track known bat distributions. I am now still pondering how to create the structure for the linking of the other table structure you suggested.

    Click image for larger version. 

Name:	Design_view.png 
Views:	10 
Size:	57.6 KB 
ID:	29617
    Click image for larger version. 

Name:	New_dist_tbl.png 
Views:	10 
Size:	84.1 KB 
ID:	29618

  15. #15
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Thanks for the further information. I downloaded your Sample database to work with - and found there are very few duplications of Admin Code + Spec code - only 8 actually - but that's OK, it's enough to work with.

    Attached is a zipped copy of the Sample database with the changes I made.

    First I made a copy of Old Distributions (structure only) and called it New Distributions
    I deleted fields RefID, CSVRow and CSVCol from that new table.
    I changed Citation in tblRefs to Numeric - Long, to match the type in SourceRef
    I renamed some fields in tblRefs to match those in New Distributions (just to reduce the chance or error in a query)
    I deleted all records in tblRefs
    Did a compact and repair to reset the Autonumbers in new and empty tables


    You will see there are 3 new queries:

    Q1 Populate_tblRefs re-populates the table tblRefs. For the moment the Dist_ID is set to 0 in all records (606). Nulls and Zeros (there was one zero) are ignored
    Q2 Populate New Distributions populates the [New Distributions] table - 8 fewer records than in [Old Distributions]
    Q3 Update tblRefs with New ID adds the Dist_ID values to tblRefs, using [New Distributions] as the source

    Redesign Q2 Populate New Distributions to include all your fields, and run them in that order.

    Let us know how it goes.

    Cheers - John
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update a field with multiple values
    By sizemoreg in forum Queries
    Replies: 4
    Last Post: 11-09-2015, 03:37 PM
  2. Find and update multiple values
    By chemthethriller in forum Forms
    Replies: 3
    Last Post: 04-22-2014, 01:15 PM
  3. Replies: 5
    Last Post: 02-20-2013, 03:21 PM
  4. Replacing many values at once in a multiple values checkbox field.
    By ConfusedDatabaseDesigner in forum Forms
    Replies: 2
    Last Post: 07-20-2012, 08:58 AM
  5. Update Query selecting multiple values in one field
    By Zipster1967 in forum Queries
    Replies: 1
    Last Post: 08-15-2011, 12:22 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