Originally Posted by
JEANNINEMFRANZ
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".