Counting Number Of Records In Relation To A Query

Aug 20, 2004

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

View Replies


ADVERTISEMENT

Counting The Number Of Records Returned In A Query

Aug 24, 2004

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

View 1 Replies View Related

Queries :: Crosstab Query - Counting Number Of Records For Each FY

Sep 19, 2013

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 Related

Counting Number Of Days That Contain Records

Feb 11, 2007

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

View 5 Replies View Related

Counting The Number Of Records On A Form

Dec 7, 2006

How would i count the total number of records on a form

View 5 Replies View Related

Forms :: Counting Number Of Records Within A Table

May 24, 2013

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 Related

Reports :: Counting Number Of Distinct Records In Report?

Oct 11, 2013

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:

=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)

View 6 Replies View Related

Counting Number Of Used Months In Each Year Within A Query

May 11, 2012

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]

View 3 Replies View Related

Queries :: Counting Total Number Of Returned Results From Query?

Aug 11, 2015

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?

View 7 Replies View Related

Deleting Records That Have No Relation

Aug 9, 2011

I have two tables, one for jobs and one for clients. I have a one-to-many relationship, so one client can have many jobs. Due to a problem with data entry, I have some client records that have no job associated with them. I need to delete these records.

View 1 Replies View Related

Counting Records In A Query

Oct 10, 2006

I am writing a query to determine employee's commissions. The commission rate changes for all order over the first 15 per employee per week. What would be the best way to write the formula?

View 12 Replies View Related

Counting Records In A Query

Nov 15, 2012

I have two tables, RMA Numbers and Serial Numbers. Each RMA number will have multiple serial numbers assosiated. I need to find a way to count how many serial numbers are assosiated with each RMA number.

I think the best way to do this is in a query using the dCount function some how.. but I can't figure out how to get it to count different serial numbers for the same RMA number.

I also considered writing my own function to do it.. but it got really messy and there must be an easier way to do it..!?

View 2 Replies View Related

Question About Counting Records With Query

Aug 17, 2005

Hello everyone! I'm new to this forum and this forum has been a very useful resource on creating my first database. I know nothing about coding and this is my first time trying to create an Access database for work. I have been working on this database for more than 4 months and this forum really helps me from creating tables all the way up to forms. THANK YOU!!

Now, I'm struggling on creating reports that counts records from my tables. Here are two of my problems:

First:

I have a main table called "tbl_main" which records students' basic information, including their birthday in a column called [dob] in mm/dd/yyyy format. I need to create a query that counts how many students in age 11, 12, 13, 14, 15,16, and 17 separately so I can list them on a report.

Second:

I have a enrollment history table called "tbl_enrollment" which records dates that students had been in and out of the school. Since a student can re-enroll for more than once, there are more than one record for the same student. I have student ID [idnumber] and admitted date [admitted] columns in this table. I need to create a query that gives me a total number of enrollment, number of one-timer, and number of repeater, so I can list these numbers on a report.

For both reports above, user will input a timeframe to count particular number of records. Thanks to this forum, I already have a working form with start/end fields for user to input. What I'm struggling is creating queries to count records.

Thanks again for spending time reading my first post in this forum and I hope I can get helps from anyone of you. Since I'm not a programer, you may need to step-by-step walk me through the whole process. Sorry!!

View 1 Replies View Related

Counting Unique Records In A Query

Jun 1, 2006

I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.

SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;

qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.

(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)

Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.

Any help is greatly appreciated. Thanks.

View 4 Replies View Related

Counting #of Records Returned In A Query

Aug 20, 2004

i have a query that returns anywhere between 0-20ish records, and i need an efficient way to count how many records are returned. any ideas?



i was also wondering if there is a more efficient way to do this:


Code: [Text127] = DLookup("[Comments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text175] = DLookup("[Performed]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text177] = DLookup("[Model]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text179] = DLookup("[Serial]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text181] = DLookup("[System]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text183] = DLookup("[HComments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text201] = DLookup("[Labor]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)

mabey i could condense them into one Dlookup somehow? any ideas?

thnx for your help

View 3 Replies View Related

Queries :: Counting Records From More Than One Query

Aug 14, 2015

I'm running into an issue where I'm trying to tie several queries together into a list one running total. I have six queries that pull data from the same table, but that meet specific criteria. What I was trying to accomplish was to have a 7th query count the records in each of the six queries, and return the results as a different value for each. Here's an example:

Queries:
qry_1A
qry_1B
qry_2A
qry_2B
qry_3A
qry_3B

Final product:
qry_totals:
1A | 1B | 2A | 2B | 3A | 3B |
20 | 15 | 33 | 19 | 12 | 6 |

What I tried:
Field: 1ATotal: Count([qry_1A].[valueName])
Total: Expression

And I did this for each field that I wanted the query to return, so:
Field: 1BTotal: Count([qry_1B].[valueName])
Field: 2ATotal: Count([qry_2A].[valueName])
Field: 1BTotal: Count([qry_2B].[valueName])
Field: 3ATotal: Count([qry_3A].[valueName])
Field: 3BTotal: Count([qry_3B].[valueName])

The problem is that I don't get what I expected - the query appears to be totaling all the records counted and applying that value to all the fields, so I get this:

1A | 1B | 2A | 2B | 3A | 3B |
105|105 |105 |105 |105 |105 |

View 2 Replies View Related

Queries :: Counting Records In Query?

May 10, 2013

I have constructed a neat database for randomly quizzing myself on French translations. However I need a simple way of counting the records in an underlying query "vocabularyQ" inside a sub routine. I have tried all sorts of statements the most recent being

SELECT Count(VocabularyQ.ID) AS CountOfID FROM VocabularyQ

View 5 Replies View Related

Counting Of Cases And Counting No Records

Jan 26, 2007

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.

View 1 Replies View Related

Index And Query Relation?

Jul 4, 2007

Hi guys

thank you in advance for any idea/info!!

i have 2 tables, P.O and Invoice. In PO tbl i have an Invoice field with Index set to Yes (No duplicate)
On a form based on PO table, I couldnt enter a new record due to this so i set the Index back to NO
Now..I have a combo bound to PO table with a query taking a list serial number from another table. I want to filter away whatever serial numbers from this list that have been added to PO so i write this SQL
"SELECT DISTINCTROW tblComputer_Cabinet.[CC_Serial #]
FROM tblComputer_Cabinet, tblPO
WHERE (((tblComputer_Cabinet.[CC_Serial #])<>[tblPO].[CC_Serial #]))
ORDER BY tblComputer_Cabinet.[CC_Serial #];"
which worked fine before i removed the index

Why was it?

View 2 Replies View Related

Counting The Number Of Entries

Aug 13, 2004

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

View 5 Replies View Related

Counting Number Of Values For A Particular Field

Nov 5, 2012

I 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

View 14 Replies View Related

Counting Number Of Occurrences By Date?

Sep 20, 2013

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 Related

Queries :: Counting Number Of IDs For Each Week - GroupBy

Oct 22, 2013

I 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;

View 2 Replies View Related

Parsing Through A String And Counting Number Of Occurrence

Feb 12, 2013

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?

View 6 Replies View Related

Counting Number Of Times A Username Appears In Column A

Mar 5, 2008

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

View 2 Replies View Related

Queries :: Counting Number Of Days When Item Used For Last Week

May 27, 2014

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.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved