Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: calculating value list from a drop down

    Hi everyone I have an issue and its in access, so I'm struggling with converting a select statement from SQL to Access 2010. Ugh I miss sql. anyone I have this statement that I am trying to run but I keep getting an error message: I have it s an image

    I'm trying to calculate Each value in my [Customer Type] combo box. like how many hang-ups or how many Unknown calls. When I did this in sql I did it in reporting services in a matrix, different database though. This is access and I am struggling with it can anyone please help I really gota get this done.



    Click image for larger version. 

Name:	Doc2.jpg 
Views:	4 
Size:	181.6 KB 
ID:	15923

    Code:
    SELECT 
    	        [Message Tracking].[User Retrieving], 
    [Message Tracking].[Date Retrieved],
     [Message Tracking].[Date & Time Left], 
    [Message Tracking].[User Returning],
     [Message Tracking].[Date Returned],
     [Message Tracking].[Caller],
     [Message Tracking].[Customer Type]
    
    
    	FROM [Message Tracking]
    	WHERE  
    	      
    	       ( Message Tracking].[Date & Time Left]) Between [Begin_Date & Time Left] And [Ending_Date Returned) 	       
    	       
    	       
    	      
    	ORDER  BY IIF([Message Tracking].[Customer Type] = 'UNKNOWN',0,1), 
    	          IIF([Message Tracking].[Customer Type] = 'HANG-UP',0,1), 
    	          IIF([Message Tracking].[Customer Type] = 'PT',0,1), 
    	          IIF([Message Tracking].[Customer Type] = 'CLINIC',0,1), 
    	          IIF([Message Tracking].[Customer Type] = 'INS',0,1), 
    	          IIF([Message Tracking].[Customer Type] = 'GOV',0,1), 
    	          IIF([Message Tracking].[Customer Type] = 'ESQ',0,1), 
    		  IIF([Message Tracking].[Customer Type] = 'APS',0,1));

  2. #2
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I think I got what I needed the rest I can do in the report itself

    Code:
    SELECT People_tbl.FirstName, People_tbl.LastName, Count(IIf([TanfActivity_tbl]![Catagoryforhours]="Travel",1,Null)) AS TotalTravel, TanfActivity_tbl.EventDate, People_tbl.[Parent ID], Count(IIf([TanfActivity_tbl]![Catagoryforhours]="Parenting",1,Null)) AS TotalParenting, Count(IIf([TanfActivity_tbl]![Catagoryforhours]="ITP",1,Null)) AS TotalITP
    FROM People_tbl LEFT JOIN TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID]
    GROUP BY People_tbl.FirstName, People_tbl.LastName, TanfActivity_tbl.EventDate, People_tbl.[Parent ID]
    HAVING (((TanfActivity_tbl.EventDate) Between [Beginning_EventDate] And [End_EventDate]));

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suspect the main are of problems is going to be the IIF's in the ORDER BY clause
    ...to get round that ugly construct what you could do is create a table for caller types containing the call type codes, a description AND a column which specifies the sort order value. and then JOIN to that table

    Code:
    SELECT 
      [User Retrieving], 
      [Date Retrieved],
      [Date & Time Left], 
      [User Returning],
      [Date Returned],
      [Caller],
      [Customer Type]
      SortOrder
      
    FROM [Message Tracking]
    JOIN CustomerTypes 
    ON [Message Tracking].[Customer Type] = CustomerTypes.CType
    WHERE  
      [Message Tracking].[Date & Time Left]) Between [Begin_Date & Time Left] And [Ending_Date Returned)
    order by CustomerTypes.SortOrder
    you could also pull in the customer type description

    you CallerTypesTable could be
    CType
    CTDesc
    SortOrder
    'INS','whatever INS is',0
    'GOV,'whatever GOV is',0
    'APS,'whatever APS is',0'
    ..declare a reference (use RI) between CodeTypes and message tracking

    just a comment, when designing tables you will make life easier for yourself if you do not use spaces in table and column names. there is no need to. use CamelCase or separate_words_with_underscore.
    if you want a human friendly label set on as the columns caption property at design time.
    don't use reserved words or symbols
    http://support.microsoft.com/kb/286335
    http://support.microsoft.com/kb/826763
    although Access will generally make a good fist of working around these problems, sometimes it can't and will just fall over.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Yes I know about the fields having spaces, I mentioned that to the person that designed the entire database. I learned long ago that this will cause you nothing but issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •