Adding a Field Updates Multiple Tables

[RRowbottom]

I am currently in the process of converting several (25+) Spreadsheet based lists into one Access Database for easier additon/removal of employees.

Each Employee record contains many columns of information, so many that I have had to continue the data on a second table, with a Unique ID Field the link between the two tables. I have formed a One-One Relationship between the tables using the ID Field.

For use by Supervisors that will be updating certain areas of data, I have created forms that look similar to our old Excel Spreadsheets. I was happy to see that adding a name to these forms would add the name to the original table, and the same with removing a name.

Now that I have created a second table, I am hoping there is a way that adding a name to any form will add the name to two or more tables. My goal is to have the Supervisors use only the forms to input data, while all of the tables that store the information remained hidden so that there are no accidental errors that corrupt the entire database.

Thank you for any assistance you can provide. My knowledge of Access is somewhat new, so bare with me. I have already been using the forums hear to solve several problems I have come across.

Regards,
Ryan Rowbottom

 

[plog]

Each Employee record contains many columns of information

This is but one of the many signs that you probably don't have your database properly structured. Numerated field names (week1, week2, week3, etc.) is another, so are Specifically-named fields (DavidPercentage, MikeHours, NovemberData, etc.). Any of your columns named like that?

I am hoping there is a way that adding a name to any form will add the name to two or more tables

Another sign that your data isn't properly structured is adding duplicate data in multiple places. You said you had an ID field in your tables that linked them, if so, there is no reason to store the name in multiple places, you simply link your tables and find the name in the one table it appears.

If you'd like to post your tables and their fields we could help you structure them properly.

 

[RRowbottom]

You said you had an ID field in your tables that linked them, if so, there is no reason to store the name in multiple places, you simply link your tables and find the name in the one table it appears.

I should have clarified that. I do not need the Name to be added to each Table, just the Unique ID Number. As of now, that Unique ID Number is added (a new row is created) on only the original table. I have to manual add it to the others.

I have attached a screenshot of the Primary Table. The Columns consist of Currency values for activities in each month of the year. At the end of each month there is a Monthly Total Caculated value. At the end of all the months there is a Yearly Total Caculated value.

If there was a better way to upload an example of this, please let me know.

 

[CraigDolphin]

I think Plog is 100% correct in his diagnosis but I doubt that screenshot has the information required to advise you. Almost every field in that table has the same name. It's very hard to know what your database table structure should be without knowing what it is attempting to model in the real world.

So, here's what I suggest. Forget completely about the issue you were trying to solve in your first post. That's a symptom, not the underlying disease.

Instead, try to describe (at a general level) what it is that your database is supposed to track, and what you want the database to do for you.

You mention employees, supervisors, and your screenshot mentions volunteers. Your table contains many columns of dollar values for the month of January, by 'employee'.

Perhaps this database is meant to keep track of payroll for people, and you additionally want to track people and their employment status?

Once we understand the overall purpose and scope of the database, people will be able to make more useful suggestions about table structure, and know more specifically what questions to ask of you to help figure out your particular situation.

I strongly recommend that you do some searching for tutorials or articles on the topic of data/table normalization. These will point you in the right direction.

[Edit]

And for illustrative purposes here's an example.

You want to track expenses for employees?
Consider something like:

Table: Employee
Fields:
EmployeeID (pk, autonumber)
FirstName (text)
LastName (text)

Table: ExpenseTypes
Fields:
ExpenseTypeID (pk, auto)
ExpenseType (text)

Table: EmployeeExpenses
Fields:
EmployeeExpenseID (pk, auto)
ExpenseDate (date)
EmployeeID (integer ->foreign key)
ExpenseTypeID (integer->foreign key)
ExpenseAmount (Single)

Every expense is a row in the EmplyeeExpenses table: not a column in a table.

To get monthly expenses you sum all the rows where the expense date is within the required month using a parameter query.

