Difficult SQL statement problem

[Eiredrake]

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

[StarE]

[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

[CGTS]

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

[Eiredrake]

Thank you CTGS. That worked famously.


Eiredrake

[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

[CGTS]

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.

[Eiredrake]

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