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 Replies
ADVERTISEMENT
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
Jul 31, 2006
I am trying to create a sum of a field called ec2mo where mopp is between 0 and 2 and then another sum when mopp is between 2 and 4, etc... I've tried a number of different things, but it doesn't seem to work - instead i get a single value for each field (i'm assuming because there are different values for mopp and many other fields)... I've attached the db with just that table.
Thanks very much for your help!
P.S. I'm not really at all good with using SQL so anything that can be done in a regular query would be most helpful!
View 3 Replies
View Related
Oct 4, 2013
I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;
1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings
These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).
I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).
View 1 Replies
View Related
Mar 25, 2006
I am not sure if this possible, the methods I have looked up on the Internet take ages for the query to open but I'll give it a shot.
What I need to do is count (and increment) how many records are returned in a query for each person.
So if 1 person returns 4 unique records, in these records it shows 1,2,3,4 in each record.
The way i have tried is by using DLookup to check the next record and evaluate it using IIF but this takes about 25 seconds to return circa 50 records.
View 5 Replies
View Related
Jun 27, 2006
Hello Everyone,I am trying to perform a sum of records for the table belowSeatType| Ext rent | |Internal rent | Work Transfer 2 2VendorOther 2 3LOB BRF GTI BRF 2 5Displacement so that when I run the query I get the following followingSeatType| Ext rent| |Internal rent| | Total |Work Transfer 2 2 4VendorOther 2 3 5LOB BRF GTI BRF 2 5 7Displaceme What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?Thank you.
View 5 Replies
View Related
Jul 24, 2013
I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.
So it looks like this..
Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22
I tried doing it by two methods.. neither of which are working.
In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.
View 1 Replies
View Related
Jun 19, 2006
:confused: Hi everyone,
I have one table that I want to query. It's a basic table that contains:
MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100
1264435, 300
1264430, 200
1154435, 100
1154435, 219
1294430, 983
2010, 845
........ so on and so forth
Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:
MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)
1264430and1264435, 300+200
1154435and1154435, 100+219
etc etc.
i tried using simple sum and group by but does NOT work:
SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY
It does NOT ROLL-UP the 4430 and 4435 together.
QS: do i need to use VBA for this?
QS: do i need to have subqueries?
QS: please help?
Thanks!
View 4 Replies
View Related
Mar 28, 2013
I have a report I am grouping by a category, and I want to put the total number of records in that category in a textbox in the group footer.
How would I go about doing this? I have searched high and low with no avail...
View 2 Replies
View Related
May 5, 2015
I have created a database that is hosted on our company sharepoint. The database contains subcontracts with resource information by geographic location and status. I have built queries and added them as reports to the web app but I need to figure out a way to sum totals on the queries. For example, one report lists all resources of a particular type by 4 different zones and 3 different status. I need to have the bottom row show the total resources.
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 13, 2013
Im working in Access 2007.
So i have query based on 1 table that populates a Form. The primary key for that table is Entity ID. Therefore once the query has been run I have multiple records that i can scroll through in my form distiguished by their Entity ID.
I have a second table that has a Entity ID column, AFE Available column, and many others. The primary key for this table is called Match ID. This table contains records that have the same Entity ID.
My goal is to display on the form the Sum of the "AFE Availible" for each Entity ID. so as you scroll through the records the Entity ID is changing and you are able to see a the Specific "AFE Availible" Sum related to the current Record showing on the form.
I couldn't figure out a way to have a query based off both tables where the records are only uniquely defined by the Entity ID in Budget Info. What was happening is the query was displaying all the records that had the same Entity ID because of the AFE Spent table. That way when you scroll through the records the form shoes records with the same entity id.
Maybe im doing it all wrong and you dont need the tables attached to the same query. That would make it easier i think. So you would have two queries populating different text boxes on a form. Is that even possible?
View 5 Replies
View Related
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
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
