Query To Get Unique Counts
Feb 13, 2013
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)
Is this doable in a query?
View Replies
ADVERTISEMENT
Jun 11, 2007
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?
View 4 Replies
View Related
Oct 15, 2007
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?
Thanks
View 1 Replies
View Related
Feb 11, 2008
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.
View 7 Replies
View Related
Mar 14, 2008
Hi. I am trying to do a query on a table that has 3 columns
ID, Person1, Person2
I want to query the data to find out how many rows each person's name appears in either Person1 or Person2.
I don't need seperate counts for when each name appears in Person1 or Person2, just the total for each person.
Result example that I'm looking for:
If table contained:
1 Eddie Sam
2 Pete Max
3 Sam Pete
4 Dave Eddie
Query to look like:
PersonsName Total
Eddie 2
Sam 2
Pete 2
Max 1
Dave 1
Surely this is easy to do?
Anyone? Thanks. Martin
View 6 Replies
View Related
Dec 14, 2012
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?
View 1 Replies
View Related
Mar 4, 2015
I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.
I have attached a screenshot.
View 4 Replies
View Related
Sep 4, 2013
I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.
Currently my query displays all the users who made errors, and the total errors they made. But I don't want the total - I want to break this number down by the types of errors that are made.
I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is "Type Error" and a couple of examples of error types are "A1" and "B1"
Code:
SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors]
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User
ORDER BY [Errors Table].User;
I've seen it done with multiple queries joined together, but I'd like to avoid that if possible...
View 5 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Jul 8, 2005
I’m starting to wonder how I ever made anything work, before I found this place.
I built 2 queries and put them in a query
They share a common field
The join type is all record from Query1 and Matching from Query2
Next I added a count field for each
Then I added a calculated field that subtracts the count from Query1 to Query2.
So far it all works
When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.
View 2 Replies
View Related
May 1, 2006
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.
Hope you can help
Thanx
8am81
View 4 Replies
View Related
May 6, 2006
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
Thankyou
View 4 Replies
View Related
Dec 19, 2006
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
View 2 Replies
View Related
May 21, 2007
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.
Thanks
Craig
View 2 Replies
View Related
Oct 27, 2014
How could I set up a numbering system where you have a main number and then have a choice of a sub number? i.e.:
Main = 1
Sub = 1.01
But still be able to move on to 2. Would I need a routine to ask if the next number is a sub number?
View 8 Replies
View Related
Sep 24, 2005
I've been on other forums with this problem but no one can solve my problem.
I've created a database with 3 tables that are linked:
- tbl_customer
- tbl_rates
- tbl_destination
They have the following fields:
tbl_customer
- cust_id
- customer_code
- first_name
- last_name
- company
tbl_rates
- rates_id
- cust_id
- rates_id
- currency
- pre_rate
- cur_rate
- fut_rate
- comment
- eff_from
tbl_destination
- dest_id
- destination
- destination_code
What I've done is created a form based on the tbl_customer table and used tbl_rates as a sub form. tbl_destination is used to populate a combo box that is situated in the sub form.
Each customer has their own rate sheet. Each rate sheet has different destinations and rates. Rates for the same destination are constantly changing. I need to use this spreadsheet to record the history of every change made to the rates of a particular destination. To do this I've just added the same destination with a new rate.
Now, what I want to do is to create a query that will only show the latest entry of a particular entry.
Eg. tbl_rate
Rate_id - Dest_id - currency - pre_rate - cur_rate - comment - eff_from
1 - 1 - £ - 0.01 - 0.03 - increase - 19 Sept 05
2 - 2 - £ - 0.12 - 0.14 - increase - 19 Sept 05
3 - 1 - £ - 0.03 - 0.02 - decrease - 23 Sept 05
I would want the query to show me the following from the above table:
Rate_id - Dest_id - currency - pre_rate - cur_rate - comment - eff_from
2 - 2 - £ - 0.12 - 0.14 - increase - 19 Sept 05
3 - 1 - £ - 0.03 - 0.02 - decrease - 23 Sept 05
Basically, the query should only show the latest entry which involves Dest_id '1' because there were two entries with the same id.
I've been given advice about this issue but none that actually work.
I would be grateful for any help on this matter.
View 1 Replies
View Related
Feb 21, 2007
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.
Thank you.
View 2 Replies
View Related
Mar 12, 2007
Hi,
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?
Thank you.
Joe
View 9 Replies
View Related
Jun 12, 2007
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.
View 1 Replies
View Related
Feb 6, 2008
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.
View 1 Replies
View Related
Aug 22, 2004
i was wondering how to go about doing this, i currently have a query which returns all the payments due in the next month, going to a report which acts as an invoice. I was wondering how to create a unique id for each invoice and store the last id so that i can automatically itterate it later
View 4 Replies
View Related
Nov 15, 2011
I have a query of 11 employees and their pay for jobs done. Only problem is there are 15 completed jobs with some of the employees doing more than one. When I run my query its only displaying unique name results so I'm only getting 11 results of the 15 jobs instead of all 15.How would I change the query to that the results are only unique employee names?
View 1 Replies
View Related
Oct 31, 2014
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))
View 2 Replies
View Related
Nov 29, 2005
I don't know if I'm going about this problem correctly. In a nutshell:
I have a table which contains a number of fields, only a two of which are important for this task, as indicated below.
| ID | Type |
Where ID is an alpha-numeric identifier (say a store number), and there are three potential types, plus Null values. For this, we'll assume the three types are Fruit, Vegetable, and Grain (plus Null).
An example dataset might look like:
0-4 Vegetable
1-2
0-1 Grain
1-2 Fruit
0-4 Vegetable
1-2 Vegetable
0-1
0-2 Fruit
0-4 Grain
1-2 Fruit
0-4
0-1 Vegetable
0-3 Fruit
If I want to know how many stores have Vegetable, I could put "Vegetable" as the criteria in the query and set UniqueValues to Yes. Then I would get three unique values, one for each Vegetable existing at a unique store.
But, what if I want to know at which stores Fruit exists and Vegetable DOES NOT. Is there a way within Access to find all the records with "Fruit" as the criteria and then eliminate those with ID numbers matching the dataset with "Vegetable" as the criteria?
In other words, if I do my "Vegetable" criteria I end up with the resulting dataset:
0-4 Vegetable
1-2 Vegetable
0-1 Vegetable
If I then use "Fruit" as my criteria, I get the following dataset:
1-2 Fruit
0-2 Fruit
0-3 Fruit
Since Store 1-2 has Vegetable, I want to eliminate it from the list so that my resultant list is:
0-2 Fruit
0-3 Fruit
Is that possible?
Sorry for the long bizarre example, but I thought it the easiest way to make the situation clear. I'd really appreciate any suggestions anyone has on dealing with this situation!!
Thanks
View 2 Replies
View Related
Jun 1, 2006
I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.
SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;
qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.
(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)
Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.
Any help is greatly appreciated. Thanks.
View 4 Replies
View Related
May 14, 2007
Hi, I have a query with approximately 100 criteria. That is a bit much, thus I have made a table of the criteria and in the query a relation laid to this table. The criteria are countries with an increased risk on tbc. I point these query on a column with parent 1 and afterwards on the column with parent 2. Then I want the results from both query's in a table, but without double rows. I have tried this with a merge query but these only give the results where the hits for parent 1 and parent 2 are the same. This is by far not always the case, so, I miss all hits where there would be a hit for one parent only. Cumbersome tale, but perhaps there is someone who understands what I mean?
View 4 Replies
View Related
