I have created a database that will be used as a point system, kind of like keeping attendance. A person would get 1 point for calling off from work, or a half point for being late. This will help management when the employee's annual review comes. There is a table with employee details and tables for each group of infractions. Each infraction table has a column for their name (which is pulled from a bound combo box), the date of the infraction, and the type of point (which is also pulled from a bound combo box).
What I need to do is be able to pull reports of an individual that will show all of their points for a year. How should I join the tables or create queries that will include all of this information?
I have attached the database (could not save as 2003).
None of the above. You have an improper table structure for this. You need to store all the points in the same table, once you have that, summing them is trivial.
You shouldn't be storing pertinent information in table or field names. The type of infraction looks to be a key piece of information and you are storing that data in table names. Each infraction is its own table, instead you need an infraction table to list all the infractions and the points they are worth. Then you create another table to hold each specific person's infractions. Link the two tables and sum to get each person's total.
I know reports and forms are the sexy parts of a database, but you really need to work on the table structure to make sure its solid.
I was advised to do just the opposite by someone else on here. He recommended that each infraction group have it's own table.
I'm interested in using your method. Would you be able to elaborate a bit more on the structure of each table? In the first table, do you mean that each type of infraction would have its own row? And in the row the infraction type would be listed and then the points that it is worth next to it?
In the second table, the employee's name, infraction type, and point for each infraction would be recorded in a row?
I would still like to have separate forms for each grouping of occurrences. In other words, I want the user to be able to click on the "Late" button and have that lead right to the Late Occurrence form.
First, table structure drives your database. You don't build the user interface and shoehorn in the tables.
With that said, your user interface can still have a form for each type of infraction. I'm not the best form builder so that question may best be reposted in the Forms section. But I do know you can have a form tied to a specific infraction, then when you enter data into it, it automatically knows which infraction to use as the default for when that form is used.
Before you do this though, think about how infractions are going to be input. Obviously, the user has to choose an employee and an infraction. Which will they choose first? Why not just let them make that decision, then go to a form with a sub-form which lets the choose the other option (infraction type/employee).
I would still like to have separate forms for each grouping of occurrences. In other words, I want the user to be able to click on the "Late" button and have that lead right to the Late Occurrence form.
No. Important for you is to display the information in different ways.
I can't open your DB so, I don't know how you designed your forms but I advice you to start from scratch with new forms.
PS.
Thank you because you "thanked" my post but I am here only because I saw that plog posted something. And he is one of the posters from who I can learn. Not this time because your request was very simple.
Firstly, re table design, Mihail's structure is exactly what I would use. Only minor thing, I'd have the ID field called EmployeeID - one less key stroke
But in so far as form display, I would be strongly inclined to have a series of sub forms, each one on a tab control, with each subform/tab page showing a different infraction. The subforms are each based on a query to limit records to the particular infraction.
This way, you select an employee and you can click between all types of infractions for that employee rather than having to open/close forms.
If you wanted to get real fancy, you could dynamically change the tab page description to include the number of infractions, eg Late (10)
Firstly, re table design, Mihail's structure is exactly what I would use. Only minor thing, I'd have the ID field called EmployeeID - one less key stroke
But in so far as form display, I would be strongly inclined to have a series of sub forms, each one on a tab control, with each subform/tab page showing a different infraction. The subforms are each based on a query to limit records to the particular infraction.
This way, you select an employee and you can click between all types of infractions for that employee rather than having to open/close forms.
If you wanted to get real fancy, you could dynamically change the tab page description to include the number of infractions, eg Late (10)
I like to see if a field/control is the ID. So... ID_ofSomething
But I don't post again for this.
I post in answer to the bold text.
Is not necessary to open/close forms. Should be used a SINGLE form with employers that have a subform with infractions. = TWO forms to design.
PS: @CRONK
One less keystroke to the ID and ... series of subforms... ?!?!? This is not an economic way of thinking
What ???? On my side I see any discussion as a brain storming. Anyone has the right to say what he think and each other has the right to argue against the first one.
Because my English is not as good as I wish to be, I'll prepare a simple DB.
I ended up going a slightly different route. This database needs to be as simplified as possible considering the 2 or 3 users that will be using it have very little experience with Access.
I was able to retain each separate menu. Each menu auto selects the amount of points based on the occurrence-drop down options that the user selects, this is completed through If/ElseIf VBA statements. Only one table for Occurrences is needed and that is tblOccurence. The points table is no longer necessary because the VBA provides the proper amount of points. Last but not least, the selected options get submitted to a table called tblEmployeeOccurrence via the qryEmployeeOccurrence query.
The database has been added. Once again, I was a unable to upload a 2003 version. Can someone please tell me why people should upload a 2003 version? It seems somewhat silly considering that version is 10 years old and is not used nearly as much as the 2007 through 2013 versions. Therefore, they give you quite a few more options than in 2003. Just a thought!
Nope. You're doing the same thing as before--storing relevant information in field names and not in records. Look at Mihail's structure for tblInfractions, that's the structure tblPointsSelect should have.
Instead of a column for each infraction type, you need a row for every infraction type.
Additionally, you don't understand what an autonumber primary key is for. For example in tblEmpDetails you have a field called ID. Why did you put that in your table? Just because somewhere you read you should?
That field is to serve a purpose, and that purpose is to allow other tables a way to link to a specific record in the tblEmpDetails table. But you haven't used that field, you have used the empName field. That is not the field to use.
In tblEmployeeOccurence you should not have an empName field, you need a field to hold the value from the ID field from tblEmpDetails.
Then you have a bunch of tables that don't seem to serve any purpose (tblStOUTOselect, tblYesNo). Because you have so many errors, my suggestion is to start with just 2 tables: tblEmpDetails and tblPointsSelect and get them structurally correct, then add more tables one at a time.
Lastly, I don't know what stats you have about version usage, but I bet 2003 is still widely used (>20%). My 2002 Toyota is still allowed on the road and works fine. That aside, I have 2013 Access and get a PDFAcro.dll error when I try and open your database. It eventually opens, but I get errors with the most recent version of Access.
I'm going to delve into a bit of relevant theory for forms since there seems to be some concern on how to do this. I also want to post a side comment about names.
Employee_ID works OK (as would EmployeeID without the underscore). For this relatively limited application, it would probably never be an issue. But having an underscore in a table name or field name would potentially cause problems if you ever used Access to prototype an app that you eventually up-convert to use an SQL back end server. That is because there is some non-uniformity in the way various SQL vendors treat punctuation in names. Therefore, underscores are a bad habit even though perfectly functional and legal in an Access database.
Now, let's discuss the issue of how you would make a form do something about showing you different infractions - or all of them - for a given employee. This is a case where you need to learn about RELATIONSHIPS (which you can Google if you look for "database relationships" because otherwise you would be equally likely to get advice for the lovelorn.)
Let's say that you have an employee table, an infraction type/definition table, and the junction table to tie a specific infraction event to a specific person. A person has an ID number. That number, to do what I am about to suggest, MUST be the primary key of the person table. An infraction has a code number identifying the type of infraction. To do what I am about to suggest, that code MUST be the primary key of the infraction definition table. The junction table must have fields corresponding to the employee ID and infraction code. For that junction table to work best, you really SHOULD (but notice I didn't say MUST) make a compound primary key comprised of the employee ID and infraction code.
OK, next you must define relationships between tables.
Between the employee table's employee ID and the junction table's employee ID, make a relationship where the join-type is (each employee and only matching entries from the junction table)
Between the infraction definition table's code and the junction table's code, make a relationship where the join-type is (each infraction definition and only matching junction table entries)
IF you do this, THEN you can get the form wizards to help you do some of the detail work.
Now build TWO forms. One will be a continuous form based on employee infractions. That will become a child form. More about this later. Now build the form for your employees that lists one employee at a time. Make a sub-form control, let's say on the bottom half of the parent form. Place the child form into that control WITH THE WIZARDS ENABLED! When you do, the wizard will ask you how to link the two and the answer is "on Employee ID". This works because (A) you have a relationship and (B) you had the wizards in place when you built the form.
OK, let's get advanced. This is what I would do, though it might require you to do some VBA coding. Again, wizards help. Create a combo box and a command button. The order doesn't matter because you will tie them together with code after the fact. Create the command button with the wizards enabled so they will build you a basic infrastructure. Choose the Form Refresh option, knowing that you will be working with that code after the fact and will not leave it as-is.
The wizard will build an OnClick routine. Behind that routine, I would put a logic toggle, a Boolean data type that I complement via a statement as simple as x = not x. Then I would use that code to make the combo box visible or not visible.
The combo box should allow you to choose one of the code types, and if the wizard were enabled when you built that, you could tell it to take the code and description fields from the infraction definition table as the two columns of the table. I would make the code be the "bound" table because that means that would be the value of the combo box. However, I would make the first column have zero width so that the only thing should in the box would be the second column - which is the name/description of the infraction.
You make these work together by making the button toggle whether you have a Filter (it's a form propery) ON or OFF and what value is in the filter.
Put the toggle variable, call it bFiltState, would be declared in the form's declaration area as a Boolean. In the form's OnLoad event code, set this variable to FALSE. Set the subform's filter to FALSE. That way, the subform will initialize to show ALL infractions.
The on-click code might look vaguely like this:
Code:
Private Sub FilterButton_OnClick
bFiltState = not bFiltState
cboInfType.Visible = bFiltState
cboInfType.Enabled = bFiltState
subform.FilterOn = not bFiltState
If not bFiltState Then
subform.Requery
subform.Refresh
End If
End Sub
Now two more parts. The cboInfType combo box can have (should have) a LostFocus routine. Select the combo box in form design view, select the Events properties, and double-click inside the LostFocus box. This will create a lost-focus event shell. In that shell, put
Last part: Base the subform on a QUERY, not a table. In the query grid, you would bring in the infraction definition table and the employee infraction table as well as the individual point value for the code. If you already had that relationship defined, the tables automatically join correctly. You would have columns form code value, infraction points, and a date (which HAS to be in the employee infraction table due to normalization concepts). Forms don't care whether they work on tables or queries. They just want recordsets, and you get that from tables and queries equally.
Here's why this works. The parent form supplies the employee ID to the child form, and the combo-box and command-button alternate between having the subform filtered on the infraction code or not filtered at all. So this form behaves as follows:
If you load the form, it comes up unfiltered and you see all infractions. If you click the button, it enables the combo box and you can select the type of infraction you want to see.
Bells and whistles? You can add DCOUNT and DSUM functions that take the employee ID and selected infraction code into account and just fill in a couple of text boxes on the form showing number of infractions of the selected type and total points for that type. If the filter is off, you don't need the infraction code in the domain aggregates.
Wrinkles to consider? Watch out for cases where any of these counts is zero and take protective action to prevent traps for "no data."
I won't build it for you because if I did, you wouldn't learn anything like debugging and such - but this form will probably give you what you want and look pretty snazzy once you get it figured out.
