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 Replies


ADVERTISEMENT

Help Counting Records

Mar 20, 2006

Hi,

I wonder if someone could possibly help me.

I'm working on a database used to dispatch first aiders to events. The general structure is a form detailing the event with a subform (currently pulling info straight from a join table though I may change the data source to a query at a later date) containing details of attendees in list format.

I have a record in the events form that shows the number of first aiders attending which is currently updated manually. In the subform however, there is a tick box as to whether they attended as sometimes they enlist but have to cancel for whatever reason.

I wanted to implement something that will count the ticks and update the number attended field automatically.

I don't know alot about VB and have tried using the count expression function in the subform footer then setting the number attended field to equal the count field. The problem I find with this though is firstly there can sometime be a delay in updating this and secondly I need the number attended to appear in a report listing all the duties attended each month for expense claims.

I may be half way to hitting the mark with what I've tried but if anyone could suggest anything, I'd be very grateful.

Thanks in advance for the help

Ian

View 1 Replies View Related

Counting LIKE Records

Mar 23, 2006

Quick overview. I have a site # and Subject #. The subject # is 7 digits and the first 4 are the site # (exp. Site # 1000, Subject # 1000001, 1000002, etc). At times the subjects switch sites but their subject # remains the same so Subject # 1000001 now resides at site # 2000.

How would I write a query like the following?

Count [tbl_Enrollment]![Subject #] WHERE [first 4 digits of the subject #] LIKE [tbl_Site_Dem]![Site #]

View 3 Replies View Related

Counting Across Records

Jan 26, 2007

I have a student with an access table that has fields names week1, week2 etc. The data in these fields is either a '1' - meaning present or a '0' meaning absent. We want to be able to put a formula in a query that counts how many absences there have been (similar to a =countif formula in excel)

any ideas?

View 1 Replies View Related

Help Counting Records

Apr 7, 2008

Hello everyone,

Got another problem that I'm sure you guys will solve with your eyes closed ;)

I've got a database with a field called courier_no. Imagine this set of data.


1234
1234
1266
1277
1277
1288
1299

I want to run a query to count the number of instances that each number appears. For example I would expect the results
1234 2
1266 1
1277 2
1288 1
1299 1

I then want to run a criteria on the count field (But I think I'm ok with that bit once I've solved the counting of the data.)

I've tried the count function in the query builder but it doesnt total the instances that each record appears.

Help please (and please keep it simple cos you know I struggle with all that VBA stuff :D)

Stu

View 4 Replies View Related

Counting Records

Feb 8, 2005

hi all...

i'm sure there is a simple soultion to what i want to do but my brain isn't helping me at the moment...

i have a table called bookings with a column called nono which consists of dates with each date being a booking for a bed....

i want to be able to run a report for instance which tells me how many beds have been booked between two dates and year to date...

how do i go about doing this the most efficient way.... i can get a count for one particular date without a problem... and i know i could go and write out totals for each date in the range and then add them up...

however, is there a better more effieicient way of doing this???

cheers in advance.....

View 1 Replies View Related

Counting Records

Jul 22, 2005

I have a table with two fields: Company, Product_Name
Currently the table is not normalized (which is what i am now trying to change). While the Product_Name are unique, the Company names repeat themselves (one company has several products).

I will like to find out which company has more than 10 products and see the names of these companies in a query.

Can I achieve this simply through SQL? If not, How do I accomplish this with VB?

As always, all assistance is highly appreciated. Thank you.

View 2 Replies View Related

Counting The # Of Records

Nov 2, 2006

In my form (on which I have removed record selectors) I would like to display the number of total records. Is there some way of inserting this information in a text box or other control?

Thx

View 1 Replies View Related

Counting Records And Other Fun Stuff

Dec 5, 2005

Hey all,

I am building a database to help my unit in Iraq. Here are the fields I have so far.

Field 1: Date
Field 2: Time
Field 3: Location
Field4: SIGACT (Significant Act)

Well that is the basics look like. I would like to be able to count the number of times a type of SIGACT occured in durring the day, week, and month. I would like to have this come out in a report I could then take to Excel and graph. The SIGACT has a couple different options like IED, and SAF. Thanks for any help you can provide

View 5 Replies View Related

Counting Distinct Records

Dec 18, 2005

i'm trying to get a count of how many customers made billing requests during the month of november. the query is based on one table in the database. some customers made multiple requests, but i'm looking for the number of different customers who made requests, not the total number of requests made. the following sql stuff weeds out the duplicate customer numbers and lists each unique customer number.

SELECT DISTINCT TBLBillingRequest.MemberID
FROM TBLBillingRequest
WHERE (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));

my problem occurs when i try to to incorporate the count function like so.

