Selective Count
Dec 1, 2005
Hi All,
I've created a table that take the results of a questionaire, with
0 = No
1 = Yes
2 = N/A
For each question I want to count the number of Yes, No and N/A in one query. I can get the result for one question by using Count with the critera set to = 0 or =1 or =2, but can't do it for multiple questions in one query and I don't really want to create 33 query for each answer and then join them.
I have attached the table that I'm using cos I'm not usually very good at explaining things.
Thank You
View Replies
ADVERTISEMENT
Jul 6, 2005
I'm working on an inventory database for video tapes. Each tape has a Catagory, Sub-title, Sub-Sub-Title, Professor, Location, and Format. What I'd like is to be able to do is select a catagory from a lookup, and only the sub-titles related to that catagory appear in the sub-title lookup, not all sub-titles in the database. Following suit, I'd like the sub-title I select to bring up a limited list of sub-sub titles. I'd like a lot of things with this database. Unfortunately I have no clue how to go about doing this, which doesn't help me much. I've tried six different ways so far of attacking this, and nothing works (I've been at it for about a week). Does anyone have any suggestions, solutions, reccomendations, whatever? Any help would be appreciated. Thank you in advance.
View 1 Replies
View Related
Apr 7, 2007
I have a query that has the follwing fields:
Gross Payment :Currancy
Tax Discount: Boolean (Yes/No)
I want to create a new field called Tax Deducted that if the tax discount is no, the Tax deducted = Gross Payment * (22/100) but if tax discount is yes, then Tax Deducted = 0.
I'm not sure if i'm supposed to do this in a query or in a report but that's what i need to do. I ask if anyone can tell me what i need to do so that i end up with something like this:
Person A £29000 Yes 0
Person B £10000 No 2200
View 2 Replies
View Related
Jan 12, 2006
Happy New Year Everybody
Can Anyone help me with this please
I have a Employee Table with the following columns
EmpID
Name
Address
Telephone
PostCode
Spending
What I want to do is to update the spending column, for eg. I have the following data in spending
1233
1555
1665
I want to set the last 2 digits of the figure to '00'?
Can anybody tell me how to do this please!! I am desparate for it!
THanks
Si
View 4 Replies
View Related
Jan 12, 2006
Happy New Year Everybody
Can Anyone help me with this please
I have a Employee Table with the following columns
EmpID
Name
Address
Telephone
PostCode
Spending
What I want to do is to update the spending column, for eg. I have the following data in spending
1233
1555
1665
I want to set the last 2 digits of the figure to '00'?
Can anybody tell me how to do this please!! I am desparate for it!
THanks
Si
View 3 Replies
View Related
Sep 24, 2007
Good afternoon,
I am trying to figure out how to create a cumulative sum field for a test report I am working on. On a very simple level, I can run queries to get my data into the following format:
Test # | Article # | Test Time | Test Parameters
1 | 1 | 8 | A,B,C
2 | 2 | 5 | A,B,C
3 | 2 | 7 | A,B,C
4 | 2 | 9 | A,B,C
For each test number, I want a separate report page showing the article number, test time, and test parameters. Also on each page, I want to display the cumulative test time for all test numbers up to the displayed test number, but only for the article used in that test.
For this example, page three would have test number 3, article number 2, test time 7, test parameters A,B,C, and cumulative test time on article 2 of 12 (5+7), while page four would have a cumulative test time on article 2 of 21 (5+7+9).
How can I convince Access to do this for me? So far I've only been able to have a cumulative test time for all test articles, not just the test article related to the test number. To make this more complicated, Access needs to be able to have another article added at any time without having to rework the code to create the test reports.
I appreciate any help you can give me that will get me on my way.
Thanks!
Erik
View 4 Replies
View Related
Jan 11, 2006
I have a two level security system to protect my date. The first uses the MS Access security, the second is a simple login system wherby a user enters their name and personal password to gain a customised view of the data eg if the user is me, I can see all tool bars, other users cannot. What I would like to be able to do is write a query wherby I can use the users login details to diplay only their data. An example of the script I built is as follows
'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD
If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" & Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If
Is there a way of including a select criteria like the following
SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"
I have tried several differnet versions for this syntax without success!
Any help would be greatly appreciated.
Regards
Peter
View 3 Replies
View Related
Jul 2, 2013
I have a DB with 1000+Records. Each record has a "History" field showing the date I last visited (about 20+Visits each). When I re-visit I print Report (A4 size) which gives me the address and previous visits, however I only want my report to show the last 6 visits.
I have tried both a Query & Subform and changed the "Top Values" from ALL to 6, but this only gives the last 6 visits from all records. I need to see the last 6 visits for each record.
View 1 Replies
View Related
Aug 22, 2014
I have a table 'Project Details' to which I have recently added a new field 'Last Invoice issued'. This field is a text field with a lookup and will default to 'No' for new records being added. When the related record is updated in my 'Invoices' table, I have set up a query to change 'Last Invoice Issued' to yes. This is all working fine, but it requires all the records in 'Project Details' to contain 'No' already in order for it to work. As there are over 1000 records in 'Project Details' I made a list in Excel of the right number of no's and copied it in. Most records were fine with the exception of 127. I can edit any of the other fields from the 'Project Details' datasheet or my data entry form, but when I try to edit one of the 127, I get a DataType mismatch error. I can't understand since the records are all in one table and have exactly the same DataTypes set up for each field, why I can edit some and not others? Is there some other place where DataType can be set up that may affect some records and not others?
I have tried to copy my table to post here but when I do that, I get a 'Index or Primary Key cannot contain a Null value' error due to the (New) record having the default values already in but the primary key not being filled until I save,.
View 2 Replies
View Related
Dec 10, 2013
I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having hierarchy levels, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will form each distinct record.
In each year there are 2 sampling seasons. In each sampling season there are up to 20 dates on which samples are taken. On each date there are up to 50 sample stations recorded. And at each station up to 20 species can be recorded.
In order to enter each species caught in a survey, the end user will potentially have to fill the season box with an entry 40,000 times. For each season there will be 20,000 date entries to be made. For each date, 1,000 station code entries, and 20 species entries for each station. What I would like to happen is this.
When the end user is entering the data, he or she will be working through the hierarchy from the ground up. Ie., they will enter a particular species' data (abundance, weight, length etc) at a particular station on a particular date during a particular sampling season. The next entry will be a different species at the same station on the same date during the same season. I would like the duplicated information from the previous record to be copied across onto the new one so that all the end user has to do is enter the species name, abundance weight and length data.
Once they have finished entering all the species data for that particular site, they can move onto the next site which they would fill in the data themselves for the station code. So, in the hierarchy, the first button would copy all the previous data except the species name and associated weight length abundance data. The next button would do the same, except leave the station code blank. The next button would leave the species name, station code and date blank. And so on up the hierarchy. The end user would be able to selectively choose which data is copied over by using these buttons, saving lots of potential extra work doing repetitive entries.
View 1 Replies
View Related
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
Aug 16, 2015
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"));
View 9 Replies
View Related
Sep 20, 2005
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.
View 2 Replies
View Related
Sep 5, 2006
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
View 4 Replies
View Related
Sep 12, 2006
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 Related
Sep 10, 2007
So 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??
View 6 Replies
View Related
Apr 20, 2006
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......
View 6 Replies
View Related
May 28, 2006
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
View 2 Replies
View Related
Oct 25, 2006
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.
View 5 Replies
View Related
Jan 16, 2007
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?
View 3 Replies
View Related
Mar 14, 2007
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
View 2 Replies
View Related
Feb 29, 2008
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!
View 8 Replies
View Related
Apr 29, 2008
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?
View 4 Replies
View Related
Feb 18, 2005
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.
View 3 Replies
View Related
Jul 1, 2005
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 Related
Oct 23, 2005
I 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 Related