To get monthly expenses for a particular employee, you use a parameter query that sets appropriate criteria for both date and emplyeeid.

To get monthly expenses of a particular expense type for a particular employee, you use a parameter query that sets appropriate criteria for date, type, and employeeId.

You never have to add fields as time goes by, you never have to add tables with new expense data either.

If you don't care about expense type, you'd eliminate that table and the foreign key field in the expenses table.

The point here is that tables should almost always be tall and narrow (few fields, many rows), not short and wide (many, many fields). And data elements should almost never be repeated (other than foreign key values).

Hope it helps.

 

[RRowbottom]

Starting from the beginning is best.

We are a combination Paid/Volunteer Emergency Squad. Currently we track various information about our employees through a number of Microsoft Excel Spreadsheets. Different individuals within the organization are responsible for certain spreadsheets.

*Side Note: Tackling the idea of permissions to certain forms or tables is currently not a priority for me, nor is it necessary.

The hassle with the current system is that in order to add a New Member or Remove one, we have to add/delete the member from over 25 different spreadsheets.

Since each Member has only one piece of data assigned to them in each spreadsheet, my thought process was to basically combine all of these spreadsheets into one Access Database. The organization or asthetic appeal of the Tables is of no concern to me. I have created a system of Forms/Reports to basically mimic our old spreadsheets and ease the transition. Some of the individuals who are inputting the data are not very computer savy, and if I can "trick" them into thinking they are still using something very similar to Excel, that is best.

As one example, we have a spreadsheet file entitled "Volutneer Incentive." This spreadsheet keeps track of the Financial Incentive program for all of our members. This file is divided into 13 Sheets, 12 for each month of the year, and a Total sheet. The monthly sheets contain columns for each category that money can be earned in. The Total sheet at the end uses basic forumlas to add all of these values up. The individual in charge of this simply puts the money value in each category each month, and totals are created automatically.

When you see all of those similar field names, they are actually broken down into "January - Incentive - Training", "January - Incentive - Position", "January - Incentive - Years of Service", etc. Like I said, the "look" of these tables is not important to me, they are simply in the background.

In addition to having 12 months worth of those above titles (about 8-10 per month), there are also similiar listings by month for a different investment account that each members has. Also, I was hoping to include Personell information, training information, etc.

I understand that this probably looks insane for experienced Access Developers, but in truth, the system was working perfectly for me until I ran into the 255 Field limit on the tables.

With one table, and the forms I created to "mimic" the old spreadsheets, I was able to add and delete members from one form, and they would be added/deleted from the Table. Now with the second table, I can no longer perform this over multiple tables. This basically nulifies the entire point of my operation, which was to allow our people to only have to add/remove a person once. Also, I want to keep them in the form interface. Each form has a companion report for printing.

I appreciate all of the help offered. I was finding solutions through other posts on this forum until now. I hope my idea is still possible.

EDIT: Realize now that the title of this is very misleading. I do not want to add a "Field" to two different tables at once. I want to be able to add a new member to a form and have that member appear across all tables.

 

[CraigDolphin]

I had a little time to kill so threw this together as a very rough working example as to how the database can be made to store the data in a normalized design, while still giving you the ability to calculate your totals by month, by incentive type, and in total very easily.

The same concepts used in the totals tab of the form can be used to create printable reports or whatever you might need.

I'm not saying this design is exactly what you need as I'm sure there is a lot of other stuff to consider that I am unaware of. But it demonstrates that it isn't all that difficult to use (enter incentives into the details tab for each squad member), and then you can view the totals in the totals tab.

Note that I have organized the data entry (forms) by squad member then having the user input the incentive type, date, and amount. I could equally organize it by incentive type instead, and have the user add squad member, date, and value.

Either way works.

And depending on how things work in the real world, it may be that you would want to tweak the data model used in this example.

But have a look at it, read up on data normalization, and see if it triggers your mind in a different direction.