SELECT For Retrieving Counts
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 Replies
ADVERTISEMENT
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
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
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
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
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
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
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 3 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
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
Aug 26, 2014
I know how to use the count statement that counts vertically, how can I count horizontally (only if the value is greater than 0) ?
CustID 1st 2nd 3rd 4th 5th 6th 7th Count
2233 7 3 0 0 2 8 0 4
5730 3 0 0 0 1 0 6 3
7590 0 0 0 4 0 0 1 2
I need to capture the value in the 'Count' column (as shown above).
View 14 Replies
View Related
Nov 6, 2013
Once a year we have a conference that has about 400 people attend. These people will participate in several classes, randomly, over a four day period. Each class hands out an evaluation form. This form has six categories and asks you to rate each from 1 to 5. This is done with a grid on the form labeled with 1 2 3 4 5 across the top and the attendees will put a checkmark or X in the appropriate box. This results in approximately 3000 evaluation sheets.
I will take these and manually enter the scores into a database I created in order to tally the results and generate reports. I have been asked to see if this process can be expedited by using a scanner to pull the scores from the sheets.
View 1 Replies
View Related
Aug 9, 2014
I am new to using access but have managed to build a database that I use to find qualifiers for horse racing based on stats I import for all the racers in the days racing. I have a query which shows the date, time, track, horse and trainer but some races have more than 1 selection so I want to add a field that counts the number of qualifiers per race. I am finding it hard to do a countif, date AND time & track are all the same within that query.
I also want to add another show the total runners in the race but this will have to look into the tables where the query is run from. Hopefully I can figure this out when I find out how to solve my original problem.
View 5 Replies
View Related
Nov 15, 2014
I have six fields in a record that look like Median.University1, Median.University2, Median.University3, Median. University4, Median.University5,Median.Unive rsity6. How can I create a calculated field that counts where values equals 4 in the fields . I have tried Dcount and Count with no success.
View 7 Replies
View Related
Nov 13, 2007
Hi,
I am new to access and need help trying to retreive data. Basically I have a unique ID field (123545). I what a user via a form to be able to retreive data using this unqiue ID. Does anyone know the best way to achieve this in access please?
Thanks
Ket
View 1 Replies
View Related
Feb 5, 2008
I have an Access2003 database that contains a table. The table has 2 fields. One is a counter and the other stores a picture which is datatype ole object. I want to do a one time export of the pictures. I want to save the pictures as jpegs in a designated directory. I know very little about working with ole object datatypes. Can someone tell me the easiest way to do this?
View 1 Replies
View Related
Nov 5, 2006
Hello All,
Is there a way to retrieve the very last entry to a table (via a query) without passing a value to the query.
Lets say I have a table Pets
ID desc
==============
1 Dog
2 Cat
3 Lizard
For example lizard was added last, is there a way I could pull just this out using a query? (keep in mind that I don't know wahat the last entry is, so I cannot pass any kind of value to the query)
Cheers,
Aaron
View 2 Replies
View Related
Feb 9, 2014
I have a a table 'Orders' with fields (Order Number, Order Date, CD Number, Card Number).
I would like to produce a query in access 2010 that would allow me to count how many times the CD Number 'Diab190617' has been purchased.
I would like to store the results of this count and counts on other cds numbers somewhere so that I can produce a graph/chart of these counts. How can I do this?
View 4 Replies
View Related
