Brand new - Help with my tables!

[Yeti]

Hello -



I'm brand new to Access and don't know how do define what I'm trying to do, or I'd look it up

I have a table consisting of an employee ID (primary key) and a bunch of fields related to data regarding the employee.

I have a second table that is simply a one-column list of Employee ID numbers that indicate "the following employees should be regarded as having status X"

I want to add a column titled "Status X" to my first table that simply places a "yes" for employees who are listed in the second table and a "no" otherwise.

How should I go about this?

Thanks!

[Rawb]

If you already have the information in another Table, I'd strongly recommend against adding the information to your main Table. That leads to data duplication and can cause mass confusion later on down the road when you need to update the information (where do you change the information: In the separate table or in the main one? What if you update one but use the other in a Form, Report, or Query somewhere? etc.)

That said, you can do what you're asking. . .

1. Open the main Employees Table and switch it to Design View.
2. Find the first blank row and add the item to it.
3. Enter a Descriptive but short name under Field Name. Try not to include spaces or any characters other than letters or numbers.
4. Set the Data Type to Yes/No.
5. Put whatever Description you feel is appropriate under Description.

If the number of Employees that have this "Status X" is small enough, you can then manually go through and add a check mark to those that need it. If it's longer, you can build a Query that updates the information for you.

Because you're duplicating data by adding this field, I'd suggest you delete the other Table once you're sure all the information has been transferred to the main table.

If you have any questions building a Query to update the data in your Main Table, feel free to ask!

[Yeti]

Thanks, that's extremely helpful and clear.

The plan is to delete the second table once I've updated the primary table - once this is established, I'll be receiving periodic updates that look a lot like the second table - letting me know that certain employees now have received this particular training.

Currently, the second table has a few hundred entries - more than I'd like to check off, so I'll need a query. Future updates should only have a handful of entries at most, so I could check them off, but if I already have a query that updates the primary list, that would be preferable I assume.

Any tips on building that query?

Thanks again!

[Rawb]

Actually, since you'll regularly be receiving a new list of people that have "Status X" you might want to keep the Table.

It's a little early to be thinking about this, but one of the things you can do is set up Access to read your Update Spreadsheet (I'm assuming it's a spreadsheet) and then automatically change the statuses for you.

Anyway, on to the Query!

Since I don't know your Table setup, I'm going to make a few assumptions:

1. Your Employees Table is called Employees.
   
   • Your new field in the Employees Table is called HasStatusX.
   
   
2. Your "This Employee has Status X" Table is called StatusX.
   
   • Your (only) field in StatusX is called HasMe.
   
   

We're going to use the Query Builder for this so you can see a little bit about how it works.

1. Create a new Query by double-clicking the "Create query in Design view" option in the Queries Tab of your database. (fig. 1)
2. Add the Tables you're going to be working with to the Query Builder. (fig. 2)
   
   1. In the Show Tables window that pops up, select the Employees Table and click the Add button.
   2. Then select the StatusX Table and click the Add button.
   3. Close the Show Tables window.
   
   
3. Now we need to link the two Tables together using a common field. Since the HasMe field of the StatusX Table lists the EmployeeID numbers, we'll link those two together.
   
   1. Select the EmployeeID field from the Employees Table and drag it on top of the HasMe field of the StatusX Table. (fig. 3)
   
   
4. Now it's time to set up the Query itself.
5. Select the following fields from your Employees Table and double-click them. This will add them to the list shown below your two Tables.
   
   1. EmployeeID
   2. any fields showing that employee's name
   3. HasStatusX
   
   
6. Next do the same with the HasMe field from the StatusX Table.
7. Run the Query to make sure it shows the correct employees by clicking the Run button. (fig. 4)
8. If the list looks correct, you can go back to the Design View by clicking the View button. (fig. 5)
9. Once you're back in Design View, change the Query Type from Select Query to Update Query.
10. And finally, add the value TRUE to the Update To: field of HasStatusX. (fig. 6)
11. Hit the Run button one more time to update all the appropriate Records in your Employees Table with their status!

See the images attached to this post to see the Figures.

[Yeti]

That's perfect. Thanks so much for your helpfulness and clarity. It makes logical sense to me as well, which is always nice. I'll be doing some more reading and working on my own, but this was a time-sensitive issue.

Thanks!

[Rawb]

Glad I could help!