Show all records in a table, one click to Update All? 1

[firebirdta84]

I have a table called EMPL_STATS with 30 records in it - each record contains two fields - [EMPL_NAME] and [NUM_COMPLETED]. In a seperate table (tbl_All_Pending_Cases), I have an evergrowing list of records - each record is an "assignment" for the employee. This table is updated via a form. When an assignment is marked complete on the form, the field [ASSIGN_COMPLETE] changes to a "Yes" value and a field [COMPLETE_DATE] is populated with the date of completion. tbl_All_Pending_Cases has a field called [EMPLOYEE] which contains the employee name. This Here's what I need:

A form called frm_Analysts_Eval with two textboxes ([txtStartDate] and [txtEndDate]) so the user can enter a date range. I want to display all 30 records from EMPL_STATS in this form at the same time. When I click a button, I want it to populate each [NUM_COMPLETED] field with the number of assignments that are assigned to that person and marked complete. Below is some code that someone on this board helped me with, but I guess I would somehow need to remove "John Doe" and replace it continuously with a new employee's name:

DCount("*", "[tbl_All_Pending_Cases]", "[Partner] = 'John Doe' AND [COMPLETE_DATE] Between #" & Format([Forms]![frm_Analysts_Eval]![txtStartDate],"yyyy/mm/dd") & "# And #" & Format([Forms]![frm_Analysts_Eval]![txtEndDate],"yyyy/mm/dd") & "#")

I am fairly new to Access so any help will be appreciated!

Joe

 

[lespaul]

So you basically want to see the number of records for each partner (is this the same field as EMPL_NAME in the EMPL_STATS table?) in the tblAllPendingCases table where the complete date between what the user has selected?

Do you need to update this count back to the EMPL_STATS table or do you just want to see how many? Is COMPLETE_DATE actually a date field or is it a text field with a string that looks like a date?


This query will return the information that you are looking for (I think), but it doesn't have anything to do with your EMPL_STATS table....

SELECT Partner, COUNT(*) FROM tblAllPendingCases WHERE COMPLETE_DATE BETWEEN [Forms]![frm_Analysts_Eval]![txtStartDate] and [Forms]![frm_Analysts_Eval]![txtEndDate]

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[firebirdta84]

That's right...and this count should update in the EMPL_STATS table everytime the button is pressed. COMPLETE_DATE is a date field in the tbl_Pending_All_Cases.

EMPL_NAME in the EMPL_STATS table and EMPLOYEE in the tbl_Pending_All_Cases contain the same employee names.

Thanks,
Joe

 

[lespaul]

So if the user puts in a date range of 1/1/2007 - 1/31/2007 and you get those numbers and insert them in the STATS table and then the user puts in a date range of 1/1/2007 - 6/30/2007 and you want to now insert those numbers in the STATS table....Why don't you just run the query and show those results? why the update to the STATS table?

 

[firebirdta84]

You're right...I don't need the STATS table...

I tried putting in the code you posted above but I get the error "You tried to execute a query that does not include the specified expression 'Partner' as part of an aggregate function". Any ideas?

Thanks,
Joe

 

[firebirdta84]

Not sure if I made this clear, but I need to basically generate a report that lists out all 30 employees and beside it, shows the number of assignments that they have pending (basically, looking at tbl_All_Pending_Cases, seeing which records have their name in the Partner field, and where the COMPLETE_DATE is between two user entered values.

Thanks.

 

[lespaul]

my bad, too early and forgot the GROUP BY:

SELECT Partner, COUNT(*) FROM tblAllPendingCases WHERE COMPLETE_DATE BETWEEN [Forms]![frm_Analysts_Eval]![txtStartDate] and [Forms]![frm_Analysts_Eval]![txtEndDate] GROUP BY Partner

 

[firebirdta84]

You, my friend, are awesome. Thank you for your help! That worked great.

Joe

 

[lespaul]

You are very welcome!

Thanks for the star too!

have a great weekend!

Les