Count Of Records That Make Up 80% Of A Sum

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 Replies

Trying To Make A Query To Count Records

Sep 21, 2005

I am trying to make a query that will count how many times a word shows up in a field.

I am still new to access and am having trouble with this one.
Any help will be greatly appreciated.

Thanks in advance
Jon

View 1 Replies View Related

Count Records Problem. Display Field Even When Count Is Zero.

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

Make Reports Visible When Reach A Certain Count (60 Days)

Jul 31, 2013

I have 2 tables on one report, 1 is visible, the other visible = no, Is there a way to make that visible = yes when you reach a certain count (60days)...

View 1 Replies View Related

Make New Records If Field Changes

Oct 2, 2012

So I have an field which you can check off. Once checked off it should generate a new record with almost exactly the same data. How can I do this?

View 1 Replies View Related

Count Of Records

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

Count Y/N Records

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

Count How Many Records????????????????????????

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

Help! How Will I Count Records I Want?

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

How To Make Button Generate Records

Aug 4, 2005

While there are lots of detailed issues discussed and resolved here, my problem is at a much higher level. Here's what I want to do.

I want to generate records in a file based on the user entering some 9 different values. The first two values will be used to get records from file A (they are a partial key). The values from the records in file A, plus the other values entered by the user will be used to generate records in file B. I hope this is clear enough.

I have created a query which prompts the user for each field and does the job just fine, but I want to replace the 9 individual prompts with a single form which allows the use of combo boxes for the partial key and a date picker for the single date field.

I've fluffed through creating a form with unbound controls to suit and even worked out a form of cascading combo box for the partial key, but I am struggling to find the best way to make a button at the bottom generate those records.

A nice optional would be to then display the added records (i.e. another query I guess).

Can somebody give me some pointers as to how to approach this? I've done very little in the way of Access programming, being mostly an SQL guy.

Thanks.

View 3 Replies View Related

Forms :: Make Two Records That Are Similar?

Mar 3, 2015

I am creating a simple inventory db. I have a form that we enter into the table inventory. Right now we enter in everything once as inventory, then again to out to coating or ship to customer. I’m looking for the code to create a button enter the record twice but make two fields change.

So in short terms I want the form to enter a record with all the data as displayed. Then create another record with all the same data but the quantity would need to change to a negative and the Status would change to “Coating”.

I know this code won't work but this is what I want to do.

Private Sub Command78_Click()
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31

[code].....

View 9 Replies View Related

Count Even Numbered Records

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

Count Records With The Same Value In Column B

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

Count Duplicate Records

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

Count Records In A Query

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

Count Of Null Records

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

Count Unique Records

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

Count Null Records AND......

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

Count Certain Records In A Form

Jun 13, 2005

In the Form Footer I use a box with '=Count(*)' to count all records.

But how do I count only those records that have the value '?'
in the field 'MailRead'.

Thanks for the help.

View 3 Replies View Related

Count Of Records In Subform

Nov 4, 2005

I have a form that has a subform in it, which is another table in it. I want to add the number of fields that are in the subform. What is the best way to do so?

I would like to write a VB script so that a field in the master field = the count of the records in that subform. Any help is greatly appreciated. Thanks.

View 7 Replies View Related

One-to-many - For Each One, Count Corresponding Records On Many Side

Mar 16, 2006

I have data which has a one-to-many relationship and I would like to display in a form (or webpage) both the one and the many side of the relationship and to display a count of the number of records on the many side which correspond to each individual record on the one side.

I attach a zipped .mdb file containing dummy data which includes a pair of linked forms showing where I've got to so far with implementing the idea. If the Words form is opened first, the linked form, Associations can be opened by clicking on the Associations button.

(Incidentally, I need to do something to control where and at what size the two forms open on screen. I think I've seen from a post on this forum that what I need is DoCmd.MoveSize.)

The problem I'm hoping someone will help me solve is how to get the count of the records on the many side to appear only once and, preferably in the Words form, not the Associations form. If you view the forms in the attachment, you will see that at the moment the count appears once for each item on the many side in the Associations form.

I'm a beginner and am unsure if the solution is very simple, perhaps involving referencing a hidden control in the Associations form with a path to that control comprising various identifiers joined with ! and or . , or perhaps using a simple snippet of Visual Basic, or whether I'm asking for help with something which is actually difficult.

View 4 Replies View Related

Count Records In Table - VBA

Mar 21, 2005

Good day,

I have a sub-routine created on the form labelled frmenrollcenter and inside this routine I want to count the records in tblec based on the value in "txtextl" textbox. Then I want to display this value in a message on the screen. I was wondering if anyone knows the function that can make this happen.

Thanks

View 7 Replies View Related

Count Duration Between Records

Oct 5, 2005

is it possible to count the time between records? example

Table set up:

LogID
StartTime
StopTime
ProblemCode

Example records:

Id -------> StartTime ------> StopTime--------> ProblemCode

1 -------->06/10/05 10:00 -> 06/10/05 10:02 -> A101
2 -------->06/10/05 11:00 -> 06/10/05 11:02 -> A131

i want to count the time elapsed between, ID 1 ending and ID 2 starting, can ido this?

if so how..

the duration i am looking for in this case would be 58 mins

cheers

Andy

View 2 Replies View Related

Count Records In Table

Oct 23, 2006

Hello,

My database has two tables: MASTER1 and MASTER2. They are used for a list of clients. They contain general info like names, address and SSN.

I use MASTER1 to add new data while MASTER2 (via an append query) to save some data which does not need to be destroyed.

For both tables I have designed a form and each table has the ID as PK and a field SSN.

I would to add a function that while I am in form MASTER1, it could count via a control (txtbox) the number of records with the same SSN of the active form (MASTER1) in table MASTER2.

Any help? Thank you.

View 6 Replies View Related

Count Unique Records

Sep 10, 2003

Hi there:

I have a Question

In Access 2002 (XP) I have to write a query that displays unique Id's:

For Example:

CustomerID Company Name
1 ABC INC
1 ABC INC
2 XYZ INC
2 XYZ INC
3 PQR INC

The query should be 3 records.

But for some reason when I write the query in sql view it doesnot work.

Thanks,
Ashi
http://www.ringvirginia.com

View 12 Replies View Related

Reports :: How To Count The Records

May 13, 2013

I have a report where I would like to count the records. I can do this in a typical way to count the records but I'm a little stuck on this scenerio.

In my report I pick a date range of 04/01/2013 to 04/30/2013 it displays all my records. I have 3 records for 04/01/2013 what I would like is for that to count 1, not 3. So on and so forth - so it will show me at the end of the report how many times a week they worked. Is there a way to do that?

View 1 Replies View Related