APPENDIX B:
FORMULAS
Formula to remove hyphens from SSN.
1. Highlight the SSN column.
2. CLICK Insert/Columns.
3. CLICK Format/Cells/General/OK.
4. Highlight 1st cell under field names in new column.
5. CLICK inside Formula Bar.
6. Type =Mid(insert step 7,1,3)&Mid(insert step 7,5,2)&Mid(insert step 7,8,4)
7. CLICK to the right of the highlighted cell. This will add the cell # to formula.
8. Highlight 1st cell under field names in new column.
9. Drag the bottom right corner of the cell to the last record.
10. Name the new column SSN.
11. Highlight original SSN column.
12. CLICK Edit/Delete.
13.CLICK inside the top left corner to highlight the entire Spreadsheet.
14. CLICK Format/Cells/Text/OK.
15. CLICK File/Save As… rename your spreadsheet.
Update Query for separating Last Name comma First Name. You must first import Last Name comma First Name in the User 10 Field.
Field: Lname
Table: tblDataInputFields
Update To: Left([User10],InStr(1,[User10],”,”,1)-1)
Criteria:
Field: Fname
Table: tblDataInputFields
Update To: Right([User10],Len([User10])-InStr(1,[User10],”,”,1)-1)
Criteria:
Continue to next page…
Field: User10
Table: tblDataInputFields
Update To:
Criteria: Is Not Null
Formula to remove everything right of Decimal (PayPDAmt)
=INT(b2*100) if 123.5645656
=INT(b2+.5) if you wish to round up
1. Create a new column
2. Type the formula in the empty cell using original cell number
3. Edit/Copy original cell
4. Edit/Copy drag down new column
5. Edit/PasteSpecial/Values/OK
MAIL MERGE
1. Open SDSI2000mdb.
2. CLICK Import.
3. Follow steps 1, 2, 3, 4, 5 then STOP!
4. Go to Explorer & double CLICK on Import.mdb.
5. CLICK on Tables Tab.
6. CLICK on tblUWImport.
7. CLICK on Tools.
8. CLICK on Office Links – Merge it with MS Word.
9. CLICK on Create a New Document _ _ _ _ _ / OK.
10. CLICK on File/Page Setup/Margins (Set the Following).
11. Top - .25, Bottom - .25, Left - .25, Right - .25
12. Go to Paper Size Tab/Landscape/OK.
13. Accept “Fix” if Error Message Appears/OK.
Note: You are ready to pull in fields from the database and any verbiage, etc. required.
Pull in fields, make adjustments, add text if needed, and print test sheet & make adjustments. Continue on next page…
If data is printed in the proper positions, proceed as follows:
14. CLICK on Tools.
15. CLICK on Mail Merge.
16. CLICK on #3 “Merge” the data with the document.
17. Merge.
18. Merge to new document.
19. CLICK Merge.
Note: If you are merging multiple print jobs with the same template, you do not have to repeat steps 9 through 13.
UPDATE THE DATABASE (CCID Field)
Open SDSI2000mdb. Software
CLICK Window/SDSI2000Database/Tables.
CLICK File/Get External Data/Import.
Set Files of type to Excel.
Find the Spreadsheet with Employee ID# & CCID/Import.
Show work sheets/Next.
Check First Row Contains Column Headings/Next.
New Table/Next.
Field options/Next.
No Primary Key/Next.
Name It CCID.
Finish/OK.
Note: Now you have a new tbl called CCID in SDSI2000DataBase
CLICK Queries Tab/New/Design View.
CLICK tblDataInputFields/Add.
CLICK tblCCID/Add/Close.
Link Employee ID#. (Left CLICK and drag one to the other)
Double Click on CCID in tblDataInputField Table.
CLICK Query/Update Query
Update to: CCID.[CCID]
Query/Run.
Note: Open tblDataInputField to verify CCID is now there.
LINK TABLES TO SERVER
1. Open Windows Explorer.
2. Highlight "SDSI 2006" folder.
3. Copy Data.mdb to server location.
4. Open SDSI.mdb.
5. CLICK Window/SDSI Database.
6. CLICK Tools/Database Utilities/Linked Table Manager
7. Click select all then deselect the following: tblExcelImportData, tblLogo, tblUWImport.
8. Check "Always Prompt For New Location"
9. Click Link Tables.
10. Navigate to the location of the data on the server, double click on the data.mdb file