Queries :: Running Count Over Group In Query
May 11, 2014
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.
View Replies
ADVERTISEMENT
Feb 2, 2015
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
View 3 Replies
View Related
Mar 16, 2013
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
View 3 Replies
View Related
Sep 15, 2014
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])
View 4 Replies
View Related
Jul 17, 2014
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]));
View 4 Replies
View Related
Sep 19, 2014
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.
View 14 Replies
View Related
Aug 14, 2013
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?
View 7 Replies
View Related
Jul 6, 2013
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.
View 9 Replies
View Related
May 6, 2015
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 Related
Jun 4, 2015
I 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
View 2 Replies
View Related
Sep 15, 2013
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.
View 1 Replies
View Related
Dec 24, 2006
I have a query with 40 fields and 16,000 records. Field 1 is Location, of which I have 197 unique values. What I would like to do is have a running count for each unique location and when the location changes start the count over again.
Example
Count Location Function
1 100 1111
1 105 1111
2 105 1121
3 105 1131
1 110 1151
2 110 1151
I am using Order By to get the proper sort. In some other instances I may want the count to be dependent on the combination of Location and Function codes. Can someone provide some SQL tip(s) that would allow me to accomplish this.
Thanks
Loki
View 5 Replies
View Related
Sep 6, 2005
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?
View 1 Replies
View Related
Nov 10, 2006
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
View 4 Replies
View Related
Apr 11, 2007
Hello,
I'm trying to calculate some running sum queries in preparation for some charts on a report.
I have:
tbl: Spencerdaily
fld1:datein
fld2:milesdriven
fld3:vehiclenumberdaily
I am trying to calculate the running sum of milesdriven for each year per vehicle. If I use the following I get the same set of sums repeated for each vehicle:
SELECT spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year]) AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
I tried this but it didn't work:
SELECT spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily AS vehiclealias, DatePart("yyyy",[datein]) AS [Year], DSum("milesdriven","spencerdaily","DatePart('yyyy', [datein])<=" & [Year] & " AND [vehiclenumberdaily]<=" & [vehiclealias] & " ") AS Mileage
FROM spencerdaily
GROUP BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein])
HAVING (((DatePart("yyyy",[datein]))>2001))
ORDER BY spencerdaily.VehicleNumberDaily, spencerdaily.VehicleNumberDaily, DatePart("yyyy",[datein]);
What am I doing wrong?
Thanks,
Gifford
View 2 Replies
View Related
Jul 18, 2013
I'm using Access 2003 and excel 2003.
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
View 1 Replies
View Related
Mar 16, 2014
creating a query with a running sum (cumulative total) across two categories. I need to accumulate Wages by employee, by calendar year for every day/every job worked. I have a table containing over 33,000 records, the years span from 2009 to 2014 with multiple employees.
The Dsum option I have found on the internet is too time-consuming and locks up my query for the over 33,000 records I need to generate results for. It needs to be a SQL statement. Although all the SQL statement I have tried simply total all Wages for every entry ever made in the column, instead of per calendar year, per employee.
Here are my fields:
Calyear = Ascending
Employee Name = Ascending
WorkDate = Ascending
ID
[code]...
There may be more than one entry per day per employee in a given year. This is so I can calculate certain payroll taxes which are based on cumulative wages amounts.
View 4 Replies
View Related
Feb 7, 2006
:eek: I'm running a Make table query that links two tables in a one to many relationship. What I need to do is add a column that is a running or sequencial counter. The counter is used to preserve the order of the child records thus I don't want it sorted. It would be nice if the counter would reset when the Parent-ID changes. This use to be quite simple in Access 98...so much for better ideas. I have reviewed the other forums, but most deal with totals, I need running count. The others are unclear, so I asking one more time. Thanks
Sample of what I need:
Parent-Id_ Child-Id_ Count
___ 123___ 043____ 1
___ 123___ 121____ 2
___ 123___ 321____ 3
___ 123___ 065____ 4
___ 243___ 032____ 1
___ 243___ 043____ 2
___ 243___ 012____ 3
___ 187___ 321____ 1
___ 187___ 022____ 2
View 1 Replies
View Related
May 17, 2007
I am trying to count records in a running sum fashion in a QUERY and for the records to restart when the field changes. I tried DCount without success.
ie. if a field contained the following data
Code Running Count
C101 1
C101 2
C103 1
C103 2
C103 3
C104 1
C104 2
Thanks
View 1 Replies
View Related
Jan 14, 2014
I have a form with tab set one tab called "Enter Receipt" and another that houses 2 queries called "Reconcile". My issue is when I open that form, I have an On Current Macro to go to NEW record for my Enter Receipt, but I am getting a delay while the query status bar runs the other queries. I was hoping not to have those ran until i enter the parameters and hit the run button on that "reconcile tab".
Everything else works, i just need the queries to keep from running when i load the form. my queries i moved from EDITED to NO LOCKS thinking the On Current new record may affected them, not change in delay.
View 3 Replies
View Related
May 20, 2015
I have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
View 1 Replies
View Related
Jun 18, 2013
I have a table for timesheet entry for a local building firm. I have a separate table containing employees and rates. I have created query "Qry_ Time Costs" which calculates the cost of hours worked by each employee by multiplying the hours field in the timesheet entry table by the rate field in the employees table.From "Qry_TimeCosts" I have created "Qry_TimeCosts1" in which i have included a running total field for time costs per day using the DSum function.
SELECT Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], Sum(Qry_TimeCosts.Hours) AS Hours, Sum(Qry_TimeCosts.Cost) AS Cost, DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "") AS RunTot
FROM Qry_TimeCosts
GROUP BY Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "")
HAVING (((Qry_TimeCosts.[Build Element]) Is Not Null));
This seems to be working for some projects and not for others. In particular dates 3rd 4th and 5th of June seem to be showing null fields, where all other dates have values. A few projects are showing inaccurate running totals whilst others are working fine.
View 1 Replies
View Related
Jun 3, 2015
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
View 7 Replies
View Related
Oct 8, 2014
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
View 4 Replies
View Related
Apr 16, 2013
I have now a database where I made a query that will show me all clients I can charge when there is a end date and an account number mentioned. I have also in a table where I put if the client is already charged. This is a check box that gives me yes or no. Is it possible to run the query and once those selected show up, to also add a yes to the table so next time I run the query the ones charged will not be charged again.
View 5 Replies
View Related
Sep 15, 2013
I have a count and store data INTO a table named USTATE
When I run the query it works within 30 seconds. but when i add INTO USTATE then it takes 4-8 minutes to complete it and asks for if i want to delete the existing.
View 1 Replies
View Related
