Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Query Help (2000)

    Further to the help I've recently enjoyed, I'm attempting to put together a query for a report. Here's the scenario;

    We have automatic reporting from vehicles on site; recording things like; when driven; by whom; for how long; etc.
    To help recognise the better drivers we're using the DB to score them. Of course this doesn't record the antics of some of the miscreants, so we've got another table to exclude them from the recognition scheme, which is done quaarterly.
    I can do the part where the points from the system are allocated divided by the hours driven. But tying that together with the other table is not quite so easy.

    What I'd like is that if a driver has an 'End Date' after the beginning of the selected quarter, they're excluded from the result.

    Im supplying an extract of the DB if anyone wants to have a bash.
    NB Of course, all the selection criteria will be done through forms for the actual users.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Query Help (2000)

    Linked table T_HISTORY1 is not available because you have not provided database H:ATMS4.MDB.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    Doh! I forgot that was linked. Here we go
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Query Help (2000)

    <<if a driver has an 'End Date' after the beginning of the selected quarter>>
    Where is the beginning of the selected quarter entered?
    Is the EndDate in the Addpoints table?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    Hiya Patt. The EndDate is in the Addpoints table (in fact there is more than one, but only the un-numbered one counts in this case). As for where the begining of the quarter is entered; the query I've included will reference a user form for the Year & quarter to be entered i.e.
    Like [forms]![Frm-DriverScoreReport]![QtrReq] & "*"
    will be in the criteria for the datepart field for quarter.
    So Access will be setting the actual dates for the relevant quarter.
    Does that help at all?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Query Help (2000)

    I thought you wanted the query to look for records where the 'End Date' is after the beginning of the selected quarter, but your query shows 4 as the qtr selected as well as the year 2002. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    What is the beginning date of quarter 4?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    Sorry Patt, I didn't explain very well.

    What I'd like the query to do is give a list of the drivers names, with their scores, but NOT including any of those whose end date is later than the selected year/quarter.

    I've just put the quarter as 4 & the year as 2002 simply for that set of records (there were actually over 45,000 records in that copy of T_HISTORY originally!). The user(s) will be putting those in at their own leisure. As for the first date of quarter 4, well that'll be the first day of October; likewise quarter 1 will be January 1st, 2 - April 1st etc.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Query Help (2000)

    I think you should try the following WHERE clause in your query:

    WHERE (((DatePart("yyyy",[EndDate])*10+DatePart("q",[EndDate]))<[forms]![frm-DriverScoreReport]![YearReqd]*10+[forms]![frm-DriverScoreReport]![QtrReqd]))

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    I've tried adding the WHERE to the end of the WHERE in SQL view, with an AND, but that didn't seem to work.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Query Help (2000)

    Post the SQL. Just describing what you tried to do doesn't tell us what might have gone wrong.
    Charlotte

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Query Help (2000)

    Try this:

    SELECT DRIVERS.NAM, DRIVERS.WorkArea, Sum(FaultScore.Score) AS SumOfScore, IIf(Sum([FaultScore]![Score])=0,0,Sum([FaultScore]![Score])/Sum([T_HISTORY1]![HI_HRSUSED])) AS CalcScore, Sum(T_HISTORY1.HI_HRSUSED) AS SumOfHI_HRSUSED
    FROM ((T_HISTORY1 INNER JOIN DRIVERS ON T_HISTORY1.HI_OPERATOR = DRIVERS.DRIVER_ID) LEFT JOIN FaultScore ON T_HISTORY1.HI_FAULT = FaultScore.Fault) LEFT JOIN AddPoints ON DRIVERS.DRIVER_ID = AddPoints.[Driver ID]
    WHERE (((DatePart("yyyy",[EndDate])*10+DatePart("q",[EndDate]))<[forms]![frm-DriverScoreReport]![YearReqd]*10+[forms]![frm-DriverScoreReport]![QtrReqd]))
    GROUP BY DRIVERS.NAM, DRIVERS.WorkArea
    HAVING (((DRIVERS.NAM) Not Like "Still Engineer" & "*") AND ((DRIVERS.WorkArea)="E") AND ((Sum(T_HISTORY1.HI_HRSUSED))>0))
    ORDER BY IIf(Sum([FaultScore]![Score])=0,0,Sum([FaultScore]![Score])/Sum([T_HISTORY1]![HI_HRSUSED])), Sum(T_HISTORY1.HI_HRSUSED) DESC;

    If this is not correct then you will have to explain it in more detail in what you are trying to achieve.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    OK here's the current SQL. This is from the copy of the actual DB, as opposed to the test one I posted;

    SELECT DRIVERS.NAM, DRIVERS.WorkArea, Sum(FaultScore.Score) AS SumOfScore, IIf(Sum([FaultScore]![Score])=0,0,Sum([FaultScore]![Score])/Sum([T_HISTORY1]![HI_HRSUSED])) AS Score, Sum([T_HISTORY1]![HI_HRSUSED]) AS [Hours Total], Max(T_HISTORY1.HI_DATE) AS MaxOfHI_DATE
    FROM ((T_HISTORY1 INNER JOIN (DRIVERS INNER JOIN DRIVERS1 ON DRIVERS.DRIVER_ID = DRIVERS1.DRIVER_ID) ON (T_HISTORY1.HI_OPERATOR = DRIVERS.DRIVER_ID) AND (T_HISTORY1.HI_OPERATOR = DRIVERS1.DRIVER_ID)) LEFT JOIN FaultScore ON T_HISTORY1.HI_FAULT = FaultScore.Fault) LEFT JOIN AddPoints ON DRIVERS1.DRIVER_ID = AddPoints.[Driver ID]
    WHERE (((DatePart("q",[Hi_Date])) Like [forms]![Frm-DriverScoreReport]![QtrReq] & "*") AND ((DatePart("yyyy",[Hi_Date])) Like [forms]![Frm-DriverScoreReport]![YearReq] & "*"))
    GROUP BY DRIVERS.NAM, DRIVERS.WorkArea
    HAVING (((DRIVERS.NAM) Not Like "Still engineer" & "test only") AND ((DRIVERS.WorkArea) Like [forms]![Frm-DriverScoreReport]![AreaCode] & "*") AND ((Sum([T_HISTORY1]![HI_HRSUSED]))<>0))
    ORDER BY IIf(Sum([FaultScore]![Score])=0,0,Sum([FaultScore]![Score])/Sum([T_HISTORY1]![HI_HRSUSED])), Sum([T_HISTORY1]![HI_HRSUSED]) DESC;

  13. #13
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    Pat, tried that later SQL & got a result, just one record. Then tried a different year/quarter & got none. Also failed to get any records when re-trying the original year/quarter (though I think I probably changed the AddPoints record for that individual).

    Hmmmm.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Select Query Help (2000)

    You said that you wanted records summed with an end date less than a selected qtr and year. What you have in your query is a test for Hi_Date being the same as a selected qtr and year.
    Which is it?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  15. #15
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query Help (2000)

    No, summing isn't what I meant. The requirement is a list (I did put that in an earlier post, honest, really), rating the lowest score at the top.

    If the fields from the [AddPoints] table were removed from the query, I'd get the result for ALL the drivers in the [DRIVERS] table (according to the criteria in the [NAM] field). But I need to drop out the ones who have records in the [AddPoints] table who have a [StartDate], [EndDate] period overlapping the criteria for the {HI_DATE]. You see, those who have been reported as driving poorly get added to the [AddPoints] table, so wouldn't be eligible for 'Good driver' recognition.

    Phew, this really isn't easy to explain with text <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Can this be done in the one query? Or would it be easier to do a separate one for the [AddPoints] table & then perhaps a union query to tie it all together?

Page 1 of 3 123 LastLast

Posting Permissions

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