Using multiple values in a single field

[galvinjaf]

Good Morning,

I'm newer to Access, and a quick study, but am not able to find the answer to my question below.

Attached is a sample table that I'll use when the pay period ends for my employees. We have an attendance incentive program, and I plan to use this to keep track of the program. The table works for me, with the exception that the Date(s) Late column, Holes Filled column, and Reward Pass Issued column can have more than one number or dates. Basically, I can issue several reward passed and would want to capture that data in the single field.

For the Date(s) late column, employees theoretically can be late several times. To make it easier also, I'd like the dates that pop up when you click on the field to show only the previous 14 days (since it is a two week pay period) from the date that I selected in the Pay Period ending.

Can this be done? I assume I would need to use some sort of code, which I honestly don't know much about.

 

[plog]

I don't fully understand what you are trying to accomplish, but I do see where you are going wrong:

I can issue several reward passed and would want to capture that data in the single field

Discrete data needs to be stored discretely. You don't jam a bunch of values into one field (by seperating them with a character) or make a code of some sort to identify multiple values. You create a new table and store each value in a new record.

It sounds like you need a new table which will have a 1-many relationship with the table you currently have.

Also, don't use weird characters in table/field names. Only use alphanumeric and underscores. Remove spaces, parenthesis, etc. from your fields. It will make coding down the line easier.

 

[galvinjaf]

Thanks for your reply.

I went ahead and fixed the column values to reflect text only. I've attached a bigger snapshot of what I'm trying to accomplish. The table I attached earlier, is actually a subform inside of an employee form. Hopefully this helps in describing my goal.

I don't know how'd I'd create another table to still maintain a view such as this. Maybe you can help shed some light? Thanks in advance.

 

[plog]

You would add a button to each row of the subform. Let's say user clicks on row A of the subform. When they do that a new form opens, similar to the one you already have. At the top is the data of row A and then on the form is a subform that allows you to enter multiple rows of data to this new table.

 

[galvinjaf]

What you are describing is exactly what I'd like.

So which table to I edit? My subform table? How do allow buttons to add another row?

I'm editing the tables I have or I'm actually adding a whole new table?

 

[plog]

First, lets stop talking about forms. Let's get your tables structured correctly.

Like I said before I don't fully understand what you are trying to accomplish. Can you please post a screen shot of your relationships and then show some sample data to demonstrate your current issue.

 

[galvinjaf]

Here is a copy of my database.

I want to be able to track reward passes (pieces of paper that I issue to my employees with a special number on them) I can issue multiple passes per pay period. Each row of data in my subform is a payperiod. Each payperiod I write down if they are late, and on which days. At any time I'd be able to check to see which numbered reward passes they have, and what pay period they earned them in.

I believe that my relationships are correct (at least I hope). I don't mean to reference forms, I'm just not up on the particular lingo I 'should' be using.

Thanks again.

 

[plog]

Instead of forms, use tables. That's the only thing we are worried about. Specifically I am looking at your relationships view (Database Tools->Relationships).

It looks screwy. tblemply is a list of all employees correct? What's tablaib holding? And why are they related by tblemply.SUBField to tblaib.ID? Is the First Name fields suppose to match between them?

Answer those questions and also do this: Name your fields better like I stated before (no weird characters/spaces/colons) and fill in the Description column for all your fields in each table. That will help me understand what each field is doing.

 

[galvinjaf]

tblemply is the list of all employees
tablaib is the list of Attendance Incentive Bonus information. (basic payperiod information for each employee, pay period ending date, did they earn reward passes, were they late multiple dates? etc)

Access created that relationship for me via the relationship wizard? Whether I selected the appropriate fields is a good question. I needed to be able to drop down a box and quickly find the employees name so created this relationship.

I've attached my updated database with the changes and descriptions, hopefully you may have some ideas to get me in the right direction.

 

[plog]

Sounds like you are going to need a few tables. You need to think in terms of entities. Employees are an entity, reward passes are entitites, tardies are entities. Pay periods and companies might be an entities. Each entity will be represented in a table. Then, you might need additional tables to sort out the relationship among your entities, but we will get to that.

So far you have one entity table--tblemply (employees). The next one we can build is tardies. What information constitutes a tardy? The tardy date, the employee who was tardy. Anything else?

Lastly, why is pay period special? Why do you need to store when a pay period occurs anywhere? We are going to store the tardy date, the date a reward was issued, the date it was used. What purpose does pay period serve?

Lastly is an aib a reward pass? Try to limit your use of synonyms otherwise you will confuse me.

 

[galvinjaf]

Thanks for taking the time to reply back.

A Tardy represents any time where an employee is late to their working shift for any given day within the pay period. We look up data inside of another system that will tell us if they were tardy or not, I just need to simply put in this database if they were tardy, and on what dates.

Pay period is special because we use some of this data for our HR / Payroll information. While I don't need this database to perform any of those functions, I'd like to be able to run certain reports/queries from time to time on employees.

AIB references wether or not HR/Payroll is going to pay the employee extra for being on time within the two week period. If an employee earns their AIB, they simply make more money per hour for the payperiod that they are in. A reward pass is something an employee hands in if they were late, but still want to earn their AIB or extra money per hour. Almost like a freebee pass for being late.

I know that all sounds confusing, but that doesn't necessarily need to be incorporated. I simply want to keep track of their Attendance Incentive Program.

We are on a paper copy now, and am looking to digitize it. Attached is an example copy of the data I'm attempting to digitize.

 

[plog]

Attached is your database normalized for the data you have. Look it over, ask any questions you have and then let me know if you need other fields added. We can work through making it do all you want.

 

[galvinjaf]

The data in each table makes complete sense to me. My only question is how we take that data in those tables, and get it into some sort of view as I had previously. I assume you'll fill me in.

Thanks for this. It looks good.

 

[plog]

I don't really understand. You are talking about a form correct? You just would replace the subform you have now with one based on WorkActions.