Difficult query

Hi,
  I have a table called WT,contains the fields "Type of call","DateW" and 
"ID", this table is used to by users to add rows that determine type of calls 
received in a call center,I want to create a query with the following 
criteria:
1- To view number of calls received in each type per day.
2- To show the field "Type of call" in this query,even the type that wa not 
used,and to view number 0 in the count field.
3-Prcentage of each type of call .
0
Utf
12/11/2007 8:52:02 PM
access 16762 articles. 3 followers. Follow

1 Replies
632 Views

Similar Articles

[PageSpeed] 54

On Dec 11, 3:52 pm, Pietro <Pie...@discussions.microsoft.com> wrote:
> Hi,
>   I have a table called WT,contains the fields "Type of call","DateW" and
> "ID", this table is used to by users to add rows that determine type of calls
> received in a call center,I want to create a query with the following
> criteria:
> 1- To view number of calls received in each type per day.
> 2- To show the field "Type of call" in this query,even the type that wa not
> used,and to view number 0 in the count field.
> 3-Prcentage of each type of call .

1- Simple aggregate ("totals") query. Group by date, count on type.

2- (a) Build a second table Types with one column that contains each
possible type. Create a query that joins both tables on type and
change the join to show all records from Types and corresponding
records from WT. Bring in DateW, and type /from Types/. Again, group
by date and count on type. Or,

2- (b) Create a helper query Types that has WT.Type. Group by this
field. Construct a new query as in solution A using the helper query
instead of a new table. Note this will only return types that are
actually present in WT.

3- I stumbled here, thinking that DCOUNT should work, but I could not
quickly make it work. Assuming you want percentage of type by day,
this subquery approach gets the right answers:

SELECT W1.DateW, W1.[Type of call],
  (SELECT COUNT(*) FROM WT AS W2
  WHERE W2.[Type of call] = W1.[Type of call] AND W2.DateW =
W1.DateW) /
  (SELECT COUNT(*) FROM WT AS W3
  WHERE W3.DateW = W1.DateW) AS PERCENTAGE
FROM WT AS W1
GROUP BY W1.DateW, W1.[Type of call];
0
Smartin
12/12/2007 1:06:19 AM
Reply:

Similar Artilces:

junction table query
I need a query that will populate ‘tbl_rep_region_junction’ (example below) with IDs from ‘tbl_rep_location’ and ‘tbl_city’ based on the entries in the 4 code_x columns in ‘tbl_rep_location’. Skip all blank code_x without region names. Thanks for the help.. tbl_rep_location rep_loc_id repname code_1 code_2 code_3 code_4 1 smith west east midwest south 2 james west south 3 phillips east midwest tbl_city city_id name region_id 1 Los angeles 2 2 New York 1 3 chicago 3 4 dallas 4 tbl_region region_id region_names 1 east ...

Query and Duplicate Rows
Hello- I created a simple query that for some reason is automatically hiding all duplicate rows from the main table it is pulling data from [DTBsFY07]. My objective for the query is just to add descriptions to that main table and I do not want it to hide any duplicate rows. What do I need to do to set it to include the duplicate values? The SQL code is as follows, and, in case it helps, I did not set a primary key in any of the tables: SELECT DTBsFY07.PERIOD, DTBsFY07.[ACCT-UNIT], [ACCT-UNIT-DESCR-TABLE].[AU-DESCR], DTBsFY07.ACCOUNT, DTBsFY07.SYSTEM, DTBsFY07.ENTRY, DTBsFY07.DESCRIPT...

Run make table query from Excel with Access closed
I have an make table query in Access that I want to be able to run from Excel. Ultimately I would like to be able to run the make table with Access closed and no messages appearing until the table is made...then a notice saying the make table is complete. Is there any way to run a make table access query from Excel without opening Access? Thanks. Kick this around. Should help Sub makeTable() 'Needs reference the Axtive X Library 2.0 or higher Dim cn As ADODB.Connection Dim sCnn As String Dim sTableName As String Dim sMakeTableName As String sTableName = "...

Multiple reports from one query
I have several reports that run from one query. Each report has one field whose criteria is different and permanent. Instead of having to change the query criteria every day for each report, can I filter through a report so that I can just open the report and it filter for each report individually? Yes. Take all filtering out of your query. Use your form to allow the user to make filtering options. Use the Where Argument of the OpenReport method to to the filtering. The Where argument is just like a Jet SQL WHERE Clause without the word where. -- Dave Hargis, Microsoft Access ...

Edit with Microsoft Query
I installed Excel 2007 and now when I Right-Click on an existing Microsoft Query file(Excel 2000 or Excel 2003), I do not see the Menu Option to 'Edit with Microsoft Query". What am I missing? ...

Locking on Update Query
I have a database that keeps track of credit applications. I am trying to setup a 2 forms to pull up existing apps and edit them. The application data table is linked to an SQL Server 2000 table. I have 2 forms, one tied to a macro which allows me to find the app I want to edit using several criteria and sends the details of the app I select to a temp table, then opens the edit form, which is showing only the app I want to edit. This works fine. However, when I make the edits on the edit form and press the save button, the query runs, tells me I am updating 1 row, then gives me the l...

