Whats up all U brilliant people,
I am baffled again. I need to print a report that can count the number of entries in a database or query. Basically I am looking for the equivalent of the countA function in Excel. All advice is welcome.
Eternally Grateful,
Khepra
I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.
Thanks ... Erik
How would i count the total number of records on a form
View 5 Replies View RelatedI need to count the number of entries for a particular item located in a table. To keep it simple, we have a database used to process orders for Turkeys, this database has a field for each order called "Long legged Weight". In order to sort the turkeys in the shed, we need to know how many of each weight there are.
Order Number / 10-12 / 11-13 / 12-14 / 13-15 / (etc)
......1 ................1
......2 ..........................1
......3 .....................................1
......4 ..........................1
Total ..................1 ......2 .........1
I have a table that tracks different events and the actual date they occurred. I am trying to write a query that counts the number of each event that occured by month. One issue I am having is if an event had no occurrence during a month I want to return 0 raher than no data for that event.
View 2 Replies View RelatedHey all,
I'm having a horrible time trying to get this to work. Basically, I have a database which has multiple tables. All of my tables are being generated with a CREATE TABLE statement. What I would like to do is add a validation rule or something else upon creation so that only X number of entries can be entered into the table.
Initially, I just added a rule that said id<5 or whatever manually to the table in order to restrict the number of entries, but I can't figure out how to do this automatically in my SQL statement or Visual basic. I want to be able to have this check added upon table creation. Surely what I'm doing must be possible.
Am I going about it the wrong way? Any Help would be appreciated. Thanks!
HeyI'm trying to create a Library database. This involves restricting the number of loans available to each member (currently 3). I need to make it so no more than 3 records exist in that table for each member. For now ive just entered a VB script that has a message box saying not to enter more than 3 loans per person but I dont think thatll be enough, I need some sort of validation for the whole table rather than a field.I know how to limit the returns of sql queries which is all I can find, but not how to limit the actual amount of entries. The loan table is part of a double query within a subform of the member form if it helps :s
View 6 Replies View RelatedHi...
here's a good one...
I have created a database which allows people to enter in when they are off sick. They have to enter in the FIRST DAY of sickness and the LAST DAY of sickness. All of this information is stored in one big table - SICKNESS TABLE. There are no other date fields in that table.
I want to know how many people have been off for 5 TIMES OR MORE in a 365 day period (which may be say, from Oct 2005 to Nov 2006 - so not always in the same year).
I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.
How can I get it to tell me if someone has been off for 5 TIMES OR MORE in a 365 day period???
Thanks
Maria
I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.
I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).
Person Referral
1 A
1 B
1 C
2 D
2 E
3 F
3 G
3 H
What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.
I'm on Access 2010 if it makes any difference.
Hi all,
Is there any option to count the number or records relating to
a specific field. Here is the details..The db is for a school and there is a tables for
class and students. I am using a form to enter students details. I had created a
relationship with these two tables. i am using a query to get the details regarding a specific
class like classname, class teacher like that.. Is there any way to calculate the number of students in that specific class.
now i am using the query as follows
SELECT Class.*
FROM Class
WHERE (((Class.ClassID)=[Forms]![Class]![ClassID]));
I had created the relationship with the above two tables with classid as the primary key
thanks in advance
Thanks
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.
I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.
What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )
thanks for your help!
tony-t
I'm after some code as to how to count a number of records within a table, I then want to have this number returned into a variable so I can determine usages with it, e.g. If the value is < 1 and using conditional statements.
View 1 Replies View RelatedI built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.
Code:
SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID
FROM qryGroupbyWeek
WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end]))
ORDER BY qryGroupbyWeek.ID;
I have about 150k rows that look like this
SHORT_DESCRIPTION
unable to view
unable to send out some stuff
what is going on
Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:
Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1
Is this possible?
I have a query to calculate the monthly depreciation for a machine for specific period of time.
My problem is how to get the number of months for each period assuming i want to start from month September 2009 till may 2012.
Attached the data extracted by the query.
I want to calculate the depreciation to be grouped year by year in report.
[TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
Hi, am sure this is simple....! Can't find a solution.
I have records which have YEAR and APPARATUS as two of the fields. All I want to do is write a query which displays all the records chronologically by YEAR then within each YEAR to group them by the biggest subset according to APPARARTUS.
Therefore if there were 20 people who used Motorised equipment, 15 who used electrical and 10 who used gas, the records would be grouped in that order. Thus showing the pattern in change in use of specific apparatus over the years.
Why can't I DO THIS!!??
aLI
I have a spreadsheet and the only column that matters is D.
Down column D it appears as:
a
b
b
b
c
c
d
e
f
c
a
I need the result to just appear like
A - 2
B - 3
C - 3
D - 1
E - 1
F - 1
I have been tasked to create a DB for my unit. I have created a few DB, but I am a novice at best. I need a crosstab Query to count the number of records for each FY. The Army's FY is from Oct -Sep. I only need to show the the total number of record for the previous FY in a Report and on a form.
View 7 Replies View RelatedI'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:
=DCount("[Drug_Name]","[UniqueDrugs]","[Patient_ID]=[PID]")
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:
=DCount("[Prescription_Date]","[UniqueYearQuery]","[Patient_ID]=[PID]")
How do I use a DCount to return the number of different drugs that have been prescribed to a patient?
If it did not require the number of different drugs, I would simply use the following formula, which works just fine:
=Count(Drug_Name)
I need to count the days that an item of equipment is used for the last week?
The problem comes if an item is deployed within the week i.e not used for the full week?
I have two fields - MobDate and DeMobDate
I have to create a third called NOD (number of days) which will display the number of days in the previous week that the item was hired out.
I want to use to count how many times a record is displayed in the period and lesson field, in another table, so that this data can be used to show how many people are attending a class. e.g how many times people are attending art 1, by counting how many times art one appeared in the first table.
View 1 Replies View RelatedI am currently trying to figure out how to limit the entries into a nested sub form to just one.
for example im making wine collection database, I have a PURCHASE table, a WINE table containing the FK of the PURCHASE PK, and an INVENTORY contain the FK of the WINE PK.
a purchase can contain many wines, but one wine can only be in one inventory location.
I am trying to add all this information with one form. I fill out the purchase details, enter the info of X bottles of wine were in the purchase, and then with one of those wines selected in their subform, i enter the inventory details for that wine. I go select the next wine in the first subform and the inventory nested subform doesnt allow another entry.
I have this code loaded into the On Current event for the main form:
Private Sub Form_Current()
With Me![INVENTORY Subform].Form
If .Recordset.RecordCount = 0 Then
.AllowAdditions = True
[Code] ....
I have a table called exiting staff data and a field called Follow up required. I want to count the number of Yes entries and display it on the main menu. Have tried
Code:
=Count([Exiting Staff Data].[Follow up required]="Yes")
And using dcount but keep getting error messages.
For my database the business has Clients who they conduct Needs Assessments for, but they only conduct 3 needs assessments. I have a Needs Assessment table with Client ID as the foreign key. how I can limit the number of times a Client ID shows up in the foreign key field to three?
View 2 Replies View RelatedI have a form (called User_Input)with 2 text boxes, 5 combo boxes and a Submit button, which is bound to a table (called Submssion table) i.e the form submits its entries into the table called Submission.
I want the submit button on the form to work based on this condition:
1. Check current submission of four fields (corresponding to entries from 4 out of the 5 combo boxes on the form) in the table and confirm if the current selection on the form already occurs in the table up to two times. If this is the case, the Message Box should pop up on the form informing user to make another selection as maximum number of entries for that particular selection is already in the Submission table.
2. The checks for the four fields will be done simultaneously as the selection will be done together on the same form.
3. However, if the entries on the Submission table is not up to 2, the entries should be saved to the table and Messagebox should display "Thank you for your Submission"
I need to use Macros for this being a web database which will be published using Sharepoint and VB is incompatible with the web database.
I'm trying to run a line of code after doing a Query Search that reports back the total number of results pulled from the search. I've dug around and found some useful code, but nothing that gives the results I'm needing. Most everything gives me the total number from the query. I'm also using a Split Form.
The process goes: Enter numeric search in Text10 and hit the search button to run the following macro: [studentID]=[Forms]![Query1]![Text10]
It then gives me the requested information.I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.
Can I simply add an additional line to the Macro?