Modules & VBA :: Count Records In Any 40 Day Period?
Jun 4, 2013
I have a table that contains a person ID, a start date for that person, and a GroupType. I want a way to count the number of people who started within ANY 40 day period. Then depending on that number , assign a value to the GroupType field (ie. if the no. of people is 1-15 then groupType = groupA, if its 16-30 then groupType = groupB, if its over 31+ then grouptype = GroupC. This needs to be re-counted every time a new record is added.
As well as having the groupType visible on the form for that person, I also want to be able to run a report showing all people, their start date and group type. Note that when start dates are entered these may be either future dates as well as dates in the past.
If possible, I would also like to track /store when a person's group type changed in another or the same table perhaps.
View Replies
ADVERTISEMENT
Feb 23, 2008
Hello all,
I need your help please, i have a Form that also has a Subform in it. I need to create a Text box on the form that will display a number which will reflect the total enties made against a particular ID within a rolling six month period using the system date as a perameter. The number shown will only reflect previous entries made that fall within the six months from the current system date, and will need to update once new entry is made. Incase i have confussed the hell out of you, let me give you an example:
Example One
Entry made today = 24/02/08
Previous entry Date = 20/10/07
Textbox on Form = 2
Example Two
Entry made today = 24/02/08
Previous entry Date = 20/08/07
Textbox on Form = 1
View 4 Replies
View Related
Apr 9, 2015
I am trying to get a count from a table, but only for a certain time period. Here is a little bit of my table:
STRMIDtrDateDT trDT
1617 3/25/2015 7:30:00 PM0
1530 1/8/2015 4:40:00 AM10
1532 1/12/2015 9:20:00 AM60
1533 1/14/2015 4:50:00 AM1
What I am currently doing is querying a table for the Count(*) for the day before. This works all well and good, but now I am wanting to break it down by a time range from the trDateDT field.
View 14 Replies
View Related
Oct 23, 2013
I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.
View 1 Replies
View Related
Sep 12, 2013
i want to Count recrods by a pressing a button from a subform.
After counting the records from the subform, it should compare the amount of the records with a number.
The user can put in the number.
The field for the number is called number.
The button is in the form.
I've created the following code.
The Name of the subform ist Abfrage_Vergutungssatze_Unterformular
Code:
If DCount("*", Me.Abfrage_Vergutungssazte_Unterformular) > Me.number + 1 Then
MsgBox "Please recheck!"
I've get an error.
error message: errors when compiling
View 4 Replies
View Related
Jun 7, 2013
I've been struggling with getting a report to simply count records (I don't need any detail other than the counts). Here is what I have:
Table CompressorRoundsT contains these fields (many more actually but these are the ones I care about for this purpose):
Date, Shift, Round
Possible records in the shift field are 1st, 2nd, 3rd and the round field has Rnd 1 and Rnd 2.
For each date, I need to count the number of records per shift and per round. For example, the report should show that on June 7th, 1st shift has 14 records for round 1 and 14 records for round 2. Same for 2nd and 3rd shift. I don't need any other details of what the records contain, just the counts.
I've had some success but the layout ends up too spread out because it is including the individual records instead of just showing the counts and it isn't grouping the way I want.
It's a report for management to see how many readings (rounds) were missed in a period of time.
View 2 Replies
View Related
Apr 4, 2014
What is the best way to pull all records in a rolling 365 day period. Like from 4/3/2013 to 4/4/2014?
View 2 Replies
View Related
Sep 24, 2014
I have a table which specifies the delivery date
I have a from that allows you to choose a year and a month.
I have an unbound textbox which I wan to display the count
I want to be able to count all the records from a table with the year and month specified in the comboboxes and display this in the texbox.
View 4 Replies
View Related
Apr 9, 2014
the project I have comprises four seperate databases all linked but kept apart for logic and data reasons. I must have rapidly approaching 300k records across all of them. As a result I am trying to extract on a regular basis (monthly) the dimensions of each database. Specifically, I want to be able to produce for each database;The number of tables (I have two types data and reference, it would be nice to be able to split the result).The number of fields per table.The number of records per tableI am not really interested at this point about other database objects, such as queries or reports.
View 11 Replies
View Related
Feb 8, 2015
I create form to calculate the difference between clock-in time and clock-out time, what is the code to do that
I have 2 fields :
field 1 name clockin
field 2 name clockout
I have a text box to display result equal the period between time1 and time 2...
View 7 Replies
View Related
Jan 19, 2015
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
View 4 Replies
View Related
Jul 22, 2014
I'm in the process of creating a database in Access 2010 that has two front ends, basically. What I want to be able to do is lock out one of those front ends for a specific period of time every day.
View 2 Replies
View Related
Apr 13, 2006
I have a table tblBookings.
In this table it has a bookingID, CustomerID and some other none relevant details.
The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings
A customer can exist in tblCustomer without existing in the booking table.
I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).
I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.
SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;
How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)
I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.
The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2
For every hotel.
That kind of thing.
If you need more information please shout.
View 3 Replies
View Related
Dec 1, 2013
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
View 4 Replies
View Related
Jan 23, 2007
I have created a query that returns x number of records. How can i count this number (x)?
View 1 Replies
View Related
Feb 1, 2005
I have a field on my Project form(form is based on a query) which is called Completed (Y/N). It holds values Y and N.
I’m wondering if there is a way to count how many Projects completed and how many didn’t. I’m not sure how to count just Ys and then just Ns.
Any help would be appreciated.
Thank you!
View 8 Replies
View Related
Nov 9, 2006
HI there
How to set up textbox which will count how many records is already in my table and add one more and display that in my Form????
cheers
Natal:)
View 2 Replies
View Related
Jan 26, 2006
I have "Age" field from 10-100 years old. My problem is I want to count how many are young(21 below), middle aged(22-45), and old(46 above) in a report. How am I going to write it in a textbox? Please help me and teach me step by step how.
View 8 Replies
View Related
Dec 10, 2007
I have a table with quite a number of records.
In this table I have a field [Qnumber], which gives me a sequential number.
The field size is Long Integer. So my records are numbered 1, 2, 3, ....n
What I want to achieve is to select only the even numbered records in this table, i.e. Qnumber = 2, 4, 6, ...... etc.
I can't find a way to do this.
Would appreciate any help on this.
View 3 Replies
View Related
Jun 24, 2005
Yes i'm having trouble with a Count problem, don't laugh at me.
I've got a query with 200000 plus records. Each record has an equipment ID number (its not the primary key) in column B. I just want to count up the number of records that have each equipment ID code, but im not sure how to do it.
Please help
View 2 Replies
View Related
Mar 27, 2006
I have an order database using Access 2000, I need a report to list all agents that have ordered in the past year, how many times they ordered and sort by Office city.
I have
SELECT tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate
FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID
GROUP BY tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate, tblCustomers.[Job#]
HAVING (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:]))
ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;
It list all agents sorting by AgentID, with a line for each order.
I would like to list each agent once with the number of times ordered, and then sort by AgentCity. Any help would be appreciated... Thanks in advance!:D
View 3 Replies
View Related
Jun 14, 2006
Hi,
I've been going round in circles a bit with this, read a lot of interesting stuff posted, but can't quite answer my problem.
I have a query that returns 2012 records showing the project phase for every Employee assignment. How do I group & count the records so that I get the number of projects in each phase for each employee?
My group and count attempts so far have either reduced the record set to 79 (the number of employees with assignments) or simply returned a "1" for each record.
Any pointers appreciated.
Regards,
View 2 Replies
View Related
Jul 29, 2006
Hi
Been taking abreak from Access for a few years now and cant get my head round my problem.
I have been asked to manipulate data from a Training Recods database.
Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.
I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.
I have enclosed the DB with the table and the form that I am looking to populate with the data.
Anyone have any ideas, I just can't get my head round it!!!!
View 4 Replies
View Related
Sep 15, 2006
Hello, I am new to Access and trying to run a query that will count only unique invoice numbers in my table. I am sure this is an easy command, and thank you for your help!
View 3 Replies
View Related
Feb 16, 2007
Hi guys,
I know how to count records so that null records would be counted but I do not know how to do this:
I have customers and works. I want to create a query that will show ALL customers and number of works done for that customer (sometimes there is no work done for customer).
How can I do it?
Thanks,
Matt
View 2 Replies
View Related
May 2, 2008
I have a table which essentially is:
- Category
- Item #
- Sales $
I'm looking for a query that will tell me the count of items that make up 80% of the sales per category (probably would have to use a subquery here). So if my table was:
item #, sales $
1, $10
2, $8
3, $6
4, $5
5, $1
Total sales = $30
80% of total = $24
The number of items needed to get to 80% of total sales = 3
Is there any easy way to do this in a query?
View 1 Replies
View Related
