Adding not Appending Data to an Existing Access Table 2

[chard231]

I've slightly modified the Asset Tracking database that comes with Access to track employee "attributes" and employee 'assets'. I am having a problem appending new employee attributes to specific employee data records in my employee table.

Unfortunately, my employee attributes are dribbling in one at a time. One day I'll get SSNs, another, hire date, etc. I can create new lists each time I receive another attribute and import the list into Access. Using a last name, first name match, I've been able to add my unique employee identifier to the new attribute's list. I now want to import that attribute into the appropriate employee table record.

If simply append the data, I get no cross reference to that employees' identifier. In the past I've gone through a set of steps involving multiple queries and ended up creating an entirely new employee list. I'm not comfortable with this process. How can I append the attribute to the appropriate record reflected in the existing employee table?

 

[willir]

Chard231

I can create new lists each time I receive another attribute and import the list into Access ... I now want to import that attribute into the appropriate employee table record.

Sounds messy.

If this is an on-going task, you should create your import queries (done already, I suspect) and coding to automate the process.

Create indexes on the key tables to prevent duplicate entries. For example, create indexes on attributes and assets where this information is stored (NOT on the Employee table -- should be a related table or tables that stores you attributes and assets). use the index to ensure unique entries into the tables.

I also suspect you have many-to-many relationships. An employee can have more than one attribute. Many employees may have the same attribute. (Not sure about "assets" -- is this computer assets, or another way of saying skill set??) With a M:M relationship, you need your "joiner" table or tables.

Now, you can use the import query, but my perfrence, although slower and requires more initial work, would be to create VBA code to read through the import table, and process each record - this employe has these attributes. Write to the joiner table as required.

Richard

 

[chard231]

Thanks for the tips.

One of my employee "assets" is actually their personal brokerage accounts (I work for an investment firm). We are required to evidence monthly review of these accounts on an ongoing basis. I'm using the "maintenance" feature of the asset tracking database to do this. I'm also going to expand the use of this feature by creating new assets representing other types of certifications, training and hoop jumping we are required to monitor. You are correct, it's messy.

All employees share the same attribute types. Everyone has a SSN, a company number, a hire date and so on. Then everyone has several internal system identifiers. We must maintain all of this information on past and present employees. I don't want to create several tables for these attributes, rather I want one record per employee that contains these unchanging attributes. Getting them in to the database is the chore. Of course I could do it by hand.....one employee at a time.. or better, I have a couple of kids working for me!

The employee information comes to me via various company systems. Fortunately they can all export to Excel. Those spreadsheets become the basis of my imports. Transferring that data to the employee sheet "en mass" is my challenge.

Although I'm a heavy Access user, I'm not a programmer. I've learned most of what I know through deconstructing some the databases that come with the program, some online courses and some computer books.

I may re-assemble all of my known attributes and re-import all employee information at once. This feel like work! Thanks again. Richard D.

 

[willir]

So, you need two systems...

OrderTracking of investments
Encountering

I don't want to create several tables for these attributes, rather I want one record per employee that contains these unchanging attributes

...kind of defeats the purpose of a database.

Here is a stab at what you may need.

tblEmployee - includes brokers
EmployeeID - primary key
EmployeeLN - last name
EmployeeFN - first name
SSN
HireDate
TerminationDate
CurrentStatus - text (active, part time, retired...)

tblAsset
AssetCode - primary key
AssetName
AssetType
...etc

tblCustomer
CustomerID - primary key
CustomerLN - last name
CustomerFN - first name
...etc

tblAssetPrice
AssetPriceID - primary key
AssetID - foreign key to tblAsset
PriceDate
AssetPrice

tblCustomerTranscation
TransactionID - primary key
CusomterID - foreign key to tblCustomer
EmployeeID - foreign key to tblEmployee
AssetID - foreign key to tblAsset
AssetPrice - currency, but will use tbleAssetPrice as reference
TransactionDate
TransactionType - text (sell, buy, reinvest, dividend, etc)
TransactionQuantity - negative sell / postive buy

Discussion: Assets will have to include cash, so you can have cash-in and cash-out

tblCustomerBalance
BalanceID - primary key
CustomerID
AssetID
AssetQuantity

...So,
- You have the requied info for your employees, past and current
- You have info on assets
- You can maintain an asset balance profile. You can calculate current value based on the current / most recent price.
- You have your transactions to record who sold what

This may seem like a lot of work, but once the ground work is done, the system should be fairly easy to maintain.

Richard

 

[chard231]

Interesting suggestions but I'm pulling you far afield.

As far as employee accounts are concerned, I'm documenting my physical review of an account statement. That's it. The Asset Tracking database has a nice "maintenance" sub-sheet that I'll use for this.

I'm really interested in a shortcut for importing additions to the employee table. These additional employee attributes will never change. I can't see creating another table for employee phone numbers for example. Is it possible to append to distinct rows in an Access table?

Example: I have 3 employees in my employee table. EmployeeID is my primary key with 3 records (1-3).

I've received and formated an Excel spreadsheet with a corresponding column labeled EmployeeID. The spreadsheet contains a new column labeled TeleNo for employeeIDs 1-3.

I import the sheet. I add a TeleNo column to my employee table. I simple query the new table, change the query to append and run. I end up with six records, the last three consisting of only TeleNos. I do not know how to force Access to append new sheet's Teleno column to the corresponding Teleno column in my employee table.

What I can do is simple query the employee table, add and link the new table using employee ID, add the TeleNo column to the query, run, make sure I get three completed records (important when I'll have many more records), change to a make table query, run and save under a different name, change the name on the original employee table, then change the name on my new table and move on.

Problem is the employee table is referenced in relationships. I have to delete all the relationships first, change table names then re-add them. This gets messy. (Can relationships be re-named, stored elsewhere and restored)?

It seems like there should be another way to do this. Thanks again for your suggestions. I truly appreciate them.

 

[lespaul]

why append instead of update? You have a second table that comes from the imported information which contains the EmployeeID:

UPDATE ActualTable INNER JOIN ImportedTable on ActualTable.EmployeeID = ImportedTable.EmployeeID Set TelephoneNumber = ImportedTable.TelephoneNumber


Leslie

 

[chard231]

Thank you. The update function was what I needed to use. RTD

Richard D.

 

[willir]

Leslie, Your answer is a heck of lot simpler than my tangent ;-) Richard

 

[lespaul]

Thanks! But you're the TipMaster of the Week!! Congrats!

les

 

[willir]

Congrats ?? More like recover from a heart attack -- what a very pleasant surprize. There are a lot of do-gooders out there who are equally deserving. BUT, it made me shine in front of my kids which was worth at least 100+ stars.