I'm trying to count the number of records within a region range using a lookup table however I keep getting duplicate values, SQL code, what is happening:
SELECT Count([summary].Key) AS CountKey
FROM Summary, lookup
WHERE ([Region])) Between [Region 1] And [Region 2]));
I have an access query named "leaveapp" and I want a running count as below:
EmpID TypeID
360 1
360 1
360 14
360 14
360 8
1390 8
1390 8
1390 14
1390 14
1390 1
and i need a column in the right with running count like below
EmpID TypeID runningcount
360 1 1
360 1 2
360 14 1
360 14 2
360 8 1
1390 8 1
1390 8 2
1390 14 1
1390 14 2
1390 1 1
I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
I want to do something within Access 2010, but unfortunately I have only partly succeeded so far..
So far this thread has been very useful: [URL] ....
It (almost) completely explains my problem. I have a database with suppliers that participated in tenders. Now I would like to, on a chronological basis, make a "running count" on how often a supplier participated in these tenders (on the basis of supplierID).
In the attachment you can see an example of how the data in my table looks like. With the following function, which I got out of the thread above, I have already been able to make a running count on how often a supplier participated (its "experience").
The function:
DCount("*";"[Table1]";"Supplier_ID = " & [Supplier_ID] & " AND ID <= " & [ID])
So far, so good. But I would like to go for an extra addition, since now the period on which this running count is based is the entire data set, which runs from 2006 till 2013. In other words, Access starts counting from the first record and ends at record 300.000. What I would like to do is to base this running count on the two years before (and including) the tender's Decision_Date, which is also in the dataset.
Example: a specific supplier participated in a tender that took place on 10 december 2010. Know I would like to know its "experience" with tenders, by means of a running count, based on the period from 10 december 2008 till 10 december 2010.
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?
Query is based on 1 table" tblTimeCnv_AgeGroups
Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
See attachment
My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])
In the Ranking column the result is: #error in the first 3 rows then zeroes
I have data like so:
Code:
SubjectID VisitID
1 5
1 5
2 7
2 7
2 9
2 9
etc
And need to return a running count for the number of visits per subject, so:
Code:
SubjectID VisitID VisitCount
1 5 1
1 5 1
2 7 1
2 7 1
2 9 2
2 9 2
etc.
I'd like to use DCount and have tried all variations of the following, but none of them returns the correct number:
VisitNumber: DCount("*","mytable","VisitID >= " & [VisitID] & " AND SubjectID =" & [SubjectID])
I have 2 tables. tblOrders and tblOrdersItems. tblOrdersItems is the child and tbl Orders is the parent. the linking field is OrderID.
I am wanting to count all the OrderID in tblOrders. the criteria i need is EmployeeListID which is stored in tblOrdersItems.
When I put these tables into query design, add the criteria, grouping(see below)
Code:
SELECT tblOrders.OrderID
FROM tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee
WHERE (((tblOrders.OrderDate)>Date()) AND ((tblEmployeeList.EmployeeListID)=[Forms]![rptReportsMainScreen]![txtEmployees]))
GROUP BY tblOrders.OrderID;
I get (record count at bottom of query screen) 87 records. then i add a field to give me the count so i can then use it i get many more.
How to get the count working. i have uploaded the relevant tbl and my attempted query.
I have a very simple query which brings back a count of records. I would like to add a percentage column to this which shows a percentage by ClinicCode. I've attached some sample data and what the inteded outcome should be.
View 1 Replies View RelatedI have a table similar to the following
area q30 q40 q60
A 2 1 3
B 1 1 3
A 2 1 3
A 1 1 3
C 1 1 3
C 1 1 3
A 1 1 3
A 1 1 3
B 1 1 3
C 1 2 3
A 1 1 3
B 1 2 3
Grouping by the area and using the criteria
(Q30 = 1 AND Q40 = 1 AND (Q60 = 1 OR 2 OR 3))
I would like to see a table similar to the one below counting those which fulfils the criteria mentioned above
Area COUNT
A 4
B 2
C 2
I am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.
SELECT t1.*
FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date])
WHERE t2.OverrideDescription IS NULL;
However I also need to be able to do a unique count of the [OverrideDescription] field.
I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".
Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.
I have the following code and I am trying to show a COUNT of duplicate ID numbers, so for example
ID ID_COUNT
2000 2
2000 2
2001 1
2002 1
2003 1
Code:
SELECT [DaisyServiceData-Import].*, Companies.CompanyName, Company_vs_Services.[Tariff Code], IIf([ClashForce]=True,"",IIf([ClashOveride]=False And [ClashClear]=False,IIf([DaisyServiceData-ClashExc].[DaisyServiceData-Import].[ID] Is Null,"",1),"")) AS ClashExc, IIf([Sell Price]="","",IIf([SellRateForce]=True,"",IIf([CompanyName] Is Null,"",IIf([SellRateOveride]=False And [SellRateClear]=False,IIf([Total Cost]>[Sell Price]+0.001,1,""))))) AS SellRateExc, IIf([Tariff Code]='1',[T1Rate],
[Code] ...
A GROUP BY query will not work for me as the recordset needs to remain updatable.
I am using Access 2010 on a Windows 7 laptop. I need a query to provide a list of ID's that have more than one occurrence of IDandDate combined but haven't been successful getting past syntax errors.
Using this table structure as an example:
ID - defined as text field
Date - defined as date/time field
TestValue
This legacy table contains a record for each test. The table should be unique based on the ID and Date combination but was never restricted to that rule. I am converting to a new table but need to identify the duplicate entries so they can be addressed by business folks.
Access 2010 query.
I have a table with a field 'FName'. I have run a query to count the number of each Name in this field. However, as well as having a blank field with 9129 results (which was expected) but another with 24 results that appears to be completely blank as well. There are another couple of incidences were results in 'FName' are appearing twice.
how to identify what is causing this duplication?
Hi,
i am new using SQL statements,
i have a simple Table something like this from a test station:
--------------------------------
Serial_Number | Status |
--------------------------------
A_____________Pass
B_____________Fail
C_____________Pass
A_____________Fail
B_____________Fail
A_____________Pass
and i need to get a query that separate 2 colums from the Status and count the records for each Serial_Number, some thing like this,
----------------------------------------
Serial_Number | Pass | Fail |
---------------------------------------
A_____________2______1
B_____________0______ 2
C_____________1______ 0
----------------------------------------
does anyone has an idea how to do this in Access?
Hi all,
I hope someone can help, i'v searched but i still couldnt figure out how to do this. =(
The screenshot i've attached is what i have so far.
What i want to do is add a column within that query that counts the number of times the ComplaintShort is the same within the Query and displays it.
I'd also like to make the ComplaintShort Field Distinct so it would only show it once and show the number of times it has really appeared as a duplicate entry.
Heres the Query I currently Have to work with.
SELECT Cities.P_Names, Complaints.precinct_id, Complaints.ComplaintShort
FROM Complaints INNER JOIN Cities ON Complaints.prec_id=Cities.P_prec
WHERE Cities.P_Names=Forms!Form1!comType
ORDER BY Complaints.ComplaintShort;
Please Help :(
Thanks in Advance
I have a query where I need to incrementally count the repetition of column values and add that value in a new column.
Example:
"Column One" and "RepeatCounter" are fields in a query.
Column OneRepeat Counter
Value 11
Value 21
Value 31
Value 12
Value 13
Value 32
I want to create the outcome in the field "Counter" in an Access Query. I am a novice in Access VBA. I found I can do it in Excel with the formula CountIF. I am unable to do it in Access.
Hello
I have a problem with a SQL query I build. I have a complaints database and for a listbox I need the 5 costumers with the most complaints in the database and the total amount of complaints so far. To do so I tried the following SQL query:
SELECT TOP 5 [Costumername],Count(*) AS [Count] FROM [Complaints] WHERE [Costumername]Is Not Null GROUP BY [Count];
But I get the following error message if I try to run this query: “You tried to execute a query that does not include the specified expression ‘costumername’ as part of an aggregate function.”
I noticed that a pie chart uses almost the same query (only sorted by name, and not the count) and this works, until I try to change that query to the one above. I hope someone can help me with this. Thanks!
Hi,
I was wondering if there a method in Access to allow me to count the number of items by group?
For example, I have a set of data in a table like so:
SA
SA
SA
SP
SP
BM
I can choose Group By and the data will group them like SA, SP, BM; and I can choose Count to count the total number of records, but I would like for instance to be able to retrieve the following data:
SA: 3
SP: 2
BM: 1
Currently I'm exporting the list to Excel, and then using Subtotals. It does exactly what I want, but I'm pretty new to Access and wondered if there's a different way! :)
I would like to select data from a database using 'Select * " based on a value in a row (same column)being unique.
By that I mean that that data must not repeat again. Idealy I would like to set the number my self so rather then unique I could say
select the rows from the database only if the uniquevalue does not repeat more then x number of times.
eg
Value A
Value B
Value C
Value B
Value B
Value C
So if I wanted to set the uniquness to 1 then only row with Value A would be collected.
If I set the uniquenss to =<2 then I would get data from rows with value A and C so
3 rows returned.
I have this so far
SELECT *
FROM SingleS.mdb
GROUP BY Uniquevalue
HAVING count(*) = 1
Hey all!
I'm having issues trying to create a query that will take data from two tables and summarize it based on ID, Count(ID), Sum(Score), Sum(Xs). I don't have an issue doing the combining, just the summing and counting (actually, just the counting). I've tried playing directly with the SQL statement, but I keep getting errors.
This is what I have:
tblRoster
ID, Name, Sex, Youth
tblScores
ID, WeekNo, A1, A2, A3, A2X, A3X, B1, B2, B3, B2X, B3X
Sample Data for tblScores would look like this (scores actually are not the same, but you get the idea)
10, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 5, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
What I would like to do is summarize the info in the following manner:
ID, Name, Sex, Youth, Count(WeekNo), Sum(A1+A2+A3+B1+B2+B3), Sum(A2X+A3X+B2X+B3X) such that I would get the following results:
10, John Smith, M, , 4, 2176, 84
23, Jane Doe, F, , 5, 2720, 105
67, Johnny Jones, M, Y, 3, 1632, 63
I have been able to get the summed part done, but my attempts to do the count have failed. I know that the resulting query will be straight forward, but right now I'm cross-eyed.
Thanks.
-BT.
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
View 2 Replies View RelatedI have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.
View 1 Replies View RelatedI have a Union Query which the result was like this:
Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00
Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date.
here goes
im trying to count the number in a group between a time period. the query takes a time period from a form runs then reports who and how many heres what i get
SELECT tblEstimate.Client, Count(tblEstimate.Recieved) AS MyCount
FROM tblEstimate
GROUP BY tblEstimate.Client, tblEstimate.Recieved
HAVING (((tblEstimate.Recieved)>=[Forms]![frmEnquireFront]![EqfromDate] And (tblEstimate.Recieved)<=[Forms]![frmEnquireFront]![EnqtoDate]))
ORDER BY tblEstimate.Client;
Client MyCount
Amey Vectra Ltd 1
Amey Vectra Ltd 1
Ashworth Mairs Group1
Property Consortium1
Property Consortium1
heres what im trying to achieve
Client MyCount
Amey Vectra Ltd 2
Ashworth Mairs Group1
Property Consortium2
am i going about this the wrong way
is there an easier way
any help would be great
thanks in advance
john:confused:
In have a table which contains customer address details.
I simply want to display a 2-column list. the first column
to display a disnct list of city's from the main table.
the corresponding column must give the no. of records that
contain the entry in the first column.
eg if the main table had 5 customers that lived in london
and 6 in Maidstone then
London 5
Maidstone 6
etc