Create custom button on POS screen that calls sql query
How can i create custom pos button that call's sql query and show the result of query on pos screen or open another screen that resembles pos screen. You may as well write your own executable program, and just launch that program when you click on the custom POS button. "Sam Thakkar" wrote: > How can i create custom pos button that call's sql query and show the result > of query on pos screen or open another screen that resembles pos screen. ...

Select a query showing only selective post codes relating a relevant postal area
Hi please could you help as i would like to run a query only selecting relevant post codes from my database. The post codes i would like to select are from ST1 to ST4, ST6 to ST7. Thank you spending time to help this issue Chedd -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 I assume that those are the starting three characters of the post code and that the next character is a space. You could try a query using the criteria Between "ST1" and "ST4 zzzzzz" OR Between "ST6" and ...

911! 911! MakeTable Queries Won't Work/Error 3001
This morning my database (A2000 format, running within Access XP on Windows 2000 Pro) worked fine. Then IT showed up, gave me a new computer, and upgraded me toWindows XP Pro/Office XP Pro. Now every one of my maketable queries throws an "Error 3001 -- invalid argument" error. I get the normal "You are about to run..." and "Old table will be deleted..." warnings, then the error. No references flagged as missing. Any ideas? This is a major disaster! Hi Larry, No specific ideas but from what I've read this usually seems to be related to corruption...

Exporting Queries to Excel Automatically
I have a table filled with information on different salesperson. Currently, to give them updates on their customers, I sort them in excel, then copy each single salesperson's data from the mastersheet into another excel files, save it and then email it to them. Is there a way in access to query each one of the sales people's info, sort by a field and then export each one of their data separately to excel? I hope this is clear. Grant ...

Layout/Formatting problems of Data Queries in Excel?
I am using Excel 2003 to display a simple table result from an Access 2003 table. 1. When I select a cell location for a Data Query in the XL spreadsheet, Excel moves all text, above or below that cell, to adjacent columns to the right of the selected cell. (Also, there is no undo option and it all has to be manually restored!) 2. In 'Data Range Properties', the option: 'Insert entire rows for new data, clear unused cellls' is selected. Despite this, the data is displayed with only cells inserted, not entire rows, rendering my report useless. ...

Error message in Excel after exporting Access query to Excel
Hello, I have a few queries in Access that I have been exporting to Excel, and never had any problems for 7 months...until last week. Each Access query is not big, 5 columns, 2-50 rows. Excel file exporting to is about 500kb, not big either. Last week I exported queries from Access without any error messages. However when I tried to open Excel, it says Excel file I am trying to open encountered serious issues last time it was opened, then asked me if I want to report to Microsoft. It did not matter whether I said yes or no to this question, the question box kept coming up for a few ...

Records Appear In the Query, But Not On the Report.
I have a table with client names and another table with the family members of the client. Those are connected via a relationship. I have a query to generate a list of only those clients who are active as of a certain date. I have created a report which lists both the clients and the family members with a left join. However, there are 12 records that are included in the query, but will not appear on the report. There does not seem to be any common factor in these records or any specific difference between these records and the others that appear. I have even attempted deleti...

