Ok, I'm missing something simple here I'm sure, but I can't see the wood for the trees at the moment.
background guff
What I have is a system tracking actions being undertaken. There's an SLA for these actions which means they should be completed within 10 days. At the moment we have no reporting on whether or not we're meeting this SLA.
Now obtaining the information for all the data this year is fine and dandy and works ok. Where I'm having problems is doing a monthly breakdown for the ytd.
I'm using the following query to give me my raw data:
SELECT [Parent Table].ID, [Parent Table].[Date entered into database],
[Parent Table].[Leave Date], [Parent Table].DateCompleted,
DateDiff("d",[leave date],[datecompleted]) AS DaysToCompleteFromLeaving,
DateDiff("d",[date entered into database],[Datecompleted]) AS DaysToCompleteFromEntered,
DatePart("m",[leave date]) AS [month]
FROM [Parent Table]
WHERE ((([Parent Table].[Date entered into database])>#1/1/2007#)
AND (([Parent Table].[Leave Date])>#1/1/2007#)
AND (([Parent Table].DateCompleted) Is Not Null));
Which gives me the various dates, the number of days it took to complete the record from when a person left the company ,from when their information was loaded into the database and finally a number for the month.
I have a query that happily gives me the average completion times on a monthly basis:
SELECT Avg(CInt([DaysToCompleteFromLeaving])) AS AvDaysFromLeaving,
Avg(CInt([DaysToCompleteFromEntered])) AS AVDaysFromEntered,
[Completed Leaver Dates].month
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month;
but I seem to be having a great deal of difficulty specifying criteria on a Count to show me the same breakdown.
I can get a total count of records per month:
SELECT [Completed Leaver Dates].month,
Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month;
But what I want to do is split that number into two columns, records where the completion date was >10 days and records where the completion date was <= 10 days which is where I'm having some problems.
Putting a critera in design view for the count field still returns the total number of records per month and returns the following SQL query:
SELECT [Completed Leaver Dates].month, Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month
HAVING (((Count([Completed Leaver Dates].DaysToCompleteFromLeaving))>10));
I'm fairly sure it's in the HAVING clause, but I'm not sure what I'm missing.
I want to make a church database grouping families together and identifying each persons position in the family while still having each family member with an individual profile, i.e. birthdays, anniversaries, position, classes, memos, possibly donor info.
Hey, I dont know much about Access but i need a little help with my queries.
I need to make it so that if a specific tick box was ticked, a number would be counted. This will be the case with many many tick boxes. What wud I put as the criteria. Exmaple: I wanted it so that every time I clicked a specific tick box (made with a yes/no) the number 2 was added to every other tick box that was selected (but those tick boxes may be a different number). It would be counted all up to a particular number.
I hope you get what I mean. It's difficult to explain.
I've been trying to use a sum in a query to add up the currency of several records in a field from another table but as yet have had no success. The sum simply shows all the records but on there own and not in one total :confused:
I also need to use a count to total up the number of records in a table with a certain piece of data, in this case a 1, but again it does the same as the totals and simply shows each record on its own
If anyone knows what I am doing wrong I would greatly appreciate some help and advice
Hi All I need to be able to do a count before and after the current date. My table has fields named month and year and I need to find the count before and after the current month. Any help would be much appreciated. ChrisD
Hi all I have a problem trying to create a query to count 2 dates in one query. I have recorded dates for the return of forms from specific types of vehicles, the first date is the date the form is for, the second is the date I received it. I have a query that returns the count of the forms received per vehicle type. I have tried adding a second field in this query to count the days where the date for is within "x" days of the date received. However when I try to run the query I get an error saying that the query has a field that is not part of an aggregate function. Is it possible to create this as 1 query or do I need multiple queries to get a result.
How come when I try to count 2 differ fields in a query they become equal numbers even though they shouldn't be? Can you count multiple fields in one query?
Alright, completly new user to access here and this forum has been a great resource for me the last 4 days thanks.
Heres my issue. Im trying to create a report based off of fields ( in progress, wins, and losses) I want to be able to add up each instance of a win loss or in progress sum them up totally and then find ratio's of each
I really have no experience using access or count functions...I know its like math, but the wording (typing the logic out) baffles me.
Such as wins over total or loss ratio losses over total. The problem is I dont know how to create a query, or do it in the report that will let me add them up carry them over to a new field and especially do calculations...
I've been playing with the expression creator* googling like crazy and checking out this forum My data looks like this
StatusID Status 1 Loss 1 Loss 1 Loss 2 In Progress 3 Win 3 Win 2 Win
and I would like it to look like this Total Projects Win Ratio Loss Ratio In progress Ratio (Sum) Wins Over Total Losses over Total (ditto)
Is there a way to do this in one query? Or create a few different ones, Or even do it straight in a report?
And what the heck does the formula look like? Are there special Functions that do this or is like like Field A + Field B = Field C?
This might be a bit difficult to explain, but i will give it my best shot. I need queries to give me the count in various fields, but with specific thresholds within the particular field, and with a criteria. So for example, I have an age field calculated. Now i created a new query to give me the "count" for how many people are between age 1-10, 11-20, 21-29, and so on. I did this by putting in coresponding criterias such as "between 1 and 10" in each field. However, because all thresholds (which are my fields) in that query are using the same calculated fields (age, from another query), only one criteria is actively working. Two or more fields using the same "age" field just returns whatever my first criteria is over and over. So if i make a field "1-10" with it's criteria, then the fields "11-20" with ITS corresponding criteria, both fields just end up giving me the count of the first criteria. Am i doing something wrong? Is there an easier way to do this? Also once i can get this working, there's another condition i need to implement, which is: count of "1-10" for "2006", all in that query. then another query for the count of the ages thresholds in 2007, and so on. Any help is greatly appreciated! Thanks.
So i'm not sure where this post belongs as it may encompass several items. I have an excel sheet where every row has an email address and a product purchased. I've imported it all into access but would like to run a query to find out how many people that bought x purchased y. Or tell me how many people that x did not buy a,b, or c.
Part of the problem is that each record is one product. My data looks like this:
I was able to group some of the data by email address in access but can't figure out a good way to query it. I'm thinking I probably need to merge the data where each unique email address is a record and the products have all the products purchased instead of just one but I really have no clue how to do that.
Is this easily do-able? how would I go about this? Thanks alot!
name, project, total_Hours_worked (only if total is >45) for a week by each employees. the query should contain total hours for all the weeks. The table contains week_ID column and dyas worked column. i can create a query which gives me sum of day_hours worked using selecct sum(day_hours) as Total.
but i badly need total sum of week hours worked by each employees. so that i can generate overtime report grouped on weekly who has woked more than 45 hours per week.
the table is attached. please see and help me if you can.
Need some help, please. I'm writing a simple report that needs to show individuals and the number of times that each individual has been designated the author and/or owner of a document. The two tables in the query (simplified) are: Person, with columns personID (PK) and personName; Document with columns docID (PK), authorID and ownerID. Each report line needs to show one line per person, with the ID, name, count of authorID and ownerID (showing the number of times he/she was designated the author and/or owner of one or more documents). For example: ID ... Name ..........Author ... Owner 1 .... John Smith .... 0 ......... 3 2 .... Mary Smith .... 2 ......... 0 3 .... Peter Smith ... 1 ......... 2
I need to create a query to retrieve one row per person, then do some kind of subselect (?) to count the number of matches for Person.personID against Document.authorID and Document.ownerID. I'm having all kinds of problems in what I thought would be a simple SQL statement. Can't find anything out there, so all suggestions are welcome.
I have an Employee Table with 3000 records. There are 3 records person employee. Now I was able to 'Group' the 3 records per employee into one by running a query. In this query, I used a "Count" function and Access gave me a result of 3000. HOWEVER, I do NOT want a count of 3000, b/c in reality, I have only 1000 employees total. What would I have to add to the query to get the correct count?
I have a telephone database with call detail records for every call. In my criteria selection, I am selecting only inbound (CallType 2), and (outbound CallType 3) call records. I need to count the total number of records in each CallType. I have very little experience using a gui type database management software like Access, (I use to use DB2 on an AS400). I am trying to create a variable like you would in VB and use it in Access put can't figure out how to do that.
Little background on the database project: In our customer service center, I need a report that displays the total number calls for each customer service rep, inbound, and outbound calls, for a specific date range. CallType 1 = intercompany calls (which I want to exclude), CallType 2=inbound CallType 3= outbound.
I have a roster of people where each person may work on a specific task during a given period (a month, or a quarter, etc.) and each person is assigned to a team.
Team Jones has 30 people on it. There are 5 tasks that a person on that team may or may not work on during Q1 of 2013. Here are those tasks:
1. Business Analysis 2. Project Planning 3. Testing 4. Test Planning and Preparation 5. Quality Inspection
Now, what I want to do is to find the unique instances for each task where 1 or all of the 30 people worked on that task for the quarter. To further illustrate, say for Business Analysis that Mary Smith did Business Analysis in January, February, and March. She performed that task 3 times (1x in each month) but I am only concerned about knowing that she did perform Business Analysis during the quarter (so I'd like to show a 1 instead of 3).
Ideally, if everyone on Team Jones did Business Analysis during the quarter at least one time, I am only concerned about the one instance where they did and the first occurrence of it, not the other occurrences. So if everyone did do Business Analysis for the quarter, my total count should not exceed the count of members on the team (in this case, the 30 people). So the distribution could look like this (I'm just making this up but this is what I'd like to see):
Task Team Jan Feb Mar Total
Business Analysis Jones 7 15 8 30
My fields in the table are: 1. Task 2. User 3. Date 4. Team (there are 5 different teams)
I use MS Graph and need show 2 data types: 1. Invoice Value, EUR 2. Date Date axis group records by month, for Example Period from 2005.01.01 And 2006.01.01 has 12 positions in Date Axis. Her I have problem: I need show sum of Values for every month. For example in Period from 2005.01.01 till 2005.02.01 I have 3 Values: 200, 500, 600EUR. Her I need to show 1300EUR, and so every month. Her is my strandart code:
Dim strSQL1 As String strSQL1 = "SELECT Date As Date, Value FROM Table WHERE (Date BETWEEN 2005.01.01 And 2006.01.01)" Me!chrStatistik.RowSource = strSQL1
OK, first time posting so I'll try to be clear here. I work for a special education agency and have created a database to track student/therapist information. Now, I have reports showing which kids are in which schools and who their therapists are. What I can't figure out is how to print a report, grouped by school, that will display which therapists are active in that school.... any ideas?
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid
Example of data:
AK001 | A Brown | Blue Car |1000 | 1/4/2013 | Yes | 20/4/2013 AK001 | A Brown | Red Car |2000 | 1/4/2013 | Yes | 20/4/2013 AK001 | A Brown | Yellow Car |500 | 1/4/2013 | Yes | 20/4/2013 AK002 | A Brown | Black Car |1000 | 7/4/2013 | Yes | 20/4/2013 AK003 | B Smith | Blue Car |1000 | 12/4/2013 | Yes | 25/4/2013
I want to create a report from this table that outputs as:
Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid
Example of report from Example Data:
AK001 | A Brown |3500 | 1/4/2013 | Yes | 20/4/2013 AK002 | A Brown |1000 | 7/4/2013 | Yes | 20/4/2013 AK003 | B Smith |1000 | 12/4/2013 | Yes | 25/4/2013
Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?
I'm creating a database of companies. Every company has at least one field of services they provide. There are three main groups which contain all the services (Let's say I have three tables).
What I need is to have a form/table (query?) where I can assign to a company any number of services from any group. Is it possible to do it in a form where I could simply add a service by choosing a group first and then a proper service, and then add more in the same way?
I have a report that I have called "0 Master". It details the number of complaints that my company has received, broken down by a specific service.
I have a CountIIf that returns the number of complaints received in a certain category. This works - but this only shows the total number of complaints received for that specific category.
That formula is =Count(IIf([Contact Category]="Stage 1 Corporate Complaint",1))
I have also got a formula that tells me the total number of those complaints that are outside of the deadline (regardless of category).
This formula works and is; =Count(IIf([Date Response Expected]>=Date(),1))
But - I want to combine these 2 formulas to tell me the number of complaints outside the deadline for a particular category - I've had a go myself and anything I do tends to return either nothing or -1
So far, I've tried various incarnations of; =Count(IIf([Contact Category]="Stage 1 Corporate Complaint",1)) And (IIf([Date Response Expected]>=Date(),1))
I am in the process of converting a very large, multi-page Excel spreadsheet into a Access Database.
This database is going to be used to quickly look up and update members of our building's emergency response team, their location, training, etc.
In my main db list I have things like last name, first name, the building the work in, the floor they work on, and what training certs they currently posses.
One of the pages on the spreadsheet has a floor by floor count of personnel. Example - tower #1 - floor 1 = 2 people, Tower 1 - floor 2 = 6 people, Tower 3 - Floor 3 = 4 people.
In the database table, I have a column named "Building" and a column named "Floor". Is there a way to run a query that would show all of the various counts on one page. Meaning, if I have 10 floors, a 60 people, can I have access tell me what floors have what number of people on them, if so, how do I do it?
