Grouping COUNT() Help
Oct 10, 2006
I have a set of evaluation response data. There are a number of question (like 20), and each respondent answered the question on a scale of 1 to 5. Thus I have a table Responses where each column is a question and each row is a Respondent's answers. Each cell has a value between 1 and 5 (inclusive), or is possibly NULL.
What I want is to be able to list out the number of people who responded with a certain evaluation for each question. So it would list the number of people who gave a 1, a 2, a 3, and so on for each question. So I'd get something like:
---------Q1 Q2 Q3 Q4 Q5 ...
-1- x x x x x ...
-2- x x x x x ...
-3- x x x x x ...
-4- x x x x x ...
-5- x x x x x ...
Ideally I'd like the questions to be on the rows and the numbers as columns, but I can just do the transposition when I make this as a report.
Any ideas of a SQL Query that will get me such a table? I'd really like to avoid VBA if possible (I'm writing this database to be used/maintained by a non-programmer), and I'd like to not have to develop like 20 different subreports in order to print out this information.
Thanks.
View Replies
ADVERTISEMENT
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
May 2, 2006
I have created an invoicing system for a CD shop
There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
View 3 Replies
View Related
Jun 21, 2005
Hi, I have a query which numerous fields, and I need to make a report based on the query. However I need to group 3 fields in the query and press the sum button on 2 of them, Qty and Total Price (which is qty*price)
I need to do this so in the report when a particular wine is purchased more than once, instead of repeating the peoples name who bought the wine, it will only show 1 and automatically add the rest to the total price.
I dont know how to group within a query, can someome please tell me how? Thanks.
View 1 Replies
View Related
Jul 6, 2005
Hi to all;
I have one code of 6 digits; each digit refer to a group; first 2= product group; beverage; live animals,
.itc (24 product groups), the 3ed digits= food product, the 4th-6th digit= detail product group; vegetables, fruits,
.,the rest of digits refers to product name, carrot, apple,
itc , example 070511
How can I use the query to sum the product value at different group level; example the first 2= product group, ..?
Do I have to split my code to 6 cods to do my calculation?
Thank in advance for help
majed
View 1 Replies
View Related
May 2, 2006
I have created an invoicing system for a CD shop
There is a transaction table which has each individual cd sold as a seperate transaction. Each transaction has an order number, so there can be more then one cd sold per order, but they all still have their own record in the table.
im now invoicing each order by mail merge based on a query that finds all the details on every transaction but wht ive found is that the mail merge puts each transaction onto a different page even if its from the same order number as another.
does anyone know how to group each order in the query so that all the items in one order come in a single invoice?
View 7 Replies
View Related
Sep 21, 2004
Hi all
I am trying to find a way of finding the number of a group of sessions as a percentage figure. e.g. total number of clients attending 1-3 sessions = 20%, 4-6 =15%, 7-12 =21% 1-2years = 8%, etc. and entering this calculation in a report.hope you can help.
Dave
View 9 Replies
View Related
Jul 18, 2005
Question on grouping within Access Reports:
A simplistic view of the report I'm trying to generate is as such:
Company Name - Company Description - Employee
I am grouping by Company name, and I am hiding duplicates of Company Description because they can be long. I also have the Company Description as Allowing to Grow.
The problem is that the first record gives the company, the description, and the employee name on the first line just fine, but the second employee name won't be listed until the Company description ends. When the description is about a paragraph long, the other employees look very seperated from the initial record.
Any way to fix this?
View 1 Replies
View Related
Mar 9, 2008
Hi,
can form objects be grouped? i currently have numerous buttons on a form that are shown according to a button selection. my current code makes all buttons visible / invisible singularly but i wiondered if they could be grouped/ named and the get the code to make the group visible / invisible?
many thanks,
NS
View 4 Replies
View Related
Sep 19, 2005
Can you help me.
I have about 2000 records with Part Number XXXCOGxxxxx. This Should be C0G. Ex:
SPN
RPE122COG101K
RPE122COG681K
RPE122COG101K
RPE122COG681K
GRM40COG100J
GRM40COG100K
GRM40COG101J
GRM40COG150J
GRM40COG220J
GRM40COG221J
GRM40COG271J
GRM40COG470J
GRM40COG471J
GRM42-6 COG100J
GRM42-6COG220J
GRM42-6COG472J
What to write a query to change just the COG portion. Can you tell me the formula?
Thanks
Chris
View 1 Replies
View Related
Oct 23, 2005
I am trying to group time entries so that I can say, between 8AM and 10AM there were this number of calls. I have a field called Time_Assigned with numberous time entries (such as 08:15:33 AM) corresponding to another field called Incident_Type (such as medical). The data spans a whole year so I have several thousand time entries and I would like to show how many incident types occur between such and such hours. Thanks again for everyone's generous help.
View 2 Replies
View Related
Dec 6, 2005
Hi,
I would like to know how i can group numbers into bands.
e.g
Col 1
1
2
3
4
5
6
How do I group the above into groups of i.e. 1 - 3 and 4 - 6, or group in regular intervals like 1-2, 3-4, 5-6?
Regards
View 3 Replies
View Related
Feb 24, 2006
Hi all,
I have a db for logging meeting action points. Each action point has a description and owner. I'd like a query/report which will produce a list of action points grouped by owner (easy), but with a particular owner's action points at the top.
Essentially, rather than do a GroupBy and ascending sort, i need to specify the first group that is displayed. I don't care in what order the other groups appear.
Eg.
Action Point Owner
1. Task 1 DC
6. Task 6 DC
8. Task 8 DC
3. Task 3 AG
4. Task 4 AG
So, above if i just did an ascending sort, the DC records wouldn't be at the top.
any ideas?
El.
View 3 Replies
View Related
May 16, 2006
How do i group the following records
ID Surname Pack
29679Minican 1
29679Minican 2
27818Oliver 1
27818Oliver 2
27818Oliver 3
so its will show ...
27818Oliver
29679Minican
... on a spreadsheet
View 1 Replies
View Related
Jul 6, 2006
I have a table which stores meeting room booking information in half hour slots.
If someone books a 2 hour meeting then 4 records would be produced one for each half hour. I want to produce a query which will group the data by room (ScheduleID) showing the max and min times (ie the initial start time and final end time) for each event and the event details. The table structure is as follows
ScheduleDetailsID, ScheduleID, CustomerID, ScheduleStartTime, ScheduleEndTime, meeting purpose
And the query I have tried is as follows:
SELECT [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, Min([Schedule Details].ScheduleStartTime) AS MinOfScheduleStartTime, Max([Schedule Details].ScheduleEndTime) AS MaxOfScheduleEndTime, [Schedule Details].[meeting purpose]
FROM [Schedule Details]
GROUP BY [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, [Schedule Details].[meeting purpose];
Can anyone tell me where I have gone wrong. It does not group the data as I want it to ie by room, then time, with only the initial start and final end times. Thanks in advance. Peter
View 3 Replies
View Related
Aug 1, 2006
I have a query which has many sums and counts on things like "Company name", "region name" and "Development Name".
I'm using this query for a report to do lots of percentages with, but now i need to filter this also by a date period.
So the user choses "Alex Homes" as the company name and then "July 2006" as the reporting month, and i need all the sums and counts to stay the same and only count/sum the records in the chosen month.
I can't seem to think of a way to do this.
If you need more info then ask.
View 4 Replies
View Related
Jan 8, 2007
I am having a problem that I hope has a very simple solution that I am somehow overlooking.
I have simplified my query for the purposes of this question. I have a query that only get the date and amount of a transaction. I want to group the information by date and have the transaction amount summed. For some reason it will not group by date.
Here is my query displayed in SQL:
SELECT Transactions.Date, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Transactions.Date
ORDER BY Transactions.Date;
And here is a sample of the data returned:
DateSumOfTransTotal
12/13/20055.12
12/13/20055.12
12/13/20055.12
12/13/20055.12
12/15/20051.15
12/15/20051
12/15/20050.12
12/15/20056.56
12/16/200519.14
12/16/200512
12/16/20058.16
12/17/200511.11
Why will it not group all the 12/13 or 12/15's together? I have done other queries like this and not had this problem. What am I overlooking?
Any help is very much appreciated. Thanks,
View 13 Replies
View Related
Jul 31, 2007
Hi
I have this table
date, error code, user
i need the output to be :
User, Error Code, Month/Week1 Count of error code, Month/Week2 Count of error code .....
basically how do i make error code field as count with each week 1-4 for the month?
Format([AuditDate],"mm-ww") create week but in 1-52 weeks not 1-4 for the month. Also need something like ' count if week = 1'
Hope this makes sense.
Thanks
View 1 Replies
View Related
Aug 10, 2007
I need to create a report that groups duplicates and totals the quantity.
Here's an example:
Current Report:
Product# Type Qty Cost Total
i123 pen 2 1.00 2.00
a987 paper 3 1.00 3.00
i123 pen 1 1.00 1.00
a987 paper 4 1.00 4.00
What I need:
i123 pen 3 1.00 3.00
a987 paper 7 1.00 7.00
Please help......
View 2 Replies
View Related
Feb 22, 2008
I have a table GIS_Subs with following fields:
Force_No ( Foreign Key)
Subs_Dt
Block Yr
Subs_Amt (number)
Running_Total
I wish to update Running Total for each record based on previous pay sorted in Ascending.
I am able to make a running sum but then it clubs all PersonNo of same date like:
[COLOR="Blue"]SELECT GIS_Subs.Force_No AS FN, DatePart("yyyy",GIS_Subs!Subs_Dt) AS AYear, DatePart("m",GIS_Subs!Subs_Dt) AS AMonth, DSum("Subs_Amt","GIS_Subs","DatePart('m', [Subs_Dt])<=" & [AMonth] & " And DatePart('yyyy', [Subs_Dt])<=" & [AYear] & "") AS RunTot
FROM GIS_Subs
GROUP BY GIS_Subs.Force_No, DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt)
ORDER BY DatePart("yyyy",GIS_Subs!Subs_Dt), DatePart("m",GIS_Subs!Subs_Dt);COLOR]
How can I calculate it for each Person seperately?:rolleyes:
View 3 Replies
View Related
Aug 22, 2006
I'm in the process of setting up a form, and I have 4 yes/no fields that need to be in it. I want to group the fields so that only one of the yes/no fields will be able to be selected. The fields are: Pass, Fail, N/A, and obsolete.
I tried setting up an option group, but I can't seem to get the information to filter back to the table that is capturing the data.
Any help? :confused:
View 1 Replies
View Related
Nov 3, 2004
I have a report with set up as follows:
Group Header: Division
Group Header: Subdivision
Detail
Is it possible to get the Division to only appear once per value?
e.g.,
Division
Subdivision
Detail
Subdivision
Detail
NEW Division
Subdivision
Detail
etc.
Right now the Division appears before the subdivision every time, even if the division is the same as before. I did change "Hide Duplicates" to "yes", but that didn't help.
View 3 Replies
View Related
Jul 27, 2006
I have a query that is sorted on the officename and the lastname.
My report is grouped by officename
I can't get the lastnames to be sorted alphabetically - I'm not sure why, since they are in the query.
What can I do to make it grouped by officename and lastname sorted?
View 1 Replies
View Related
Jan 14, 2008
Hi, Last piece of advice i got from here was excellent so thought i would try it again,
I have records that show delivery days and postcodes some post codes have more than 1 item going to them on several days through the week i was wondering if i could group the same postcodes together so it only showed 1 record instead of a possible 15 but only those delivered on the same day, Is this possible?
View 3 Replies
View Related
Jan 14, 2008
Hi, Im not sure how to ask this but i need to add together the total amount of times a see a persons name in a field i.e a person signs for 5 boxes with 5 signatures i need to be able to add those together so it gives me a value of 1 is there anyway i can do this?
View 6 Replies
View Related
