Help with another Select statement please?

[Jacksparrow87]

Hi people,

I was hoping someone could offer me some help please, I have two select statements which work and show me different information but how do I join them to show the information I need?

The first select statement is as follows:

"Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"

The second statement is:

"Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN ? AND ? And CompID = ? And NoteNumber = 1 Group By [Company], [ChaCode]"

What I want to show is the same data as select statement 2 (only where Notenumber = 1) however I want to include the Sum TSpent for all the data (not only where notenumber = 1). For example something like the following:

"Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log where Dol BETWEEN ? AND ? And CompID = ? And NoteNumber = 1 Group By [Company], [ChaCode]"

However the trouble with the above statement is that it calculates the SUM of TSpent only from notenumber1 where it should calculate it from all rows.

If you dont understand please tell me, thanks.

 

[mansii]

Perhaps you need a JOIN query. Something like:

SELECT Selection1.Company, Selection1.ChaCode, Selection1.NumberofChargeCodes, Selection1.SumofTimeSpent  
FROM
[blue](Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN ? AND ? And CompID = ? And NoteNumber = 1 Group By [Company], [ChaCode]) AS Selection1[/blue] 
INNER JOIN
[green](Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [NoteNumber]) AS Selection2[/green] 
ON ([blue](Selection1.Company[/blue]=[green]Selection2.Company[/green] AND [blue]Selection1.ChaCode[/blue]=[green]Selection2.ChaCode[/green])
WHERE [green]Selection2.NoteNumber[/green] = 1

Note that the INNER JOIN may only works on MS Access mdb.
Hope this helps.

 

[Jacksparrow87]

Yep, I thought I might need to juse a Join somewhere.

However I tried that and it didn't work, it broke on the adapter.fill line.

 

[jebenson]

Put the adapter.fill code in a Try...Catch block, then in the Catch part put code to display the error message:

Try
Adapter.Fill(Table)
Catch ex As Exception
MsgBox(ex.Message)
End Try

Hopefully the error message will give you a better indication of where the SQL is failing.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Jacksparrow87]

Hi thanks for that, well it says 'Syntax error in Join operation'

Thanks

 

[Sorwen]

That statement is missing a closing some where. There are two opens on the on, but then only one close.

SELECT Selection1.Company, Selection1.ChaCode, Selection1.NumberofChargeCodes, Selection1.SumofTimeSpent  
FROM
[blue](Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN ? AND ? And CompID = ? And NoteNumber = 1 Group By [Company], [ChaCode]) AS Selection1 [/blue]
INNER JOIN
[green](Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [NoteNumber]) AS Selection2 [/green]
ON [red](([/red][blue]Selection1.Company[/blue]=[green]Selection2.Company[/green] AND [blue]Selection1.ChaCode[/blue]=[green]Selection2.ChaCode[/green][red])[/red]
WHERE Selection2.NoteNumber = 1

Did you already fix that or just copy pate?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Jacksparrow87]

I copied and pasted the whole statement, sorry I dont understand what you mean, could you elaborate ir make changes to the statement please?

thanks

 

[Sorwen]

I think they meant to put:

WHERE Selection2.NoteNumber = 1[red])[/red]

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[mansii]

As I mentioned, the INNER JOIN may only works on MS Access mdb.
Find similar JOIN syntax for the database you are using.

 

[Jacksparrow87]

I am using Access though, Thanks Sorwen I changed it as you suggested and now it has the following error:

Syntax Error (missing operator) in query expression '(Selection1.Company=Selection2.Company AND Selection1.ChaCode=Selection2.ChaCode)
WHERE Selection2.NoteNumber = 1.

Any ideas? Thanks

 

[Jacksparrow87]

Update I got the following to work without any errors:

Code:
SELECT Selection1.Company, Selection1.ChaCode, Selection1.NumberofChargeCodes, Selection1.SumofTimeSpent 
FROM (Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where NoteNumber = 1 Group By [Company], [ChaCode]) AS Selection1
INNER JOIN (Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log Group By [Company], [ChaCode], [NoteNumber]) AS Selection2 ON (Selection1.Company=Selection2.Company AND Selection1.ChaCode=Selection2.ChaCode) WHERE Selection2.NoteNumber = 1

However it does not show any data, it does show the column headings but no data. This is so annoying

 

[mansii]

1. Check the result for each subquery.
2. Try to remove the "where NoteNumber = 1" from Selection1

Good luck.

 

[Jacksparrow87]

Will do, thanks

 

[Sorwen]

Easy way to do this is build the query in access until it pulls back the data you want then just cut paste the sql.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Jacksparrow87]

I tried that but where it says 'Enter parameter value' what do I input there? Thanks

 

[Jacksparrow87]

Hi,

there is sufficient data in the table to show some records, for example imagine on my vb form I have three buttons.

Button 1 has the following select statement:

"Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"

Button 2 has the following select statement:

"Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where Dol BETWEEN ? AND ? And CompID = ? And NoteNumber = 1 Group By [Company], [ChaCode]"

Now both of these statements show the data that I want to see (the only problem is I need to join them),

Button 3 (the select statement Im trying to get to work) has the following select statement:

"SELECT Selection1.Company, Selection1.ChaCode, Selection1.NumberofChargeCodes, Selection1.SumofTimeSpent FROM (Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes] From Log where NoteNumber = 1 Group By [Company], [ChaCode]) AS Selection1 INNER JOIN (Select [Company], [ChaCode], COUNT(ChaCode) As NumberofChargeCodes, SUM(TSpent) As SumofTimeSpent From Log Group By [Company], [ChaCode], [NoteNumber]) AS Selection2 ON (Selection1.Company=Selection2.Company AND Selection1.ChaCode=Selection2.ChaCode) WHERE Selection2.NoteNumber = 1"

I know what you mean when you ask me if there is the correct data in the table, I know there is correct data because Button 1 and Button 2 show that there is data in the table so Button 3 should not have a problem trying to run the sql statement.

Thanks