I'm trying to find out the statistics of my dabase:
# of total items
# of unique items from 4 different criterias
When I put that into the queries, using the count function, it works well for 1-2, and then if I add in more into the query, it gives ridiculously high numbers for the counts, and freezes. Am I doing something wrong?
Thanks!
I will try to explain my problem as best i can and would appreciate any thoughts other people have on it, it is surely similar in some degree to someone elses previous work!
I need to produce management information on a monthly basis, one example of this type of work are an employees one to ones.
table121 contains following fields, ID scheduledDate CompletedDate Completed(yes/no)
My report/query needs to group records by the month (which i do through formatting date fields to display mm/yy), count the number of scheduled one2ones, count the number of completed one2ones, display a %.
I have played around and got this to work using querys with grouping and sums.
My problem is if the schedule date and the completed date are in different months then all of the statistics become out of sync, particualy when there are more appraisals taking place than scheduled.
Any ideas?
I am looking to come up with statistics for my volunteer tracker. I have a table of transactions that records who works each night we are building our haunted house. These transaction records record the Date, Worker and the Time In & Time Out. I would like (probably a cross tab query) that lists all the dates in the left hand column, and count the number of workers in another column, then the number of man-hours put in for that date. Kind of like this:
Date | Worker Count | Man-Hours
---------------------------------
7/30/2007 | 42 | 168
7/31/2007 | 31 | 124
8/2/2007 | 28 | 120
8/3/2007 | 17 | 68
Any help on this would be awesome. I have no experience with cross tab queries whatsoever.
SW
Sorry if this is a question asked a lot but I need help with statistics in acces.. Im doing a booking system in access with customer and booking/bill tables. I have an IF statement to work out price (in a query) for the booking or which displays error if booking cannot be made. The query runs when a button is pressed in the form. The booking price is worked out by time (morning, afternoon, evening) and special (gold wedding anniversary, extended evening etc) which change the price. The date and booking time fields are set together as primary key (composite or whatever key...). The system works monthly. I need a system to find out how much money has been made, how many bookings have been made (and how many could have been made), how many are regular bookings (there will be a regular booking yes/no field). I have probably given too much information but I need to know how to do this more automatically then copying and pasting info into excel and doin equations in it. Also in excel I would just have to presume month = 30 days or manually type in. Is there any code to copy the data into an excel spreadsheet with predone equations automatically? or is there a better way to do this? that isnt too difficult. I have only just started looking at VB so dont know much. PLEASE HELP! PLEASE!!!
View 1 Replies View RelatedThis is either very simple or very complex, I haven't figured out which yet.
I need to know the number of tables in my database and from each table I need to know how many records are in each table. Ordinarily I would just count the number of tables then open each one up to get the number of records, but I'm working with 100+ tables so that's not very practical.
If it makes a difference, the tables in question are linked tables. I don't imagine that is relevant, but thought I'd mention it.
Thanks!
I have a database which has numbers for different statistics and i would like to be able to search, for example, the past 10 weeks and find out how many time a certain number has been recorded.
View 1 Replies View RelatedIs there a way of formulating statistics at the bottom of a report?
Heres what i have.
The report pulls Rank, Last Name, First Name, Assigned weapon, Weapon qualification date. After 6 months i use conditional formatting to highlight the soldiers qualification date red. Im in the military that's why im tracking all this, but I need figures to report to higher, and at the bottom i would like it to show, "#Qualified", "#UnQualified","% Qualified", "% Unqualified", "#Expired","%Expired"
I am trying to create a statistics function on a database. The idea is that the user will enter a start and end date and either search for all records during that date range or select a client from a combo box and only view records for that client during the selected dates.
After doing quite a bit of searching, it seems that I should be using a wildcard in the criteria however I cannot get this to work. The code I have been playing with is:
Code:
=Nz(Forms![Statistics]![ClientCombo] ,"*")
I have changed the "*" to a specific client number and if the combo box is left blank, results are shown for that client only and if a client is selected from the combo box then the selected client is shown. The only thing I cannot get it to do is show all entries if the combo box is left blank.
I am building a database to enter staff phone statistics. As an example my fields would be - Name, Date, Staffed time, Available time, Aux time and then calculated fields to show the percentage of time i.e %Aux, %Available etc.
My problem is the formatting of the times entered as they are duration not time. Say staffed time is entered as 08:00:00 for 8 hours and Aux time 03:57:21. The only format I can see to suit is date time but then Access takes these entries as 8am and 3:57am is there a way to change this to work as duration hh:mm:ss?
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.
I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.
BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797
The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.
Right now this is what i get
BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014
This is the output i would like
BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014
Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?
current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));
Hi,
Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.
I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.
All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.
Can I do a count of the count or do I do some sort of sum of the count results?
I've tried everything I'm capable of as a newbie, and I'm not having any progress.
Any help appreciated.
Hi all,
Do someone have a way to do a count function in a Reports to let the report showing the details at the same time do the accumulating of the number??
I had try so many way but it not work~~!!! Pls i need someone help cause i stuck my report there without the accumulating of the number, Thanks.
Regard,
alex
I having trouble trying to figure out how i can get totals in a report, the report picks the information up from a query i have set up. The report shows various data to do with Grades awarded as part of an audit process (1-4), i want to put a total in the report to count how many 1's, how many 2's etc. i not very experienced with access so can anybody help me with this.:confused:
View 1 Replies View RelatedSo i have 2 fields(124816 records)
IMKEY, DOCBREAK
IMKEY is like an ID. And docbreak is like a page counter, where some records are empty and some arent(seperated by D's and some C's). im trying to find out 2 things
1> Count how many values are within each group in DOCBREAk.
example:
DOCBREAK DOCCOUNT<--trying to figure out
D 3
<EMPTYREcord>
<EMPTYrec>
D 1
D 4
<EMPTYrec>
<EMPTYrec>
<EMPTYrec>
C 1
I tried this query, but it counts everything, i just want to count how many values are within a group(C's and D's
Select COUNT(DOCBREAK) from Jan003;
in excel, i could of done it, but since excel has a limit how many rows it can support, i had to do it in ACCESS...
2>in the IMAGEKEY column, since DOCBREAK seperates and makes groups, im trying to as well get the beginning number and the ending number(1st and last number).
ex
IMAGEKEY Beg End
1 D 1 3
2
3
4 D 4 4
5 D 5 8
6
7
8
9 C 9 9
i did it in excel, but then again, for my personal use, i would like to know how to do it in access
heres how i got the 1st number in excel(A=imagekey, and B=Dockbreak)
=IF(B1="D",A1,"")
end number (C=DOCCOUNT)
=IF(C1="","",OFFSET(A1,C1-1,0))
can anyone help me out??
I have 11 sites and I'm trying to missed visits at each site.
Currently I'm counting all non-missed visits at the site, and in the report I'm subtracting that number from Total # of patients at the site. This works fine - but there has to be a solution to the more "direct" method described below.
If I count up the actual missed visits and one site does not have any, the result only shows 10 sites and their totals. If the total count of missed visits = 0, is there a way to have access return that result?
I've tried changing the join relationship, inserting IF ([CountOfDay 30 Missed]) Is Null (or is 0) THEN .....
I know my naming conventions suck - I'm learning as I go, and I'm afraid to go back and change things now. Lesson noted for my next DB......
Hello,
Alright heres a problem for you's.
I have a table which holds roster information for staff. For each day in a 2 week period, there are 3 checkboxes, 'am' 'pm' and 'nt'. I need to perform a count on how many are 'am' 'pm' or 'nt'. For example, if 3 staff members worked on monday week 1 pm, i need it to return '3'. Problem is i am unsure how to perform a count on multiple fields?
Cheers
Derek
I'm struggling with this one.
first, I do a total query to retreive the frequency of a certain attribute
eg This produces
Customer Depot Order
CustA North 12 orders
CustA South 8 orders
CustA East 10 orders etc
CustB North 9
CustB West 11
CustB East 10
So now I want to retrieve for each customer, the Depot with the highest order count
ie
CustA North (12)
CustB West (11)
I can't find the right structure for the second query to generate the answer, because as soon as I do a group by, I get all Depots again! Or can I do the whole thing with one query.
If there are two similar max counts, ideally, I want to return either one. I suppose I could do a dlookup on the max count to retrieve the associated depot, but this seems sloppy.
I have two tables - one is the inventor name, one is the #patents and the business unit. (in a nutshell - these are the only fields I need from each)
I need a query to return the results:
Inventor Name #Patents BU
Joe Smith 28 W
Joe Smith 4 F
what I get is:
Joe Smith 1 W.....(28 times in a column)
Joe Smith 1 F...(4 times in a column)
I've read and studied and queried and tested until I can't see. This can't be that hard. Can someone help?
Hi, I need to count how many times FarbeBC changed in value.
I don't need how many different values. That would be 4. But it only changed 3 times.
I managed to do this in excel. But can can't find out how to do this in access.
Any help from you guys?
I've Access 2000 with an ODBC connection to an SQL server.
DateEVT | FarbeBC
13/03/2007 06:14:09 | -M7X
13/03/2007 06:16:12 | -M7X
13/03/2007 06:17:12 | -92U
13/03/2007 06:18:35 | -92U
13/03/2007 06:20:53 | -92U
13/03/2007 06:21:57 | -92U
13/03/2007 06:23:00 | -92U
13/03/2007 06:24:31 | -92U
13/03/2007 06:27:24 | -92U
13/03/2007 06:28:43 | -92U
13/03/2007 06:29:47 | -84A
13/03/2007 06:31:09 | -M7X
Say I have the following database in Access:
Letter Name
(blank) Joe
c Joe
c Joe
c Sue
d Joe
c Sue
How can I know the know letter count without duplications for each unique name?
So for Joe I want it to return 3, (= blank + c + d).
For Sue it should return 1, only c is unique.
TIA!
Need help
When I try running the query below I get this error.
"Reserved error (-3025); there is no message for this error"
SELECT
totalelectricemergencies_1 = (SELECT Count(electricemergencies_1) FROM Phase_1 WHERE electricemergencies_1 ='Always'),
totalelectricemergencies_2 = (SELECT Count(electricemergencies_2) FROM Phase_1 WHERE electricemergencies_2 ='Always');
Any ideas?
I have a form, with a tabbed interface. On each tab is a subform showing a continuous form with records matching criteria.
I'd like for the name of each tab to also include a count of the items in each subform on each tab. Is that possible?
So the name of tab 1, instead of "Opened Cases" would be "Open Cases (X), where X is a count of how many cases are currently open.
Thanks.
I wonder if someone can help me with a count function. I ahve looked at the help files, but I cant understand it. Basiclly I have a number box on a form, I need to add to gether the numbers from each record to form a total.
View 4 Replies View RelatedI am having trouble counting the number of fields that contain a value greater than £0.00 for example I have five fields in a form £5.00 £2.50 £0.00 £3.00 £4.00 I have tried nz =1 >1 my current sting is =count([price]) which results in a count of 5 but I don't want £0.00 counted......
View 3 Replies View RelatedHi,
I have a question.
Suppose there is a table:
Vendor__________Code ......................
A________________1
A________________2
Dcount("Code","table","Vendor = A") = 2
It is fine.
Vendor__________Code ......................
A_______________1
A_______________1
A _______________2
Dcount("Code","table","Vendor = A") = 3
I want to count the different between the code, it should be 2.
I don't want to create the query to group it and then count the query, because the values of the rest of the fields of the table may be different.
Does any function for count different? Thanks.