Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. Sideline

    Array calc using count of distinct events in denominator

    test_data is in MS Access stand alone inner query returns 10 rows but when the inner query is placed between ( and ) it throws the error above. thx
  2. Sideline

    Array calc using count of distinct events in denominator

    Skipvought, in ms query Transform Sum(test_Data.Elapsed_Time)/Count(*) as ET Select test_Data.Weekday, test_Data.Class From ( Select test_Data.Weekday, test_Data.Hour, test_Data.Date, test_Data.Class, SUM(test_Data.[Elapsed Time]) as ET From test_Data test_Data Group by test_Data.Weekday...
  3. Sideline

    Array calc using count of distinct events in denominator

    Skipvought, apologies for mis-spelling your nick. I tried to use your suggested sql without success. The sub-query would not work. The good news is another forum suggested the use of {= SUM(IF(FREQUENCY(IF((A2:A14=1)*(B2:B14=1),C2:C14),C2:C14)>0,1))} This allows for a change in the criteria...
  4. Sideline

    Array calc using count of distinct events in denominator

    Skipcought, all of that is already in place for the original dataset. However, one limitation of running an aggregation at the database level is not having the capacity to modify the denominator to include other dimensions from the dataset. ie from the example dataset below Weekday = 1, Hour...
  5. Sideline

    Array calc using count of distinct events in denominator

    One last comment. It would be cool to be able to do this within the workbook without having two distinct datasets which would blow the file size out. thx
  6. Sideline

    Array calc using count of distinct events in denominator

    thx skipvought, I have spent too much time in the forrest. Aggregating the data by hour, weekday and date would solve the problem. Will do in the database and let you know how I get on. cheers
  7. Sideline

    Array calc using count of distinct events in denominator

    SkipVought, you are correct if the denominator is the number of dates for that combination of hour and weekday; which is 7. However for the example above, day 1 hour 1, there are 7 records, but only 5 distinct dates. There are two records for each of 08/03/09 and 09/03/09. Thus the...
  8. Sideline

    Array calc using count of distinct events in denominator

    SkipVought, thanks for the quick response. each row in the actual dataset has a date time stamp which is converted using the weekday and hour functions. Hence the data set has many event instances for each weekday (1- 7) and each hour (0 - 23). in the hypothetical data set above the last event...
  9. Sideline

    Array calc using count of distinct events in denominator

    Dear All, This has me stumped and I would very much appreciate any suggestions. Dataset is 220K rows. An small subset follows. Using an array calc with criterions Day = 1 and Hour = 1 summed on Elapsed Time returns 81 with an average of 11.57 for 7 events of which there are 5 distinct dates...
  10. Sideline

    RDP Citrix ICA Client Mac OS X keyboard not functioning

    As an update my iMac died (blown logic board) about 2 weeks post this thread. Purchase of a new machine with 10.5 solved the problem. Not sure if it was the new os x of a config change but all is great now. cheers
  11. Sideline

    RDP Citrix ICA Client Mac OS X keyboard not functioning

    Dear All I am trying to use rdp for a xp machine using citrix ICA client for mac os x 10.4. However, when the 'log on to windows' appears the keyboard is unresponsive. I can not enter a password to access the xp desktop. Using options at the remote desktop connection dialogue box allows me...
  12. Sideline

    Distinct row based upon date/time criterion

    But then again there is nothing like self help! SELECT Transformation.Theatre, Transformation.[Op Date], Transformation.[Operation Id], [qry NT 1]![Anaes Start]-IIf(IsNull([qry NT 2]![Surg Fin]),0.3541661,[qry NT 2]![Surg Fin]) AS [Start Delay], Transformation.Year, Transformation.Month...
  13. Sideline

    Distinct row based upon date/time criterion

    Thanks for the feedback Remou. As indicated in the initial post I was unsure on how to get this started using sql as opposed to the access query gui. A colleague created the following, using sql server, and I will now have a go at translating this into a code which I can use in access. If...
  14. Sideline

    Distinct row based upon date/time criterion

    I have little sql experience beyond the access gui and would appreciate any tips on how to get this started. Based upon my event dataset I need to create a query which returns for each day the first event of each day in the dataset based upon a data/time criteria. That criteria is a) the first...
  15. Sideline

    Mac OS X and Citrix

    Tim, Did you get a solution for this problem? I posted the following in remote access forum without reply. Any thoughts thx Richard Apologies if my terminology is astray. I am using IE on OS X attempting to log into my Windows XP profile at work and I get the following error when I try to...
  16. Sideline

    ClassNotFoundException: com.citrix.JICA.class

    Apologies if my terminology is astray. I am using IE on OS X attempting to log into my Windows XP profile at work and I get the following error when I try to boot an application. java.lang.ClassNotFoundException: com.citrix.JICA.class Searches on the web have suggested importing the security...
  17. Sideline

    Filter dates defaulting to American formats

    Using a form to contrust a report filter the result returns records between dates based upon an American format ie mm/dd/yy for the filter. This occurs even though the form and report control formats and the query field that provides the source data are all defined as dd/mm/yy. Thanks
  18. Sideline

    Converting Data Types

    Dim dbs As Database, rel As Relation Set dbs = CurrentDb Dim tdf As TableDef, linktdf As TableDef Dim fld As Field, fld1 As Field Set linktdf = dbs.TableDefs("Task_Information") linktdf.RefreshLink 'dbs.Execute ("Alter Table Deliverables alter column...
  19. Sideline

    Converting Data Types

    mpastore, To clarify, which I should have earlier, a am trying to write a module that will delete a relationship, run a make table query, change the data type of one of the fields in the created table, recreate the relationship, run a select query that uses the created table and then preview a...
  20. Sideline

    Converting Data Types

    mpastore, The following CurrentDb.Execute ("Alter Table Deliverables alter column OutlineNumber long") produces a 'syntax error in ALTER TABLE statement". I cannot see where the error in the code is. Can you please debug? Thanks Richard

Part and Inventory Search

Back
Top