I am just starting to use Access and have set up many simple forms, datasheets, reports, summaries, etc. I have not worked with macros, etc. and need help for this project. I have set up a task table
F1 - DateOfCall - date - now()
F2 - Client - text
F3 - DateCompleted - date
F4 - DueDate - date
F5 - problemDescription - memo
F6 - Resolution - memo
I need to run a query and/or report every week summarizing what has been done the past week. Would also like to view previous weeks. I need help. How do I go about doing this.
Hi, we use access for localisation in our application. The business object which accessess the access uses DAO connectivity and we often land to "Too many client tasks" problem.
is it wise to change the DAO to ADO? if so the above problem will go off? what are the chances that the problem still persists?
is that access has any limits on the number of connections? like 64, 255? or is that ADO/DAO has any limitaions on the number of concurrent connections to access DB?
The timetracker that I am building stores the history of the task in multiple rows. Here's a simplified view.
Task ID | Assignor ID | Assignee ID | Description | Status 1 | 5 | 10 | I could not make this change. Please try at your end | assigned 1 | 10 | 2 | I made this change. Please check it. | resolved 1 | 2 | 10 | Please make a few modifications. | assigned 1 | 10 | 2 | Done. | resolved 1 | 10 | 10 | Cool. You're in for a raise! | closed
If i want to select task histories of tasks which are not closed, i.e. the status in the last record is not set to closed, how would i go about doing it? As of now I am doing this through code, where I cycle through the recordset and skip displaying the rows if the task has been closed. But I'm sure there's an easier way of doing this.
I am still confuse after reading threads on producing running sum on queries. help!
This query is to produce a time line graph of Progress Tasks with 3 main categories - New, Completed and Outstanding. So I got months on the x-axis and count on the Y-axis.
sample data. say we look back for jobs in the month in feburary Date Added; Date Completed ; category 11/1 ; NIL ; Outstanding 11/1 ; 1/3 ; Outstanding 11/1 ; 14/2 ; Completed 1/2 ; NIL ; Outstanding 1/2 ; 1/3 ; New and Outstanding 1/2 ; 14/2 ; New and Completed
so the total count of the categories for Feburary New = 2 ,Completed= 2, Outstanding=4
I'm creating a fairly basic case management system in Access 2007 and i'm wanting a way of populating a set of tasks when a case is created...
e.g.
I have a case table, which contains basic information about the case, case manager, ID, date created etc.
I've also got a task table with a list of tasks
When i create a new case I want it to pull in the list of tasks from the task table, the tasks are static so i wouldnt want them to update the task table but update progress on the tasks against the case.
Do i need another table for like casetasks where it populates the blank tasks and then they're updated with an ID linked back to the Case.
I would like to make an action list based on tasks. I try to make a query that does the following things:
-Taken.Priority=1 -show maxium 5 tasks sorted on make date(taken.ingevoerd) -OR with a total duration of maxium 480 minutes (Taken.[Geschatte duur (min))
The last one is to hard for me to make. For example if the duration of 4 tasks is 420 minutes, the fifth task (100 min) should not be shown, because the criteria of maximum amout of minutes is reached.This is what I have made so far:
SELECT DISTINCT TOP 5 Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)] FROM Taken GROUP BY Taken.ID, Taken.Omschrijving, Taken.Ingevoerd, Taken.Deadline, Taken.Priority, Taken.[Geschatte duur (min)] HAVING (((Taken.Priority)=1)) ORDER BY Taken.Ingevoerd DESC;
I have a query, i need to get dsum of total task given to an employee.
I have grouped task_description and select count and it is now showing each employee as 1 which is correct.. i ma looking to get dsum based on this task count but with a criteria that i want to see these tasks sum month a have a month field which i get from assigned date the month field is showing like this december-2013.
Based on this i want to calculate dsum as first to see task count which i mentioned earlier, then employee id which is in query as ID and then month.. this sum will calculate monthly tasks given to each employee.
I have been searching the web for a solution to this problem. I have created a scheduled task on Windows 7 to automatically run a macro from a command line, however, it seems to only run when the user is logged onto the machine. Is there a way to run Access without the interactive logon? I would rather not leave the machine logged in with this account, but if that is the only solution, so be it.
I need the ability to assign employees to a different task in each of four different timeslots on a daily basis. What I would like to do is as I assign an employee to a task in timeframe 1, I would like for them to become unavailable to assign to another task in the same timeframe.
For Example: Three employees to assign to three tasks at the 8 AM to 10 AM time slot.
Employee Task Time Assigned
Emp 1, Emp 2, Emp 3 Task 1 8 AM to 10 AM Emp 2-Task 1
Emp 1, Emp 3 Task 2 8 AM to 10 AM Emp 3-Task 2
Emp1 Task 3 8 AM to 10 AM Emp1-Task 3
It would be nice to set this up in a form where my person doing the scheduling can assign an individual to a task and that individual name disappears from the combo boxes, list boxes, or is "greyed-out" in a listing of employees available for the time period for the remaining assignments.
I'm working on a scheduling system for my fathers company that automates some redundant tasks. Most importantly the system manages the order of processes on different parts (ie, part "tubes" may be cut, then drilled, then welded, then shipped).
I'm trying to create a form that allows for the editing and viewing of all the parts and the processes for each part for a specific work Order. Ideally the information that applies to all the parts (Work order Number, due date etc) would appear at the top of the form with all of the processes to appear beneath (Separated into groups - all the parts to be welded shown together). My problem with using sub forms for this was that I need all the records to be shown without scrolling. Is there a way to dynamically change the size of each Subform to match the required size, or is there a better feature to use?
I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.
I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].
Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?
I'm sure this must have been the subject of a thread before, but after an hour seaching my head aches.
I'm running (Access 2000 on XP Prof) a sort of planning board. The week commencing dates change automatically, as per previous postings on here. I have a job run from a command button to then change a field representing the week the job is to be run in ie week 2 jobs to week 1, week 3 to week 2 and so on. So far so good.
I would like this job kicked off automatically once the first time the db in opened in the new week. Any advice or pointers to appropriate thread welcome.
I'm not really sure what to do on this one, I'd like to hear some suggestions on what everyone thinks..
I have a database where users enter information Bi-Weekly into a form. I have 2 date fields set up, one is called the RecordDate, it is the date at which the user has entered the actual data into the database. Ignore this date.
I have another date, called MainDate, which is the date that all departments need to have submitted the reports (Each department fills out these bi-weekly reports [approx 100 departments]), meaning that one complete set of 100 reports is entered every 2 weeks. Even though they are all entered at various dates within those 2 weeks, usually almost at the end of the 2 weeks, all of the reports will contain this MainDate and it will be the same for all departments every 2 weeks.
I'm looking for the easiest method to accomplish letting a user select these bi-weekly dates and making sure that the dates are correct, validation?.
Some thoughts I had 1) For instance, if typing in the bi-weekly dates, I want it so that they can only type in the dates of every 2 weeks, no other dates will be allowed.
** Best option in my opinion 2) Have a small calendar that expands when clicked right next to the MainDate field so that on the calendar you can only click on dates every 2 weeks, or have every 14 days a different color on the small box for the date. [the users seem to like the ease of using calendars, one of the reaons is is a good option in my opinion]
3) Have a dropdown? That shows the current date that is allowed to be selected based on some sort of 14 day interval cycle. (i.e. after 14 days of one cycle are up, the only option in the dropdown is to click the next MainDate, which will be exactly 14 days from that just passed.
Anyway to have this date automatically filled in after 14 since the last MainDate entry? Bi-Weekly reports are due every second Thursday.
I am creating a booking system and I need to create a query for a report that will show how many bookings are made per week and the total of the bookings. I have managed to make a query that will get the number of bookings and the total, but i have no idea on how to do this for each of the weeks in a month. From this query i hope to then make a report showing 4 weeks at a time.
I try to make my second database in Access. I need to summary 5 numbers, and then minus the lowest and the highest of them. For example (1+2+3+4+5)-1-5=9
In excel it looks like this: =SUM(C2:G2)-MAX(C2:G2)-MIN(C2:G2)
In access (Expression builder) i made this code: [J1]+[J2]+[J3]+[J4]+[J5] - it's the summary of five marks, but how can i erase the highest and the lowest one?
I am looking for the best way to produce a form and/or report that will count how many appointments are scheduled by every tenth of an hour for every hour. Here is what I have for an expression in a query for 1:07 PM - 1:13 PM:
7-13Start: (IIf([Startminute]="7" And [StartHour]="13",1,0)+IIf([Startminute]="8" And [StartHour]="13",1,0)+IIf([Startminute]="9" And [StartHour]="13",1,0)+IIf([Startminute]="10" And [StartHour]="13",1,0)+IIf([Startminute]="11" And [StartHour]="13",1,0)+IIf([Startminute]="12" And [StartHour]="13",1,0))
This works but will take the 288 expressions to get what I need because the requirement is to show schedule start AND schedule end numbers. I am getting tunnel vision and would like to know if anyone has any better ideas.
I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.
I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.
I do not want to use record by record VB because it is slow and I have a million+ records.
Can anybody help ?
Transactions Table (input)
Account Period Amount 1011100 1015 100
Account Summary (desired output)
Account Period Opening Movement Closing 101 10100100 10121000100 10131000100 10141000100 1015100100200
I'm trying to build a database that will calculate the price of meals. I have a table 'ingredients' that has the ingredients, measurements and prices. As prices can change regularly I assumed that inividual recipes should be queries that calculate the total cost on the fly. My problem is that I regularly need to print out a summary of costings that includes total costs for each recipe. From reading various posts I have formed the opinion that I REALLY shouldn't store calculated results in a table. Is there another way to get my summary? Or will I have to resort to being an access pariah?:(
From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:
Field No Records With Records Total Address 10 256 266 CreditREc 5 261 266
I have the detail data in a query working fine in MSAccess 2000 sp3 and I'm trying to create a summary of the data. My query is as follows:
SELECT CUH.CULevel, CUH.AREA, TYPES.Asset, Count(TYPES.Asset) AS Counts FROM (TYPES LEFT JOIN Broker ON TYPES.[Owner/LastLogin] = Broker.LOGIN_ID) LEFT JOIN CUH ON Broker.COSTUNIT = CUH.COST_UNIT WHERE (((TYPES.Asset) In ("T30", "DESKTOP"))) GROUP BY CUH.CULevel, CUH.AREA, TYPES.Asset;
On the attached jpg(s) you will see the query I'm working with. It's fairly simple except for a couple of things and those are the items I'm stuck on.
What I'm trying to do is get a representation of the amount of traffic that has come through our salescenters in the last week. Some centers had no traffic and others had multiple prospects. So when I look at the query for week 33 (this week) I would like to see all the centers even if they had zero traffic - the query should show zero.
If you look at Traffic_Query.jpg you'll see how the table is laid out. With the query I'm trying to show all of our centers for the current week even if no traffic was entered.
I may not be explaining it clearly so if you have any questions let me know.
Thanks, Chester Campbell Joseph Freed and Associates
My next challenge/task is to create a way to record weekly attendance. I have looked at several databases available online but not that really reflect what I need. Also I am trying to do it the simpliest way possible (for now) with my limited knowledge of VBA code.
Here is what I am hoping to do. I have a form linked to a query that will select the students assigned to a faculty member in all the courses that faculty teaches. I would then like to be able to use the same form, and a second combo box, to create records in an AttendanceTable that stores the attendance for that student in that course for that week. I have created a key using StudentID-CourseID to identify each student with each of the course they are enrolled in. It is also used to relate all the involved tables.
I have a form where I display the client's information and a series of checkboxes showing which services have been registered to them (ex. telephone, internet, cell phone (all info is held in separate tables with client id as the foreign key...).
For instance: I have a working query that pulls the client's id from the form to search the Cell Phone Table to see whether the client has a cell phone registered to them or not. How can I get that to translate into the checkbox on a form?
It seems so simple, yet the solution isn't presenting itself.