SELECT DISTINCT Count(TBLBillingRequest.MemberID) AS CountOfMemberID
FROM TBLBillingRequest
HAVING (((TBLBillingRequest.DateSCSCLogged) Between #11/1/2005# And #11/30/2005#));

it gives me a count, but the count includes duplicate customer numbers as well, not the total of unique customer numbers, which is what i want.

any suggestions/help would be greatly appreciated!

View 2 Replies View Related

Counting Elusive Records

Dec 19, 2005

Hello All, I have what I hope to be a simple problem to fix.
I have 2 tables: tbl-Agents and tbl-Reviews
Every employee is an agent however; not every employee has a review for
month(x).
I am wanting a count of all reviews done for each employee even if they do not have a review done.

Joe Smith exist in tbl-Agents and also has 10 instances in tbl-Reviews for October. He will show up given the sql below.
Cathy Jones exist in tbl-Agents but does not have any reviews in tbl-Reviews for October. She does not show up given the sql below.
I need the output to look like
Joe Smith October 10
Cathy Jones October 0

SELECT [tbl-Agents].AgentID, [tbl-Agents].[Dept/Bucket], [tbl-Agents].AgentName, [HISTORY-Q].ReviewMonth, Count(*) AS Expr1
FROM [tbl-Agents] LEFT JOIN [HISTORY-Q] ON [tbl-Agents].AgentID = [HISTORY-Q].[Emp Num]
GROUP BY [tbl-Agents].AgentID, [tbl-Agents].[Dept/Bucket], [tbl-Agents].AgentName, [HISTORY-Q].ReviewMonth
HAVING ((([tbl-Agents].[Dept/Bucket])="Bank Card 61-90dpd") AND (([HISTORY-Q].ReviewMonth)="October"));

Please Help :confused:

View 4 Replies View Related

Counting Records And Updating

Sep 6, 2007

i have a query which find all the records of stock that are similar. I want to count how many of these there are. How would i go about doing this?

I then want the user to type in the system they want, how many they want, and this will "remove" the system from the list, but not from stock i.e. makes it unavailable. The query is been created from the stock, and so everytime the query is run, it would overwrite the "unavailable stock". If any one could help, this would be great!

Emily

View 2 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 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 Matching Related Records

Sep 30, 2007

Hello there,

I have tables like so (simplified):

-=Holiday=-
HolidayID
Name
Date

-=Booking=-
BookingID
HolidayID
ClientID

What I would like to do is create a query which returns a list of all the holidays with a field showing how many bookings have been created for each holiday. This almost works:

SELECT Count(1) AS CountOfBookingID, Bookings.HolidayID
FROM Bookings
GROUP BY Bookings.HolidayID;


...but it does not display holidays where there are no bookings.

Is it possible to create one which will show all holidays even if there are no bookings?

Thanks!:cool:

View 3 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

Summing/Counting Records In Access

Dec 29, 2004

Hello & thank you in advance.
I am fairly new to using Access and would appreciate any help.

I have a file with 50,000 records
Example:
NAME CATEGORY
Karen Shoes
Karen Sports
Karen Collectibles
Karen Cars
Bob Music
Bob Sports
Bob Cars


The results that I am looking for are a sum of the total category purchases by each name.
EXAMPLE:
NAME CATEGORY
Karen 4
Bob 3

Is that something that I can do via Acces?

Thank you

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

Queries :: Counting Specific Records

Feb 27, 2014

I am working on a Human Resources database for a big project that has a field called 'Position Number' (eg. 290) which is unique for each position. But the position can have up to 4 different stages over the life of the project which is shown in the primary key field 'Position ID' (eg. 290-A, 290-B, 290-C). There are columns for each month of the life of the project and If the Start Date and End Date of the position are in between these months then a number one will appear in the Month column.

For example, position 290 will start at level A (290-A) and will start on Jan-14 and finish on Aug14. The next stage of the project will start and position 290 will do the same role in a different area of the project (290-B) and will start on Nov-14 until May-15. Therefore the 48 columns with months as headings from Jan-14 to Dec-17 will show a number one in those months from Jan-14 to Aug-14 and Nov-14 to May-15.

There are over 150 position numbers with up to 4 different position ID's which is phased throughout the 4 years of the project. I need to do a count of how many "number ones" are in the monthly columns per Position.

View 2 Replies View Related

Counting Records With Multi IIF Conditions

Jun 8, 2014

I want to count the records with multi iif conditions. I am able to get one condition right but don't know how to put more than 1.

Eg: =Count(IIf([Step #]=1 And [Type of Work]=1,0))

But I am not able to get if I want step#1, step#2 and step#3 all together with type of work = 1

View 5 Replies View Related

Counting The Amount Of Records With The Same Value And A Message Box Response

Sep 15, 2004

Hey. Basically I have several tables. One table (vistiors) has a combo box in one field called Hostee. Each hostee is listed in another table with all their information. When a hostee is selected in the hostee field of the visitors table I simply need it to count how many people including the currently selected person are being hosted by the person and check if it is greater or less than the amount of people set in the host table which has a field called Limit that has a number which is the maximum amount of people one person is willing to host.

If its greater than the amount in the host table a warning message should come up informing the person (but still allow the assignment to occur.)

As a note I have the combo box set to show the first name of the individual but select both the first name and ID

the code is below for the row source:

SELECT [Hosts].[First Name], [Hosts].[id] FROM Hosts;

If someone could tell me how I could get it to show multiple things within the combo box that'd be great.

IE Right now it shows just a first name liek Alex, Rachel, Bill. I'd like it to show the first name as well as the last name and a third additional field. IE Alex Lastname T1 or Rachel Lastname F4 etc.

Can anyone show me the general code to do this? I do know some VB but I'm used to using SQL with PHP primarily. I am new to access and only know it vaguely. Thanks in advance!

View 2 Replies View Related







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