Results 1 to 2 of 2

Thread: sql/access

  1. #1
    Join Date
    Dec 1969

    Default sql/access

    here is my table:<BR><BR>ID-------Job Request------Date------Hours<BR>1--------Art work---------1/5/99----7<BR>2--------Drawing----------2/5/01----3<BR>3--------HouseKeeping-----3/5/01----4<BR>4--------driving----------3/6/01----2<BR>5--------Housekeeping-----3/7/01----1<BR><BR>and the list continues<BR>My query Goal: To enter a date in a submit box, so that the query generates a list of Job Requests with sums of the job request for that particular week. For example, if i type into my submit box week of "3/6/01" The recordset would return each Job Request that was active during that week and then next to the Job request would be the total number of hours that i worked on that job. so, 3/6/01 would return (Housekeeping 5 hours). and the forms must be generated in Microsoft access, so i am confined to using sql ONLY.<BR><BR>I started writing my sql statement:<BR><BR>SELECT Date<BR>From TableName<BR>WHERE #&#060;BETWEEN ? &#062;<BR>AND Job Request = (the Job Requests from that week)<BR>SELECT HOURS From tableName where ? <BR><BR>as you can see, I have no clue, any help would be great

  2. #2
    Join Date
    Dec 1969

    Default RE: sql/access

    SELECT JobRequest, SUM(Hours) as TotalHours FROM Table <BR>GROUP BY Jobrequest<BR>WHERE Date BETWEEN datevar AND DATEADD(day,7,datevar)<BR><BR>This gets you the Sum of hours for each jobrequest between &#039;datevar&#039; and 7 days later. If this isn&#039;t what you want just play a little with the dateadd function. (it&#039;s in the help)<BR>

Posting Permissions

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