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. gRegulator

    Date Parameters in SQL Query

    It works as long as the dates selected are on of before todays date. This is weird, but I will leave my code as is. We don't need to calculate stats for the future anyway, so this should be sufficient. Thanks for your help PC. Greg
  2. gRegulator

    Date Parameters in SQL Query

    Also, I have checked the date entries and I am positive there are no errors. This seems very confusing to me....
  3. gRegulator

    Date Parameters in SQL Query

    Here is an example of when dates are not picked up: If I type 1/1/2005 as start and 4/1/2005 as end, the numbers are correct. if I type 4/1/2005 as start and 3/31/2006 as end, the numbers are way off. I would expect this query to return hundreds of results, but it only returns 4! I...
  4. gRegulator

    Date Parameters in SQL Query

    Hi PC, Thanks for your reply. I have changed Admit to qry_q31_offence_types.Admit. I have checked all records and there are no null values. The dates are entered in the format mm/dd/yyyy. I am still having the same luck. I'm very confused because as you said, the SQL looks ok. What do...
  5. gRegulator

    Date Parameters in SQL Query

    I have the following SQL Query: SELECT qry_q31_offence_types.Offence, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients] FROM qry_q31_offence_types GROUP BY qry_q31_offence_types.Offence; I have been trying to put in date parameters but I have been out of luck. I have tried...
  6. gRegulator

    SQL Query, Wrong Results

    Yeah the sum is wrong. For example, If i enter the start date ([Forms]![frm_dateparameter_q31]![txtStart]) as 4/1/2005 and the end date ([Forms]![frm_dateparameter_q31]![txtEnd]) as 3/3/2006, I get values that are 2 , 3, etc, where the number should actually be more like 200 or so. For shorter...
  7. gRegulator

    SQL Query, Wrong Results

    Hi, I have the following SQL query. For some reason, the results it produces are very wrong. What I am trying to do is get a count of the types of offences committed by people (grouped by male and female) for clients admitted during a given time period. My query looks like this: PARAMETERS...
  8. gRegulator

    Average Caseload between Dates

    WOW! That is exactly what I needed. Thanks for that last step. I am not very familiar with the switch statement, so that helped alot! Tom, Thank you so much. Have a great day!
  9. gRegulator

    Average Caseload between Dates

    Tom, That seems to be what I'm looking for. Right now I have the code written as: PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime; SELECT MALE, FEMALE, Sum(IIf([Probation...
  10. gRegulator

    Average Caseload between Dates

    Hi, I have been using the following SQL code to find the average number of clients that are supervised over a selected time period. It works great. The date fields that it uses are [Probation Start] and [Probation Expiry]. PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime...
  11. gRegulator

    Query: Average Daily Counts per Month

    gol4, thanks again, i managed to have both male and female in there just by adding the female field to your sql statement to read: PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime; SELECT MALE, FEMALE, Sum(IIf([Probation...
  12. gRegulator

    Query: Average Daily Counts per Month

    gol4, That code you gave me works great, now i am just wondering if you can show me how to divide the data so it will break it down into male and female clients. Male and Female are options from the same table (tbl_offenders) and are check boxes. So instead of having a total average, i...
  13. gRegulator

    Query: Average Daily Counts per Month

    Hi, Thank you both for your advice. gol4, I used your suggestion and it gave me exactly what I was looking for! I really appreciate the help, that was one of the last things I have to finish on my database, this brings me one step closer! Thanks again and have a great day! Greg
  14. gRegulator

    Query: Average Daily Counts per Month

    Hi, I am trying to figure out how to calculate an average daily count of clients that we supervise. I have all my data stored in a Master table called tbl_offenders. From the tbl_offenders I would like the following fields: Male, Female, Probation Start, Probation Expiry. Where Male and...
  15. gRegulator

    Year End Summary Query: Average of Each Month

    I think thats on the right track... Each case load per month should reflect whether the cases are active or not. _______ SELECT 'Probation' AS ConditionDescription,Male, Female, [Probation Start] as [Start], [Probation Expiry] as [Expiry] FROM tbl_Offenders WHERE Probation = True UNION ALL...
  16. gRegulator

    Year End Summary Query: Average of Each Month

    I want to have a count of clients on [Probation], [Conditional Sentence],[Fine Option Program] and [Community Service Order]. (These are all check boxes). I want this count to be an average for every month. i.e. January Probation - 36 Conditional Sentence - 12 Fine Option...
  17. gRegulator

    Year End Summary Query: Average of Each Month

    Hi, I am trying to make a year end summary query with the Average Caseload of clients supervised. So what I want is the average Caseload from January, February, March, etc. and then add them up and divide by 12, to get a yearly monthly average. The fields I wish to have a count for are...
  18. gRegulator

    Error Message in SQL Query

    Hey Golom, Thanks for your help! I was really confused on this one, but your suggestion straightened it right out! Have a great day!
  19. gRegulator

    Error Message in SQL Query

    Does anyone have an idea why this may be happening?
  20. gRegulator

    Error Message in SQL Query

    I don't know if this will help but here is the query that it is based on: SELECT 'Offence Against Person' AS Offence, Male, Female, NZ([Probation Start],[CS Start]) as [Start] From tbl_offenders WHERE [Offence Against Person] = True UNION SELECT 'Offence Against Property', Male, Female...

Part and Inventory Search

Back
Top