I have a questionnaire table that has three columns, each column containing a textural answer to the column header question. Each answer is one of six textural choices that are specific to each column.
What I want to do is summarise the information so that I can read off how many of each answer there are e.g how many answered 'x' in quesiton 1, how many answered 'y' in quesion 2. If it helps, I don't require a summary that might detail how many answered ''x' to question 1 AND 'y' to questions 2. I just need column summaries.
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name 1 xx yy KPP USA John 2 zz kk KPP USA John 3 ss ff TLL USA John 4 ww qq PPO Italy Marco 5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT COUNT(DISTINCT department) AS NumberOfDifferentDepartments FROM people GROUP BY name;
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code: TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID] SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID] FROM Register GROUP BY Register.National PIVOT Register.P_Gender;
I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:
PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime; TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode SELECT tblAttendance.EmployeeName FROM tblAttendance WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime])) GROUP BY tblAttendance.EmployeeName PIVOT Format([AttendanceDate],"Short Date");
This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code: Private Sub Command11_Click() Dim dbs As DAO.Database Dim rst As DAO.Recordset
Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
Hi. I have a question I'm hoping someone can help me with. I would like to take data from multiple columns and put the data into one column. Additionally, I do not want to exclude any data (union all) and I would like to group the resulting union by another field. For example:
So far I'm using the following SQL. What do I need to add or change to get my desired result of grouping the unioned depths by the 'sample event' field?
I appreciate any help anyone may have to offer. Thank you.
SELECT Depth1 AS Depths FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth2 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth3 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth4 FROM Depth_Velocity_Substrate_Correct Union all SELECT Depth5 FROM Depth_Velocity_Substrate_Correct
I have a Access 2003 file and I want to filter anywhere where there last name is "expired" and change the column first name to say "no". How do I do that?
I need a query that could split data into 2 different field. The table pic is attached for your ready reference. I tird with Left & Right funtions with no results.
Any one can help me please.
Table name is Agcad and field on which I need to split is Board_Size. The problem is Board_Size data is sometime in 3 digit (first half part) and sometime second half part is in 4 digit. It is a text data type field.
I have a csv file created from e-mail gateway app for incoming and outgoing mails. I am trying to get e-mail stats from this file. One column contains "To" data. In the "to" field I may have more than one recipient per line (one mail cc to many) eg. user1@domain.com, user2@domain.com, user3@domain.com etc. in the next line I might get user2@domain.com. So for this my stats would say I recieved 2 mails (the line counts) but 3 recipients. Here's what I need. Need the data to be sorted to show only the first recipients name and do a count of how many mails that recipient received. In the outgoing it works nice, because I only have one sender in the "From" field there I just run the query wizard and presto. This doesn't work to well in the incoming since the data differs as the "To" recipients are'nt all the same.
I have two separate columns in my query but they contain the same kind of data. These two columns data should match but some of the data doesn't. Can I write a criteria that will show me only the rows that do not match.
DUTYPOSC 11B111B1 11B111B1 11C111B1 11B111B1 92Y111B1 11B111B1 13F121B1 For example, I want the query to show me only the rows in which the data in the Duty column doesn't match the POSC column.
I have qrys that show current info and also repeated info. A request has been brought to me to now count how many times the repeated data has been repeated.
The data is updated daily, so the repeated record would need to increase in the number of it's occurances by 1 each day.
Any guidance on how to accomplish this is GREATLY appreciated! :)
Sorry to come to you with this one but my sql is terrible......
I have a recordset named Leave reason. Its numerical and can cover some 20 differing numbers. These are not always sequential.
Ive tried a crosstab - not good enough, Ive tried a form with =Count(IIf([Leave reason]=9,0)) in it, this works, but, it requires a text box for every possible number that could occur! To labour intensive that.
So the bottom line is is there a small piece of sql that I can add in to the queries sql view to handle this issue, if there is can someone just lay down the code (yes I know Its naughty to ask) so Ill learn and be able to do it in some 30 other queries of the same nature.
How do I count only filled out data in a query? I am using the count function and it counts blank and filled out records, I just want the filled out ones.
I'm using Access 2010. I have a report that summarises students and the number of courses they are attending after a set date. My query lists name, course date, course description, the count being on the course description. I thought it was working until I noticed that students are listed twice if they attended courses on two separate days.
For example Liz 4 Liz 3 instead of Liz 7
I have moved the count to other fields but it then doesn't show any students at all when I run the query.
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
I am having trouble arranging my data from a record row into a query that will give me columns for the pivot charts that I need. My guess is that I need some sort of a complicated SQL statement, and I know almost nothing about SQL. I have expressed the problem better, and with graphics, here: http://www.olypen.com/bhardin/RGB/RGB.htm. I would love to hear the solution to this aggravating :mad: problem.
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.
I need to categorize 35,000 cells of data in a column into 3 different groups (A,B, and C). Each cell will have something like "G00001" or "G00525" in it, but depending on when it was added, I want to change it to "G00001A" or "G00525C". I created a select query to show only the G0000's column and the column that indicates AB or C, but i don't know how to add the letters at the end of my G0000's. Do I need to use an update query or can I do something more simple? Also, if I need to use an update query, how would I build it to do what I need to get done?
I am want to validate the data in a table column as per the data in a corresponding column in teh same table.
Ex. I have a table with column "Frequency" of datatype Text. And other 12column are in the name of the months. i.e. Jan, Feb etc of datatype "Yes/No" in checkbox format. Default value as false.
I have only 4 different values i.e. Yearly, Half Yearly, Quarterly And Monthly.
If the value is Yealy then I should able to change the value on Only "Jan" from False to True.
Similarly If the value is Yearly then I should able to change the value on Only "Jan" & "July" from False to True.
Same holds true for Quarterly changing the values only of March, June, Sep & Dec from false to true.
OK, I have a truck mileage database with a columns for monthly "StartingMiles" and "EndingMiles" - At the beginning of each month I want to automatically move the entire content of the "EndingMiles" column to the "StartingMiles" so I can start a new reporting month. I'm assuming I can use an Update Query to do this - correct? How do I setup the Update query to perform this? Or is there a better way?
Have a column titled Total Funded that lists numeric dollar amounts. I want to suppress the records in that column whose value is 0. I did this: IsNull ("0") and ended up getting exactly what I don't want -- that is a query that returned only the 0 funded records. Those are the records that I want to suppress.
