Pulling totals from query's Total row into form's textbox

[funwithaccess]

Hi everyone!

I am back with another question!

The new database that I am building will be used as a point system based on attendance. This means that I will have a main form that a few users will utilize to look up an employee's score. So far, I have a combobox (cmbEmployeeDropDown) that lists the query for each employee using the following:

Row Source:

SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Flags]=0 ORDER BY [Name];

Row Source Type: Table/Query

From this combobox selection I would like the totals from the Total row in the query to appear in textboxes on the form (txtTotalPoints, for example).

Is this possible to do or is there a better way to do this?

Please let me know if any of you experts have an idea!

Thanks,
Nate

 

[pbaldy]

Are you saying that you have separate queries for each person? If so, not typically a good idea. I'd have a single query that looked at a form for the employee criteria.

 

[funwithaccess]

Hi Paul,

Would you be able to elaborate on that? I would like to do this in the most efficient manner. Otherwise, I was planning on a separate query for each employee with 365 records for each calendar date. In each record/row there would be the option of inputting a 1 representing the point for each field (1 point for being late, 1 point for returning late from lunch in a separate field, and so on). This is proving to be quite tedious.

Thank you for your interest!

Nate

 

[David R]

Do they only get 'points' for occurrences (being late, etc)? If so you don't need 365 records, you just make a record every time they ARE late. Call it tableBeingLate.

You already have a single Employee table, right? Does it have a Primary Key (identifier... maybe their Employee ID, or username/login, or whatever)? If so all tablebeingLate has to have is three fields (plus a PK, of course):
EmplID
DateOfOccurrence (Datetime field, formatted short date)
TypeOfOccurrence ("Late from lunch", "Late to arrive" etc - this can reference a tiny lookup table if you want to make adding categories/changing points easier later)

Then all you need do is run a query on that Employee's entries in tablebeingLate over a certain time frame... and your combobox doesn't have to contain 117 queries, just 117 EmplID's and Employee Names!

Stop thinking of Access like a spreadsheet that is WIDE. Relational databases want data that is in rows, not columns. Google 'normalization' and you'll probably see 17 ways to make your database work better.

 

[pbaldy]

You don't want separate queries. Getting a new employee shouldn't require you as the developer to do anything, and that would. You want a parameter query. You probably have:

SELECT...
FROM...
WHERE EmployeeID = 1

with a different one for each employee. What you want is:

SELECT...
FROM...
WHERE EmployeeID = Forms!FormName.ComboName

replacing FormName and ComboName with the appropriate names. The combo would list employees, so the query would run for whatever employee was selected.

For similar reasons I wouldn't have fields for each reason. I'd probably have table of reasons (late, late from lunch, etc). I'd have a form where I chose the employee, the reason and entered the date. If I was late for work today plus came back late from lunch, I'd have 2 records for today. That's a more normalized structure.

 

[pbaldy]

This is what happens when the phone rings while you're typing.

 

[funwithaccess]

You guys sold me on it! I'm scrapping the calendar format and utilizing the "date of occurrence" method. My experience with relationships and joining relies heavily on already created tables and queries so I'm sure that I will have questions on that subject.

Thank you both for your thorough replies!

Nate

 

[pbaldy]

No problem; I think you'll be happier in the long run.

 

[funwithaccess]

I have 6 small tables with 2 to 3 fields that are related occurrence-types in each table (they have quite a few point criteria). What would you guys recommend for the primary key? This is where I get a bit confused. I'll need to link all of these tables in the event that they want reports ran on an employee and they want all of the data in one report.

EDIT: The key can be the employee ID or name but I was wondering if I will run into the issue of duplicates causing an error since every record added will add the employee's ID or name again.

Also, they'd like to be able to click on a name in a combo box on a form and have a total points text box display the total amount of occurrence points for that employee. Would that be a parameter query that I would use?

Thanks again,
Nate

 

[David R]

tableBeingLate or whatever you ended up calling it should just have an Autonumber PK. All that does is ensure each record can be referred to uniquely... the EmplID and date and type do the work needed by your reports. EmplID and (probably) OccurrenceType are what are called Foreign Keys - they link to the PK in another table, but in this one they can be duplicated, obviously.

Yes you can tally points on the fly. DSum() is probably going to be what you're after...hopefully they gave you a date range or something so you're not pulling data from 2007??

 

[funwithaccess]

Hi David,

Thank you for that info! This point system won't be implemented until 1/1/14 so the date range will be easy to start off. How should I setup the form for the user to input the data? In other words, would you suggest the textboxes and comboboxes be tied directly to the tables or should I do an append query? I'm also trying to figure out how all of the tables will tie together when I need to pull the data into a report for the management/employee. I hope that this makes some sense. I'm all kinds of confused as you can probably tell! Haha

Thanks again!!
Nate

 

[David R]

Always bind your controls directly to a table/query if you can. APPEND queries take longer to run, are more prone to error and harder to debug.

What I would do is just have a small continuous form with three controls side by side:

1. Combobox that is BOUND to tableBeingLate.EmplID, but has RowSource of a query based on tableEmployees. You only need to show the first and last name (first column 0" width), three columns total.
2. Text box bound to your datetime field - leave enough room on the right edge for the datepicker. You may or may not want to set the default value to Date().
3. Combobox that is BOUND to tableBeingLate.OccurrenceType, but has RowSource to your tableOccurrenceTypes lookup table. It's up to you whether a tiny table like this has a meaningful PK or not (maybe your timeclocks or HR policy has codes, like our office has DCK for Docked time), but the same deal applies as with your first combo... index column is hidden, show the user the meaningful text.

That's really it. You can put an automatic filter on it to only show existing records from the last 7 days or whatever, or put a header on that lets you filter it by an arbitratry date range, but that's all gravy, make sure your fields work first. Bonus points if your database has some other form that already shows your Employees and you can just click a button that says 'open OccurrenceForm for this employee, and autofill the first column with their EmplID for me'.

 

[funwithaccess]

David,

First, thank you for your assistance as I work this out.

I have gone ahead and created a second form that will open upon clicking a button next to the employee name combo box in the main form. This second form opens to the Employee Occurrence Form and the first text box on the this form autofills the employee name from the selected name on the main form (a third form is available via a 2nd button on the main form that opens to the selected employee's work info that is pulling from a table called tblEmpDetails). Also on the occurrence form is a text box with a date picker and all of the possible point text boxes. However, I have not linked the points text boxes to their corresponding tables/fields.

I have attached the database (I could not save it as 2003) to this post in the hopes that you can take a look at it and see if the structure works well and how you would recommend adding the points to a new record in the appropriate places.

Thank you for all of your help!

Nate

 

[spikepl]

Ages ago I saw an Italian cartoon. In the episode, the heroes worked in a factory. To keep employees working rather than goofing off in the loos, a device moved back and forth over the long line of toilets and would occasionally randomly zap a toilet cubicle with 10,000 volts. An automatic sweeper would sweep out the remains, if any.

Now why did I come to think of this

 

[funwithaccess]

Ages ago I saw an Italian cartoon. In the episode, the heroes worked in a factory. To keep employees working rather than goofing off in the loos, a device moved back and forth over the long line of toilets and would occasionally randomly zap a toilet cubicle with 10,000 volts. An automatic sweeper would sweep out the remains, if any.

Now why did I come to think of this

Haha! I like that!