|
hi everybody,
I have a table whith a lot an invoice (2007, 2008, 2009 etc)and I want to group the invoice by years (2007, 2008 etc). A client may have many invoice every year.
I try something but I didn't find a solution to SUM value of invoice for all years separately:
SELECT SUM(total_sales) AS year_1, Client
FROM vanzari_totale
GROUP BY DATEPART(
Database is MS Access.
Please is urgent.
Thanks in advance!
|
|
|
|
|
sorin_zan wrote: Please is urgent.
It's only urgent to you. To a bunch of people who volunteer their time for free, it really doesn't matter.
You have to specify one or more column names in the GROUP BY clause, not actual data.
|
|
|
|
|
Not clear.
Why not just pull all invoices by year?
or do you need them totaled?
Or do you want them sorted?
More info please.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC Link[ ^]
Trolls[ ^]
|
|
|
|
|
Hi!
Could you please check this one: ( without calling DatePart() )
SELECT SUM(total_sales) AS year_1, Client
FROM vanzari_totale
GROUP by invoice_date, client
|
|
|
|
|
Nope. This will group on a daily basis, each distinct invoice_date value belongs to a different group. However, I have a solution, see my other message.
|
|
|
|
|
thanks but don't work...
I find a solution:
TRANSFORM Sum(Vanzari.Valoare) AS SumOfValoare
SELECT Vanzari.Client
FROM Vanzari
GROUP BY Vanzari.Client
PIVOT Month([Data] )
|
|
|
|
|
Hi,
1.
you're in the wrong forum, this is a database question, not a VB one. So the SQL or Database forums are more appropriate.
2.
this works fine on SQL Server:
SELECT COUNT(1), year(payment_date) as year FROM payments GROUP BY year(payment_date)
However it fails on Access, and so does this:
SELECT COUNT(1), datepart("yyyy", payment_date) as year FROM payments GROUP BY datepart("yyyy", payment_date)
However this does it:
SELECT COUNT(1), datepart("yyyy", payment_date) as yr FROM payments GROUP BY datepart("yyyy", payment_date)
so the only problem was SQL Server did and Access didn't accept "year" as an alias (it also is a function name).
[ADDED]: one can use square brackets when there is a risk of clashing with reserved keywords, like so:
SELECT COUNT(1) as [count], datepart("yyyy", payment_date) as [year] FROM payments GROUP BY datepart("yyyy", payment_date)
[/ADDED]
Luc Pattyn [My Articles] Nil Volentibus Arduum
modified on Wednesday, August 24, 2011 12:09 AM
|
|
|
|
|
Thanks a lot for your kind answer, Luc!
Now I'll try your suggestions.
SELECT COUNT(1) as [count], datepart("yyyy", payment_date) as [year] FROM payments GROUP BY datepart("yyyy", payment_date)
work good.
But, there is a problem:
1. I would like to do something this
client | 2007 | 2008 | 2009 | 2010 | 2011 |
-----------------------------------------------------------
client_1 |1,200.78$ |267.89$ |.... |.... |..... |
-----------------------------------------------------------
client_2 |678.67$ |5,76.00$|.... |.... |..... |
means that, on each column to has a sum of all invoice from that year
Sorry for my english...
Could you help me again?
|
|
|
|
|
Have a look at this query:
select SUM([total_sales]) as [Total], [Client],year([invoice_date]) as [InvoDate]
FROM vanzari_totale
group by [client], year([invoice_date])
|
|
|
|
|
thanks for your kind post, but your solution isn't one good for me because the years appear in row not in column
3545,3 2007 client_1 1
433,33 2008 client_1 1
77756,1 2009 client_1 1
3423,7 2010 client_1 1
1234,44 2011 client_1 1
I think that the only solution is to use TRANSFORM.....PIVOT
Right? Or there is another solution?
Please let me know if is true.
Thanks a lot for your support!
|
|
|
|
|
The format you have provided for output, it seems that you want to use it in chart control? Is it right?
|
|
|
|
|
No, it's only interogate for a datagrid. I want to do this for a rapid information of sales agents about history of customer. It's a small office application...
|
|
|
|
|
LUC,
I think that the solution is:
TRANSFORM Sum(Vanzari.Valoare) AS SumOfValoare
SELECT Vanzari.Client
FROM Vanzari
GROUP BY Vanzari.Client
PIVOT Month([Data]);
and work good, this I have a column for each year and sum of invoice/year/client
|
|
|
|
|
OFFFF!!!
It didn't work.
TRANSFORM is not recognize in design mod, using Query Wizard of TableAdapter.
|
|
|
|
|
Dim op As String
Option Explicit
Dim i As Integer
Dim exp1 As Double
Dim exp2 As Double
Dim Result As Double
Dim count1 As Integer
Dim scitype As String
Private X As Double
Dim mem As Variant
Private Sub Command1_Click(Index As Integer)
If count1 = 0 Then
Text1.Text = " "
MsgBox ("Calculator is not on")
End If
If count1 = 1 Then
Text1.Text = " "
count1 = count1 + 1
End If
If count1 > 1 Then
Text1.Text = Text1.Text & command1(Index).Caption
End If
End Sub
Private Sub Command10_Click()
Text1.Text = mem
End Sub
Private Sub Command2_Click()
Text1.Text = -Val(Text1.Text)
End Sub
Private Sub Command3_Click()
If count1 > 0 Then
exp2 = Val(Text1.Text)
Select Case (op)
Case "+"
Result = exp1 + exp2
Text1.Text = Result
count1 = 0
Case "-"
Result = exp1 - exp2
Text1.Text = Result
count1 = 0
Case "*"
Result = exp1 * exp2
Text1.Text = Result
count1 = 0
Case "/"
Result = exp1 / exp2
Text1.Text = Result
count1 = 0
Case "%"
Result = (exp1 / 100) * exp2
Text1.Text = Result
count1 = 0
End Select
End If
End Sub
Private Sub Command4_Click(Index As Integer)
Result = exp1
exp1 = Result + Val(Text1.Text)
Text1.Text = " "
op = Command4(Index).Caption
End Sub
Private Sub Command5_Click()
Result = 0
exp1 = 0
exp2 = 0
Text1.Text = " "
count1 = 1
End Sub
Private Sub Command6_Click()
count1 = 0
Text1.Text = ""
End Sub
Private Sub Command7_Click()
Result = 0
exp1 = 0
exp2 = 0
count1 = 1
Text1.Text = "0"
End Sub
Private Sub UpdateLog()
Trim (Form1.Text1.Text)
End Sub
Private Sub Command8_Click(Index As Integer)
scitype = Command8(Index).Caption
Select Case (scitype)
Case "sin"
Text1.Text = (Text1.Text * 3.14) / 180
Text1.Text = Math.Sin(Val(Text1.Text))
count1 = 0
Case "cos"
Text1.Text = (Text1.Text * 3.14) / 180
Text1.Text = Math.Cos(Val(Text1.Text))
count1 = 0
Case "tan"
Text1.Text = (Text1.Text * 3.14) / 180
Text1.Text = Math.Tan(Val(Text1.Text))
count1 = 0
Case "sqrt"
Text1.Text = Math.Sqr(Val(Text1.Text))
count1 = 0
Case "abs"
Text1.Text = Math.Abs(Val(Text1.Text))
count1 = 0
Case "rnd"
Text1.Text = Math.Rnd(Val(Text1.Text))
count1 = 0
Case "log"
Text1.Text = Math.Log(Val(Text1.Text))
count1 = 0
Case "Atn"
Text1.Text = Math.Log(Val(Text1.Text))
count1 = 0
End Select
End Sub
Private Sub Command9_Click()
mem = Text1.Text
End Sub
|
|
|
|
|
And the error is where? and what?
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
SIR i don't know how to fix it in command botton please help me u have facebook? please add me michaelgsm22@yahoo.com I w8 your response
modified on Monday, August 22, 2011 10:23 AM
|
|
|
|
|
No need to discuss this on Facebook, since you probably have realised, that this is a - as you also might have guessed from the page menu already - "discussion forum". Isn't that nice? So no need to change to a stupid platform for which you have to do a new registration in case you're not a member yet.
By the way, it's "I wait", not "I w8". Don't use textspeak, you are not five years old (probably).
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
So, Can you tell us from where you copy this code?
|
|
|
|
|
in my proff. this my project in 1st sem sir help me!
|
|
|
|
|
Add all the command buttons and text boxes. For Command1 only (as the code tell us), copy it & paste it to form to create an array of command. I don't know how long the array would be.
|
|
|
|
|
Tells us what is going wrong - does it fail to run? give the wrong answers? bomb out in the middle of a calculation?
If you do that someone might help. I guess however that the most profitable advice would be to stop using VB6 and use VB.Net instead.
Posting your address on Facebook is not likely to bring results and is not a good idea.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
|
sir the code is ok but i do'nt know how to fix it in command buttons huh !
|
|
|
|
|
Describe what you are after doing and which command button that you want it doing.
But as people have said VB6 isn't a supported language anymore and most people that do VB in its many guises have moved across to .net
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
IIRC double click the button in the designer. That takes you to the code window with a stub for the buttons click event. Put your code in the stub.
I think the last time I used VB6 was in 2000 so it's a bit rusty. You might be able to do it from the properties window in the designer - I have a sneaking feeling that you can but it is better to use double click method. VB6 seemed to get confused otherwise (again I might have misremembered.)
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
