Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2020
    Posts
    2

    Update query not working and need advice on building my database

    Hello! I am building a database for work and I wantto make sure I am going about this the right way.

    I have two external data excel rosters (MARFORES IPAC EAS ROSTER AND IPAC PCSPCA ROSTER).


    I need to do the same thing with both, so I will use theIPAC EAS Excel doc as the example.
    The external information that is brought in by the excelroster contains columns such as Last Name, First Name, and EDIPI that Igenerated from another database and saved in the excel doc; I am using theEDIPI as a primary key in the other table. The EAS Roster Tracking Data Table contains columns that have checkboxes to track that documents are complete for each record; that information isto be updated in forms in the Access database. I need to merge the information from both tables and make sure theinformation from the external excel roster updates it as it is updatedexternally.
    I tried to do this in Table 1 which contains all of thecolumns that I need from each of the other tables. The data type all matches.
    The external information updates in the linked exceltables. I ran an append query to get theinformation from the excel table to table 1. That works; however, the update query does not work to update keepupdating the information to Table 1.

    I attached a picture showing the list of my table andqueries and my SQL for my update query.

    I also added a column in the tracking data table with a button to mark "archive" so I can keep records for a while, but move them to an archive. I plan to build a form that connects to Table 1 that will have room for comments. I want to be able to save all of that, but remove it from the current table.

    Is there a different way I should go about building this another way?
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,805
    My 2 cents.......

    I would not use all uppercase - it is considered shouting. (gets tiring on the eyes)
    I would use an autonumber type field for the PK field. (maybe EDIPI_ID_PK)

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique.
    The most common use for an AutoNumber field is as a primary key.


    Some suggestions:
    ------------------
    Use only letters and numbers (exception is the underscore) for object names. (object names include field, table, query, form & report names)
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


    Creating a PK field: Microsoft Access Tables: Primary Key Tips and Techniques



    There are 4 fields that seem to be Date but are set to Short Text...???
    LWAS Start Date/UD# And D.....
    Date Orders Reviewed/Sign........
    Date 214 Reviewed/Dropped........
    Date 11060 Reviewed/FWD To.....



    Without seeing the dB, this is all I can offer.......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    Join Date
    Feb 2020
    Posts
    2
    Hi Steve! Thanks for looking at this! I am a little confused; I only used all caps on a file name. Is my post coming across in all caps?

    I attached the database. Hopefully it goes through.

    The fields that seem like a date do include date, but also need to allows words. Those are for tracking purposes and I am not having an issue with those. My issue is that the information that is updated in the IPAC EAS Roster from the externalfile is not updating in table 1. All ofthe other information is fine. Hopefullythis is more clear.
    Attached Files Attached Files

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,805
    Quote Originally Posted by JEANNINEMFRANZ View Post
    I am a little confused; I only used all caps on a file name. Is my post coming across in all caps?
    Sorry, I should have been clearer. All the field names are in caps. I use camelback - a lot easier to read IMHO.


    I understand a little better after perusing the dB. I searched and found out what EDIPI means
    electronic data interchange personal identifier (EDIPI)
    The EDIPI is a ten-digit number the first 9 digits are assigned unique numbers with the 10th digit being a check digit for the identifier.
    The EDIPI can be used as a unique person identifier.

    ....still struggling to understand some of the other abbreviations.


    Because the EDIPI is a number, I would use a Long Integer data type instead of text, since you are using the EDIPI as the PK. (a Long Integer is a 19 character number: -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 )


    However, I am not understanding the process explained in your first post.
    There are 3 local tables and two linked Excel spreadsheets.
    Do you import the Excel worksheets, EAS and PCS into the respective local tables?
    Is Table1 just a table you are using to test Appending and updating?
    What happens first? What are the steps?


    I created an Excel spreadsheet with I row of data- copied from Table1.
    I changed the data in 2 columns - Rank and Current Leave Balance. I set rank to TSgt and Current Leave Balance to 150.
    I left the name of my spreadsheet the same as yours so I didn't have to change the update query.

    In the query I created "qryEAS_Update", I deleted 1 field
    Code:
    Table1.EDIPI = [MarForRes IPAC EAS Roster for Database].[EDIPI],
    because you would NEVER change the PK field data and I would think you would never change a person's EDIPI.


    I still recommend changing the field names in "EAS_RosterTrackingData" and "PCS_PCARosterTrackingData". (actually, I did change the field names in these two tables)


    In the attached zip is the dB and the Excel spreadsheet the spreadsheet I created.
    Re-link the spreadsheet to the dB. Review the data in Table1, then run the query "qryEAS_Update".
    Compare the data in Table1 (fields Rank and "Current Leave Balance") to the original data in "Copy Of Table1".
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Need Advice re: Building Database
    By musicmanbob in forum Database Design
    Replies: 30
    Last Post: 02-20-2017, 01:35 PM
  2. Replies: 41
    Last Post: 11-04-2016, 11:09 AM
  3. Need advice on building DB for company
    By f15e in forum Access
    Replies: 4
    Last Post: 03-07-2016, 05:00 PM
  4. Replies: 12
    Last Post: 09-18-2015, 02:20 PM
  5. need some advice building DB (noobie)
    By sureshot in forum Access
    Replies: 2
    Last Post: 10-12-2009, 09:49 AM

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 - Senior Forums