Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: Help Please, Way over my head!!

    We've instituted an "Employee Recognition" program at the office. We're using poker chips (cause they're fun to play with) as a reward system for employees that are observed doing the right thing or going above and beyond. The employee with the most points at the end of the month gets a gift card or an extra paid day off.
    green chips are worth 1 point.
    blue chips are worth 2 points.
    red = 3, and blak is worth 4.
    I've created a "Tracking" table. fields are:
    ID = AutoNumber
    EventDate = Text
    LSID = Number ( Relation to leadership table: lead id, lead Name)
    EmpID = Number ( Relation to Emp table EmpID, Emp Name)
    CVID = Number ( Relation to CoreValue table CVID, CVName)
    ChipID = Number ( Relation to Chip tale ChipID, ChipName, ChipValue)
    Reason = Memo ( Notes as to why the recognition was given)

    And here is where I fall on my face.
    Need a query to select All of the employees in a date range and show how many chips they've gotten and how many points they're worth.

    Select Emp.EmpName, Count(ChipID) as Total Chips, Sum(Chip.ChipValue) as Total Points
    from Tracking, Emp, Chip
    where Tracking.EventDate >= StartDate and Tracking.EventDate <= EndDate
    and Tracking.LSID = 18
    Order By "Total Points"

    As you can see, I'm a bit retarded... but this is the idea of what I need. I'll need a similler query to sort by LSID. But I should be able to hack something together if I can get something to work with.

    As you've probably already noticed, this is an Access database. I have no choice in the matter and have to work with the tools available... Sorry.

    Any assistnce would be greatly apreciated.

    Thanks
    Jim

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've moved your thread from the ANSI SQL Forum to the Microsoft Access Forum. It will almost certainly get much better answers here.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2009
    Posts
    50
    Thank you. But the advise I'm looking for is purely the sql query to use for situation. Access is just the engine that I'm forced to use. Is that still the best place for to get the assistance I'm looking for?

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    UPDATE:
    Heres a better picture of what I need. The report should show each employee and the number of each chip that they have recieved as well as the total points. From a report perspective, the colunm headings would be as follows:
    EmpName Green Blue Red Black Total Points
    Jim 1 1 1 1 9
    Joe 0 1 2 0 7
    Frank 1 2 1 1 10

    and so on...
    Hope this helps clarify what I'm looking for.

    Regards
    Jim

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are two problems with your query and one probelm with your table

    first, you want aggregates by both employee and chip, so you need a GROUP BY clause

    second, you have not joined the tables properly

    the problem with the table is the eventdate column, which should be a date/time column if you want to do date range comparisons
    Code:
    SELECT Emp.EmpName
         , Chip.ChipName
         , COUNT(*) as Chip_count
         , SUM(Chip.ChipValue) as Chip_points
      FROM (
           Tracking
    INNER
      JOIN Emp
        ON Emp.EmpID = Tracking.EmpID 
           )
    INNER
      JOIN Chip
        ON Chip.ChipID = Tracking.ChipID
     WHERE Tracking.EventDate BETWEEN StartDate AND EndDate
       AND Tracking.LSID = 18 
    GROUP
        BY Emp.EmpName
         , Chip.ChipName
    this should get you totals that you can verify

    rearranging the results into the desired report format comes after you have the query returning the correct results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jbedson View Post
    ...I'm looking for the purely sql query to use for situation...Is that still the best place for to get the assistance I'm looking for?
    Yes! The SQL used in Access is slightly different from other 'flavors' of SQL, and the variances can really make difference, at times.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to possibly simplify some of your queries you could assign the PK of the 'chip' to be its numeric value. so when you give a chip to an employee its value is included in that assignment. unless you need to report the chip type it means saving a join.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2009
    Posts
    50
    Using the PK would make things a bit easier, however, it's not practicle. If the point value of each chip were to change, I would have no way of implimenting it. I used the ID for the Chip in case the Name of the chip changes. This way, I can use the id to change all of the previously awarded chips, by changing the name, or creat a new id and keep the old system for reporting and historical purposes. This set up gives me the most flexibillity with the fewest number of fields.

    Please tell me how you would impliment it. You've sparked my curiosity.

    Jim

  9. #9
    Join Date
    Dec 2009
    Posts
    50
    Thanks for the help. I was able to tweek the query to get the results I was looking for.
    I have one more request and again I'm lost.
    I need a query to show the number of each chip that each member of the leadership team has issued. Here's my attempt to use sql to describe what I"m looking for. (this did not work btw)

    Select Leadership.LSName, Count(select Chip.ChipID =1) as Green, Count(Select Chip.chipID = 2) as Red, Count(Select Chip.ChipID = 3) as Blue, Count(Select Chip.CHipId = 4) as Black
    from (Tracking Inner Join Leadership On Tracking.LSID = Leadership.lsid)
    where tracking.eventdate between '8/1/2011 ' and '8/31/2011'
    group by Leadership.lsname;

    The result should should show the number of each chip that each Leadership ID has issued.
    I just removed the select from each of the count statements and got a result. Each field was 5, which is the total number of entries overall and not the count of each chip type.

    Thanks again for your assistance
    Jim

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Leadership.LSName
         , COUNT(IIF(Chip.ChipID = 1,'green',NULL)) as Green
         , COUNT(IIF(Chip.chipID = 2,'red  ',NULL)) as Red
         , COUNT(IIF(Chip.ChipID = 3,'blue ',NULL)) as Blue
         , COUNT(IIF(Chip.CHipId = 4,'black',NULL)) as Black
      FROM (
           Tracking 
    INNER 
      JOIN Leadership 
        ON Leadership.lsid = Tracking.LSID 
           )
    INNER
      JOIN Chip
        ON Chip.ChipID = Tracking.ChipID
    
     WHERE tracking.eventdate BETWEEN '2011-08-01'
                                  AND '2011-08-31' 
    GROUP 
        BY Leadership.LSName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2009
    Posts
    50
    Thanks for the assistance R937, that worked perfectly. You've bailled me out and I'm very grateful for that. Thank you again.

Posting Permissions

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