problem with query

[abu taher]

I made a cross tab query. please see the attach file. the sum of category show in textbox in my project. like:
Stationary = 2000.00
Machinery = 5000.00
Salary = 2200.00
here I can write sql command like:

select sum(stationery) from table1

but here I know the category"stationery" so I write it. but the category will be various. that I don't know what. so how will be the sql command here, that show the sum of category.

[manoshailu]

Hi,
Try the following Script:

TRANSFORM Sum(item.Tk) AS [Total Of Tk]
SELECT item.Category, Sum(item.Tk) AS [Total of Category]
FROM item
GROUP BY item.Category
PIVOT item.Item;

Have a Great Day
Shailaja :)

I made a cross tab query. please see the attach file. the sum of category show in textbox in my project. like:
Stationary = 2000.00
Machinery = 5000.00
Salary = 2200.00
here I can write sql command like:

select sum(stationery) from table1

but here I know the category"stationery" so I write it. but the category will be various. that I don't know what. so how will be the sql command here, that show the sum of category.

[abu taher]

nice . but I want to know another thing. here the category show in row. then how I show it in two text box in vb6. I mean the total sum of maintenance and stationery in show in two text box.

[vb5prgrmr]

Are you asking how to create controls at runtime to allow your users to view all of the returned results?

Set textboxes indexes to zero (0) and do something like...

Dim TextBoxCounter As Integer

Rs.MoveFirst
Text1(0).Text = Rs.Fields("Category")
Text2(0).Text = Rs.Fields("TheSum") 'instead of above AS [Total of Category]

Rs.MoveNext

TextBoxCounter = 1
Do While Not Rs.EOF
  Load Text1(TextBoxCounter)
  Text1(TextBoxCounter).Left = Text1(0).Left
  Text1(TextBoxCounter).Top = Text1(TextBoxCounter - 1).Top + Text1(0).Height + 30
  Text1(TextBoxCounter).Text = Rs.Fields("Category")
  Text1(TextBoxCounter).VisIble = True
  
  Load Text2(TextBoxCounter)
  Text2(TextBoxCounter).Left = Text2(0).Left
  Text2(TextBoxCounter).Top = Text2(TextBoxCounter - 1).Top + Text2(0).Height + 30
  Text2(TextBoxCounter).Text = Rs.Fields("TheSum")
  Text2(TextBoxCounter).VisIble = True
  
  TextBoxCounter = TextBoxCounter + 1
  Rs.MoveNext
Loop

or you could use recordset navigation buttons (movefirst, moveprevious, movenext, movelast) Or you could use a grid of some sorts to populate the data.

Good Luck

[abu taher]

Text2(0).Text = Rs.Fields("TheSum") 'instead of above AS [Total of Category]

I don't understand this line.

or you could use recordset navigation buttons (movefirst, moveprevious, movenext, movelast) Or you could use a grid of some sorts to populate the data.

I think If I use this then it show one row. but I want total.

[vb5prgrmr]

You really need to read or remind yourself of what has been written in these threads. The query by mano can return more than one record and in that query mano use the alias key word AS [this name], my post was instead of qualifying the alias with the [] brackets you just use a single descriptive word, thesum.

Yes, if you use recordset navigation it will only show one record at a time but it was ment as an suggestion to show the results of mano's query which does show totals...

[abu taher]

thanks. actually I want to do different. so if you are don't mind then I can say it.

[ljwolf67]

I made a cross tab query. please see the attach file. the sum of category show in textbox in my project. like:
Stationary = 2000.00
Machinery = 5000.00
Salary = 2200.00
here I can write sql command like:

select sum(stationery) from table1

but here I know the category"stationery" so I write it. but the category will be various. that I don't know what. so how will be the sql command here, that show the sum of category.

When you created you cross-tab in Access, click the sql button located on the right bottom corner. You can then copy the sql statement. Depending on where you copy it to, you may have some slight changes to make.