Results 1 to 7 of 7

Thread: Difficult SQL statement problem

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2000
    Location
    Westminster, Md.
    Posts
    163
    I have a bit of a problem and I cant' seem to find away around it. Any help would be appreciated.

    I am using an Access Database (currently using Access97, SR2), VB6 SPIII and have the following references in the project. I am using a Datagrid to display my data.

    References:
    Microsoft ActiveX Data Objects 2.0 Library
    Microsoft Data Binding Collection

    I have a table in the database which is linked to another table by a common ID. Each IDNumber represents 1 person who works a specified number of hours each day. What I'd like to do is to create a Total for each person of hours that they have worked during the week. Sounds Simple dont it!

    Unfortunately it isnt. The table that holds the data for each rep has all of the data for all of the reps in it for every day. What I need to do is take only the Values for each rep for THIS week and Sum them. Then display them with Each person as 1 row in the Datagrid and only their Totals showing.

    Below is an idealized example of the data in the two tables. There is actually a lot more data to it. There is also two fields in the RepTable which contain the names that belong to the ID numbers, which is what I want to display in the Datagrid instead of the ID number. (As if this wasn't complicated enough)
    ------
    [WeekData]
    IDNUM DATE Contr Uncon
    111-11-1111 11/1/00 06:00 02:00
    222-22-2222 11/1/00 06:00 02:00
    333-33-3333 11/1/00 06:00 02:00
    111-11-1111 11/2/00 06:00 02:00
    222-22-2222 11/2/00 06:00 02:00
    333-33-3333 11/2/00 06:00 02:00
    111-11-1111 11/3/00 06:00 02:00
    222-22-2222 11/3/00 06:00 02:00
    333-33-3333 11/3/00 06:00 02:00
    111-11-1111 11/4/00 06:00 02:00
    222-22-2222 11/4/00 06:00 02:00
    333-33-3333 11/4/00 06:00 02:00
    111-11-1111 11/5/00 06:00 02:00
    222-22-2222 11/5/00 06:00 02:00
    333-33-3333 11/5/00 06:00 02:00
    111-11-1111 11/6/00 06:00 02:00
    222-22-2222 11/6/00 06:00 02:00
    333-33-3333 11/6/00 06:00 02:00

    [RepTable]
    IDNUM FirstName LastName
    111-11-1111 Bob Robertson
    222-22-2222 Arnold Musclehugger
    333-33-3333 Homie TheClown
    --------

    Now I know what you're thinking.... Use the SUM() SQL statement. Well I tried that, and it's giving me an error because I am not trying to sum the ID and Date fields.

    What I need is basically this SQL statement:
    "SELECT SUM(Contr),SUM(Uncon),Date,IDNUM FROM [WeekData], FirstName,Lastname FROM [RepTable] WHERE IDNUM=" xx " AND Date BETWEEN " MondaysDate " AND " SaturdaysDate


    xx=A sum for each Rep
    ----

    So That each row in the datagrid looks like this:
    FirstName LastName Contr Uncon
    Bob Robertson 30:00 10:00
    Arnold Musclehugger 30:00 10:00
    Homie TheClown 30:00 10:00

    This is unfortunately a project I'm working on for my Internship through my college for my AAS degree. I'm close to completetion but I've only got 4 weeks left to finish this project! So any help would be appreciated, especially if it's FAST help.


    Thanks,
    Eiredrake

  2. #2
    New Member
    Join Date
    Nov 2000
    Location
    Dublin, Ireland
    Posts
    10
    [QUOTE]Originally posted by Eiredrake
    [B]I have a bit of a problem and I cant' seem to find away around it. Any help would be appreciated.
    //////////////////

    Now I know what you're thinking.... Use the SUM() SQL statement. Well I tried that, and it's giving me an error because I am not trying to sum the ID and Date fields.

    What I need is basically this SQL statement:
    "SELECT SUM(Contr),SUM(Uncon),Date,IDNUM FROM [WeekData], FirstName,Lastname FROM [RepTable] WHERE IDNUM=" xx " AND Date BETWEEN " MondaysDate " AND " SaturdaysDate

    try it :
    "SELECT SUM(Contr) AS Sum_Contr ,SUM(Uncon) AS SUM_Uncon,Date,IDNUM FROM [WeekData], FirstName,Lastname FROM [RepTable] WHERE IDNUM=" xx " AND Date BETWEEN " MondaysDate " AND " SaturdaysDate

    s.e.a

  3. #3
    Member
    Join Date
    Jun 2000
    Location
    Perth Western Australia
    Posts
    41

    Cool

    Use this statement, it converts the times to Double and sum that conversion........... multiplied by 24 gives total hours (There may be a bit of work needed on the conversion accuracy, but try it out)

    SELECT LastName, FirstName, Sum(CDbl([Contr])) * 24 AS sumContr, Sum(CDbl([Uncon])) * 24 AS sumUncon
    FROM RepTable INNER JOIN Weekdata ON RepTable.IDNUM = Weekdata.IDNUM
    WHERE (((Weekdata.DATE) Between #2/11/2000# And #6/11/2000#))
    GROUP BY LastName, FirstName

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    May 2000
    Location
    Westminster, Md.
    Posts
    163

    Thanks!

    Thank you CTGS. That worked famously.


    Eiredrake

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2000
    Location
    Westminster, Md.
    Posts
    163

    Perhaps I'm missing someting....

    The final product is exactly what I'm asking for but I'm not sure how you got there... Could you explain a little further? I modded the original statement to include my actual field and table names....

    strSQL = "SELECT LastName, FirstName, Sum(CDbl([ControlledHours])) * 24 AS [Tot Ctr], Sum(CDbl([UncontrolledHours])) * 24 AS [Tot Unctr] FROM [CS-RepTable] INNER JOIN [DAT-DayStatsTable] ON [CS-RepTable].IDNUM = [DAT-DayStatsTable].IDNUM WHERE ((([DAT-DayStatsTable].DATE) Between" & "#" & tbsWeek.Tabs(1).Tag & "# AND #" & tbsWeek.Tabs(6).Tag & "#)) GROUP BY LastName, FirstName"

    Isn't this selecting Lastname, Firstname ControlledHours and UncontrolledHours from the [CS-RepTable]??

    The actual data resides in the [DAT-DayStatsTable]. But it's still coming out correctly even if I change the data in the [DAT-DayStatsTable]. Am I missing something here?

    Not that I'm not greatful for the help or anything, I just want to know how it works naturally =)


    Eiredrake

  6. #6
    Member
    Join Date
    Jun 2000
    Location
    Perth Western Australia
    Posts
    41

    Cool Re: Perhaps I'm missing someting....

    Originally posted by Eiredrake
    The final product is exactly what I'm asking for but I'm not sure how you got there... Could you explain a little further? I modded the original statement to include my actual field and table names....

    strSQL = "SELECT LastName, FirstName, Sum(CDbl([ControlledHours])) * 24 AS [Tot Ctr], Sum(CDbl([UncontrolledHours])) * 24 AS [Tot Unctr] FROM [CS-RepTable] INNER JOIN [DAT-DayStatsTable] ON [CS-RepTable].IDNUM = [DAT-DayStatsTable].IDNUM WHERE ((([DAT-DayStatsTable].DATE) Between" & "#" & tbsWeek.Tabs(1).Tag & "# AND #" & tbsWeek.Tabs(6).Tag & "#)) GROUP BY LastName, FirstName"

    Isn't this selecting Lastname, Firstname ControlledHours and UncontrolledHours from the [CS-RepTable]??

    The actual data resides in the [DAT-DayStatsTable]. But it's still coming out correctly even if I change the data in the [DAT-DayStatsTable]. Am I missing something here?

    Not that I'm not greatful for the help or anything, I just want to know how it works naturally =)


    Eiredrake

    The key to the SQL statement is this part........

    FROM [CS-RepTable] INNER JOIN [DAT-DayStatsTable] ON [CS-RepTable].IDNUM = [DAT-DayStatsTable].IDNUM

    It is forming a join between the two tables on the unique key fields [IDNUM] which resides in both DAT-DayStatsTable & CS-RepTable

    So, effectively you can reference the fields from either table as if it were one. This is a common method of joining One to Many relationship tables and you can build the statement to join many tables not just two.

    The JOIN clause can also be used as LEFT JOIN or RIGHT JOIN if you need to retrieve all from one table not just the values where there is a match. LEFT being the first table referenced in the SQL statement. Unfortunately you will get an Invalid Use Of NULL Error if you try it on your example because CDbl([Contr]) would try to convert a null field.

    The conversion of the hours fields was a bit of a fluke I came across one day, but it seems to work OK to give a fraction of a day.

    Hope this is a little more useful.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2000
    Location
    Westminster, Md.
    Posts
    163
    Very interesting..

    Thank you, the textbook didn't explain it like that. This makes a lot more sense.

    so I can reference both tables as though they were one and all fields will be included that I specify... that's handy to know.



    Eiredrake

Posting Permissions

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



Click Here to Expand Forum to Full Width