AVG crosstab query with one field containing text
I have a crosstab query that I use to display grades in a report. All but one of my classes uses numeric grades, which it uses a letter grade (just to show the class was taken). In the crosstab query I've figured out how to get a grade average for each student without that one class included, but I have yet to figure out how to do it with it included. I get a "Datatype mismatch in criteria expression" error. Here's my SQL (Please no lectures about naming conventions, I inherited this database and I've tried changing them, but gave up trying to hunt down ev...

delete query using inner join
The following sql in access DELETE DETAIL2.ID, DETAIL2.[DET:CUSTNO], DETAIL2.[DET:INVIDX], DETAIL2.[DET:CUSTIDX], DETAIL2.[DET:INVOICE], DETAIL2.[DET:DATE], DETAIL2.[DET:JULDATE], DETAIL2.[DET:ITEMNBR1], DETAIL2.[DET:ITEMNBR2], DETAIL2.[DET:ITEMNBR3], DETAIL2.[DET:ITEMNBR4], DETAIL2.[DET:ITEMNBR5], DETAIL2.[DET:ITEMDESC1], DETAIL2.[DET:ITEMDESC2], DETAIL2.[DET:ITEMDESC3], DETAIL2.[DET:ITEMDESC4], DETAIL2.[DET:ITEMDESC5], DETAIL2.[DET:PRICE1], DETAIL2.[DET:PRICE2], DETAIL2.[DET:PRICE3], DETAIL2.[DET:PRICE4], DETAIL2.[DET:PRICE5], DETAIL2.[DET:IPRICE1], DETAIL2.[DET:IPRICE2], D...

Query fetch method
Hi I want to write eq of following SQL in qurty fetch method. "SELECT SalesOrderId, CFCCOGSexGST, PricePerUnit FROM SalesOrderDetailBase WHERE (SalesOrderId = '{566B674E-82A8-43BA-829A-298F57F60F4E}') AND (CFCCOGSexGST > PricePerUnit)" What I do not know is how to write 'field' value as value clause. for example( I have written here part of the code) queryXml.Append("<filter type='and'>"); queryXml.Append("<condition attribute = 'SalesOrderId' "); queryXml.Append("operator='...

Query Problem 09-23-07
I have a subform that brings up recipe ingredients. The subform record source is Recipe Ingredients table. I place a textbox on the subform that pull a calculation from the query. The control source for the textbox is set to =[UnitExt Query]![UnitCost] but when i run the subform it comes up #NAME? If I run the query the calculation is correct. What am I doing wrong? On Sun, 23 Sep 2007 14:40:14 -0500, "Dudy" <dudyone@hotmail.com> wrote: >I have a subform that brings up recipe ingredients. >The subform record source is Recipe Ingredients table. >I place a textbox ...

Defining a Range of Data with a Query
I have a range of data - say A1: D500. The range is being used like database with 6 column fields. I want to be able to identify the tw rows within the range. My data is hourly. The data displays multipl machine stats over various hours over various dates. I want t identify the first and last row where the fields match my query. Say Column A = Date, Column B = Hour, Column C = Machine and Column D Measured Data . Lets say I am querying on Date = 01/01/2004, Hour = 1. I need t search the array to find the start row and end row that have thes values in them. What I am really trying to...

passing value from a form with IIF statement in a query
I have a form with a combo box that returns either a 1 or 2. If 1 is selected , the query would select the records that corresponds to the person with an ID of 1. If the value is 2, I want to select everyone who is NOT a 1. Using that value from the combo, I want to run a query that incorporates the following IIF statement: IIf([forms]![dlgRxRptByMonth].[repDoc]=1,1,>1) If I select the 1 in the combo box it produces the correct data. IF I select the value 2, the query runs without an error BUT produces no data. (I actually tried to insert the >1 as the value in th...

Pass Through Query
I've written some SQL, which when run on SQL Server 2005 returns rows. Plugged this SQL into an Access 2003 pass through query, but when run it reports an error saying pass through query with return records set to true did not return any records. I've got other pass through queries in this Access database that work fine. The SQL in this query is more complicated and creates a temp table - could this be causing the problem? hi Gary, On 08.03.2010 10:38, Gary Watson wrote: > The SQL in this query is more complicated and creates a temp table - could > this be cau...

Calculated result in query problem
I have written a query (using SQL) that has in it a calculated field which in itself uses a subquery to perfrom a sum() function of a list of values. When I view the query results directly, the returned values are calculated correctly. I then have a report that uses this query as the data source. When I display this calculated result in the report, it is wrong. It is always a larger number in the report than in the query, as though it were being multiplied by some factor. The report does nothing fancy but display the results from the query. Has anyone else experienced this phenomenon? F...

select status in a query
I have a table with account information. One of the fields is Account_Status, there are 6 possible choices for account status going from 1 - 6. Wha I'd like to do is run a qurey where the user can choose to return accounts with status 4 or 6. Any help appreciated. I can use "between and" ut this returns to much information if I need just 3 or 6. It also has to be user friendly as I do not want someone in the query. Any help appreciated. Thanks Jo See sample database at this site: http://www.cadellsoftware.org/SampleDBs.htm#FilterForm Look at how the listbox is set up (...

Adjusting field data with update query
I run an import where the date is formated as follows: Wed Aug 22 11:47:43 -0500 2007 In excel I created the following macro to adjust the format to mm/dd/yy hh:mm:ss For Each Cell In Range("e2:h" & Range("e65535").End(xlUp).Row) If Cell.Value <> "" Then Y = Right(Cell, 4) M = Month(Mid(Cell, 5, 6)) D = Mid(Cell, 9, 2) T = TimeValue(Mid(Cell, 12, 8)) Cell = DateSerial(Y, M, D) + T End If Next Can I create something similar with an update query? I tried placing the follo...

How can I get the Next Record fields in the same Query
I have two identical queries, but want one half of the records to come from Primary Key record 1, and the second half of the records to come from Primary Key record 2. The Primary Key is: Run_point_List_ID Run_point_List_ID Run_No Run_point_Venue_A Run_point_Address_A Run_point_List_ID2 Run_point_Venue_A Run_point_Address_A 377 1 Health Centre 23 Main Street 378 Central Hotel 32 South Street I can manage to get both columns for Run_point_List_ID, with this line in the QBE grid: Run_point_List_ID2: ([QRY_Reports_Points 3 alternate copy 2].[Run_point_List_ID]+1), but cannot work out how t...

Query that is a sourceof a table
I use the following query as a source of a table: SELECT tblClientDetails.* FROM tblClientDetails ORDER BY tblClientDetails.strClientNo; Is it possible that since I started using this instead of using the table as the source I started having problems because of that, to me it seems absolutely fine and I wonder if anyone thinks that I should call the fields separately or else? Thank you, Samuel In news:eh6VPIX8HHA.1208@TK2MSFTNGP05.phx.gbl, Samuel <samuel.shulman@ntlworld.com> wrote: > I use the following query as a source of a table: > > SELECT tblClientDetails.* > ...