Hi All, I am stuck, and can't get my head around an easy way to do this. I have a table that has an Team_mem,strmonth, and 31 other fields 01 through 31 with valves of D, V, A etc and I need a way to count how many times the letter V shows up in fields 01 through 31 in a given row. Example
Team_mem strmonth, 01 02 03 04 05 06 07 08 09 etc.....
Bill P May 2007 D D D A V V D D
Joe E May 2007 D D D V V V V D D
I need to be able to see a result like
Team_mem strmonth, Count of V Count of A
Bill P May 2007 2 1
Joe E May 2007 4
i'm trying to get a count of how many customers made billing requests during the month of november. the query is based on one table in the database. some customers made multiple requests, but i'm looking for the number of different customers who made requests, not the total number of requests made. the following sql stuff weeds out the duplicate customer numbers and lists each unique customer number.
SELECT DISTINCT TBLBillingRequest.MemberID FROM TBLBillingRequest WHERE (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
my problem occurs when i try to to incorporate the count function like so.
SELECT DISTINCT Count(TBLBillingRequest.MemberID) AS CountOfMemberID FROM TBLBillingRequest HAVING (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));
it gives me a count, but the count includes duplicate customer numbers as well, not the total of unique customer numbers, which is what i want.
any suggestions/help would be greatly appreciated!
I remember reading a tut on how to count records using Running totals in a report - but I cannot remember where I read it. It went something like this.
In the section that you want to count, add a field with record source '=1' and make it a running total for the group.
In the header (one level up from where the running total resides) add a field with record source =Max(RunningTotalField)
Although the 'intellisense' sees my 'RunningTotalField' when I create the above mentioned formula, when I run the report, its asking me to input the value for the 'RunningTotalField'?
Have I confused some concepts or am I on the right track?
I'm creating a report for an imaginary "medical clinic's database", the intended function of which is described as follows: "Create a statistic that shows the total number of distinct drugs prescribed to a patient."
Where I'm at:
I've created a query called UniqueDrugs containing drug and patient info. The SQL is:
Quote:
SELECT DISTINCT Drugs.Drug_Name, Patients.[Patient _ID] FROM Patients INNER JOIN (Drugs INNER JOIN Prescriptions ON Drugs.[Drug_ID] = Prescriptions.[Drug_ID]) ON Patients.[Patient _ID] = Prescriptions.[Patient_ID] GROUP BY Drugs.Drug_Name, Patients.[Patient _ID] ORDER BY Patients.[Patient _ID];
The results of this query seem to be what I need. All I need is for the count formula that I use to return the number of distinct drug names there are that are related to a patient's ID.
I've created a report and I've put the following formula into a text box:
Where PID is the name of another text box in the report that contains the Patient ID that I want to compare to the drug names returned by the query.
However, this *always* results in #Error, no matter how I change the formula.I have been led to believe that syntax is not the issue, as the following formula worked as intended for a different task:
I am a novice and have been searching to no avail for a solution to my problem. I have a main form and a subform (datasheet view) of a table. In my main form I have various calculations which calculates the data from the subform (when auto-filtered). What I am trying to do is count the distinct [Call Number] reflecting the data from the subform into a textbox in the main form (when subform is auto-filtered).
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name 1 xx yy KPP USA John 2 zz kk KPP USA John 3 ss ff TLL USA John 4 ww qq PPO Italy Marco 5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT COUNT(DISTINCT department) AS NumberOfDifferentDepartments FROM people GROUP BY name;
Hi, Wish if some one could help me ASAP. I have a table which contains name, tel, email i need to import only records which have distinct email. I do need need to import data of all three fields but only which has distinct email. As there are number of record which are duplicate. They have different names but same email. So i need to condition only for distinct email but dump the data in a new table with all three records. so same names can have different email. but same email can't have duplicate email. So need only records which have distinct email. Please help .......
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
I have a query with in excess of 20 fields. One of the fields is [POLICY NUMBER]. I want to use DISTINCT to only show rows of data where the Policy number is unique.
If I put DISTINCT after SELECT the query will only include results where the combination of values from all the fields are unique and I dont want this.
I am running a query in a combo box but for some reason the following SQL statement does not give me the result I am after. Which is to only displaying the distinct records. It continues to display the multiple occurrences of the records. I also tried distinctrow with no better results.....
I have a table with the follwing; ShopID, CustomerID, Month, Item_ref, Product Category
There are about one thousand shops. The CustomerID field is only unique per shop. I.e. all transactions for customer ID '002' in shop 1 will be for the same customer, but customer ID '002' could appear in another shop and will be a different customer, I want to be able to bring back a list of distinct customers, the number of items they have had and the product categories.
Has anyone got any ideas how I do this? I presume I would need to do a count distinct or something??
Hello, I have a strange problem: one of my comboboxe is looking up a column in one of my table. Some of the record can be duplicated so I decided to change SELECT to SELECT DISTINCT in my SQL for the row source but it doesn't seem to change anything. Is anybody's got an idea why?
I want to get disctinct count of InvoiceNo while summing up Amount by Date and CustomerID in one query. Firstly, I tried to use correlated query to count unique records but it failed. SELECT A.Date, A.CustomerID, (SELECT COUNT(B.InvoiceNo) FROM Table1 B WHERE B.Date = A.Date AND B.CustomerID = A.CustomerID ) AS CountOfDistInvNo, Sum(A.Amount) AS SumOfAmount FROM Table1 A GROUP BY A.Date, A.CustomerID;
The result of Count is not a unique count. Can anyone tell me what's wrong with this correlated query? :confused:
After hours of googling on internet, the solution I got is equivalent to querying on top of the resulting table from another query.
My final query is
SELECT B.Date, B.CustomerID, Count(B.InvoiceNo) AS CountOfInvoiceNo, Sum(B.SumOfAmount) AS SumOfAmount FROM (SELECT A.Date, A.CustomerID, A.InvoiceNo, SUM(A.Amount) AS SumOfAmount FROM Table1 A GROUP BY A.Date, A.CustomerID, A.InvoiceNo) AS B GROUP BY B.Date, B.CustomerID;
Is there a better solution than this in Access? :)
Tried several suggestions in the forum but haven't found one to match my needs.
I have one table, Jupiter and need to use this query
SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind FROM Jupiter GROUP BY Jupiter.codename, Jupiter.tooling_ind HAVING (((Jupiter.tooling_ind)="Y"));
For each "codename", there is an attached "part_no" which can appear under a codename several times. The problem is that I need to count only one instance of the "part_no" for each "codename". So, my query looks like this: Build # Needing Tool #Tooled LP1 769 192
The # Needing Tool is counting every record that, say pn 10592 shows up in and I need it to be counted only once in each "Build"
I did try this suggestion I found with no results.
SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind FROM (SELECT DISTINCT Jupiter.part_no FROM Jupiter)Jupiter GROUP BY Jupiter.codename, Jupiter.tooling_ind HAVING (((Jupiter.tooling_ind)="Y"));
I have a problem using distinct or distinctrow in one of my query. The result shown reflects all records instead of distinct records. May I know what may be the cause of the problem?
Query: SELECT DISTINCT Assets.*, Depreciation.DepreciationDate, Depreciation.DepreciationAmount FROM Assets LEFT JOIN Depreciation ON Assets.AssetID=Depreciation.AssetID;
I've searched on google for count distinct help but I had no luck. I'm counting 2 different columns and when the query is run the counts in both columns appear exactly the same. Also, I need a SUM distinct (if possible) to sum only where it is being counted in 'CountOfCar_Nr'. Here is my SQL....
All,For some reason, I cannot seem to get a result set with unique records using the DISTINCTROW predicate in Access 2007. However, if I simply use the DISTINCT word (even if I am querying more than one field in a table), the result set gives unique records. This seems backwards....it's certainly not right. I know that the DISTINCTROW predicate does what it's supposed to do in earlier versions of the program, because I use them as well. Does anyone have any ideas on this??Has anyone else experienced this?? Thanks!!
Ok , I will try and explain this as best I can. I have a database and would like to run a DISTINCT query ( which I can do no problems ) I would also like to count the number of records at the same time. Say for example my database looked like this
Name Fruit
Adam Apple Carrie Apple Hollie Apple Joe Apple Adam Orange Carrie Orange Hollie Orange Joe Orange
What I would like it to do is Select the distinct names and list them once ( I can do this bit ) Adam Carrie Hollie Joe
I would then like to count the number of records found for each name and put it next to the relevant name
for example Adam (2) (one apple and one orange)
How would I write the actual query I hope this makes sense.
I'm building a sort of uberquery to do some calculations on a dataset for me, and I reached a point where I'm having some problems. I'm going to have to assemble the main query by doing a join on several subqueries.
Essentially, I have one table with the following fields (DrawingNo, DrawingRev) and another table with a great deal of data in it, but as far as this problem is concerned, it has (DrawingNo, DrawingRev, OrderNo, SPOOL). I have several tests that have to be performed.
The first table contains a master listing of al drawings we are supposed to have, the second contains information related to procesing the drawings. There is one row in the second table for each piece to be made from each drawing.
The first test is to verify that no gievn revision of any drawing has been split across more than one OrderNo. Essentially, I Count(OrderNo) and group by DrawingNo and DrawingRev, so that there is one line in the query for each drawing/revision pair, and a count of the number of OrderNos that meet those criteria.
The second test (and this is the one I have a problem with) involves taking a count of the number of unique values of DrawingRev that occur for a given DrawingNo. Something like SELECT Count(DISTINCT Drawingrev) FROM SecondTable GROUP BY DrawingNo should work, but Access doesn't appear to support "Count(Distinct". How do I get around this, and remember that any answer has to be able to be used as a subquery.
I'm making a form for my school that needs to show every parents name, along with the child they are the gaurdians of. When i use SELECT DISTINCT [Parent Menu PTC].Relations_FullName FROM [Parent Menu PTC]; as the source code it shows only one of each parents name, btu when i use that; I can't see a divorced parents child (it shows bboth parents but only one kid, and they have two kids...) Can any1 help?
Im having some probs with a DISTINCT query and hope you can help me out.
In the simplest terms I have a table that has 3 fields. Firstly the BikeManufacturer, then the BikeModel and finally the EngineCC.
Now from this table I am dynamically creating a drop down list via an AJAX request.
See it here .
When the user clicks on a manufacturer the select should be created showing DISTINCT BikeModel.
This could simply be achieved with a DISTINCT Statement but what I also want to do is order the list by the EngineCC.
Now if i put both fields into the DISTINCT statement i get duplications of the models since they dont all have the same EngineCC. But if i leave out the EngineCC from the select then I cant order by it.
