• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

need experts help

hi
hope can help me with this
have table
acc no,accname,credit
 1        A     100
 2        B     200

this my code and work good
For i = 1 To MSFlexGrid1.Rows - 1
 rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1)), adSeekFirstEQ
       If Not rec.EOF Then
    bal = rec!credit
    rec!credit = bal + MSFlexGrid1.TextMatrix(i, 2)
    rec.Update
   Else
    sql = "insert into xx(ACCno,ACCNAME,credit)values(" & MSFlexGrid1.TextMatrix(i, 1) & ",'" & MSFlexGrid1.TextMatrix(i, 3) & "'," & MSFlexGrid1.TextMatrix(i, 2) & ")"
   db.Execute sql
End If

problme:
i want make this for each month and of course it will not
be good idea to make 12 tables for 12 months

i want make same code but if month changed execute same code but with new month

will be very greatfull with any help;wait expert s help
thanks
0
egkids
Asked:
egkids
  • 16
  • 10
  • 3
1 Solution
 
egkidsAuthor Commented:
hope my question is uploaded!!
0
 
bukkoCommented:

What about adding a Month collumn, rather than 12 tables?
You can make the month part of the primary key if required, so keeping it unique.

bukko
0
 
egkidsAuthor Commented:
i can't change accno i want make new fields maybe name month how add month only in col?

month  accno  accname  credit
1      101     a        100

2      101     a         70



as u can see  from my code in first post
check if A exist then edit credit if not exist then add new

but want separate months
hope u understand what i want to do

thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bukkoCommented:

That would still work.
Check if a record exists for Account A and Month 1, add if not.
This will work in the same way as your 12 tables idea but it will be more relational and will give you less problems in the future.
If you list each function you want to perform, I'll show you how.

bukko
0
 
bukkoCommented:

Also, you don't need to change the account number to change the primary key.
The primary key can be a composite of the account number and the month number.

bukko
0
 
egkidsAuthor Commented:
hmmm sound good" will check for key and month"
ok how can add month only in the table
and how check with both?

please tell me more if u can add sample code will be great

thanks
0
 
applayerCommented:
Hello,

I agree with bukko in that adding a month or a date column is definitely the way to go.  Here is a suggestion for your primary key though...You can create a column for your primary key which is just a number that auto-increments and use that as your primary key.

I know that you probably won't ever want to display that in your grid...but here's a trick you can use:  Keep a column in your grid and fill it with the auto-incremented number which you use for your primary key...just mind the spacing of your columns and you can insure that the column with your primary key is never actually visible on the form!

Another possible suggestion is that you can loop through the table containing your data, and based on the values of your data you can configure and fill the values which get displayed on your data grid.  I'm listing below a huge chunk of code from one of my programs...it's messy, and not applicable directly to your situation...but it will show you how to loop through a table, and based on the values you find in the table configure and display information in your data grid.

In my code below, the data found in "gridClientData.Col = 5" is the unique ID for that record, and it comes from the database based on a column which is set to auto-increment.  I just set up my data grid so that the column widths ended up such that .col 5 never displays on the form.  

Anyway, that's a lot of info, but I hope some of it might help you out.  If you go the route of the unique identifier using an auto increment column...you only have to check if the person has clicked on a row where the column matches your ID...not the month and ID.

One last suggestion is that you can use a variable in your forms to store your unique ID, and then you go check that variable against the database, and the grid when somebody clicks on the grid to see if you are in the right place.

here's some code from one of my apps:

Private Sub FillDataGrid()
  Dim adoConn As Connection
  Dim adoRS As Recordset
  Dim strConn As String
  Set adoConn = New Connection
  Set adoRS = New Recordset
  strConn = "DSN=CSI;"
  adoConn.Open (strConn)
  adoRS.Open "select * from SurveyAnswers", adoConn, adOpenKeyset, adLockReadOnly

  Dim x
  Dim strSplit() As String
  Dim strTemp As String
  If adoRS.EOF = True Then
    gridClientData.Rows = 1
    Exit Sub
  End If

  adoRS.MoveLast
  adoRS.MoveFirst

  While Not adoRS.EOF
    If adoRS("Archive") = "No" Then
    intOkayToSort = 1
    gridClientData.Rows = x + 2
    gridClientData.Row = x + 1
    gridClientData.RowHeight(x + 1) = 275
    gridClientData.BackColorSel = RGB(15, 51, 83)
    'gridClientData.BackColorSel = RGB(230, 130, 34)
    '-----------------------------------------------
    gridClientData.Col = 1
    If Len(adoRS("ClientID")) > 0 Then
      gridClientData.Text = adoRS("ClientID")
    Else
      gridClientData.Text = " "
    End If
    '-----------------------------------------------
    gridClientData.Col = 2
    gridClientData.Text = adoRS("ClientFirstName") & " " & adoRS("ClientMiddleName") & " " & adoRS("ClientLastName")
    '-----------------------------------------------
    gridClientData.Col = 3
    gridClientData.Text = adoRS("CounselorFirstName") & " " & adoRS("CounselorLastName")
    '-----------------------------------------------
    gridClientData.Col = 4
    If Len(adoRS("DateTimeStart")) > 0 Then
      strTemp = adoRS("DateTimeStart")
      strSplit = Split((strTemp), " ")
      If Len(strSplit(0)) > 0 Then
        gridClientData.Text = Trim(Format(strSplit(0), "mm/dd/yyyy"))
      Else
        gridClientData.Text = " "
      End If
    End If
    '-----------------------------------------------
    gridClientData.Col = 5
    If Len(adoRS("CaseID")) > 0 Then
      gridClientData.Text = adoRS("CaseID")
    Else
      gridClientData.Text = " "
    End If
    '-----------------------------------------------
    x = x + 1
    End If
    adoRS.MoveNext
  Wend
  adoRS.Close
  adoConn.Close

End Sub
0
 
egkidsAuthor Commented:
hi
i add coulmn in table name month
my table now
month - accno - accname - credit
 
problme i want to seek for both accno and month if found both (true and true=true) then do my code

but i can't make key for month because there are many acc under same month i have key for accno only
can seek without key? only index with month field??
i don't know i feel  lost my way again

text4 is name of month, but not working

rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), "& text4 &"), adSeekFirstEQ

0
 
egkidsAuthor Commented:
help me to correct this
rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), (Text4.Text)), adSeekFirstEQ

0
 
egkidsAuthor Commented:
hellooo experts group!!
0
 
applayerCommented:
hello egkids...

"help me to correct this
rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), (Text4.Text)), adSeekFirstEQ"

I would drop this approach if I were you...

if i was you then i would have a table with the following columns...id would be a column set to auto-increment that is sure to be unique.

id - month - day - year - time - ampm - accno - accname - credit

1. the columns i would add are id, day, year, time, and "ampm", where you code whether it is am or pm.

2. set your column id to be of type integer, or bigint, and set it to auto-increment.

3. forget (seriously!) the code you are currently working with that loops through the number of rows in your grid control

4. loop through the table in your database and fill the data grid with the information you need.






0
 
applayerCommented:
hello egkids...

"help me to correct this
rec.Seek Array(MSFlexGrid1.TextMatrix(i, 1), (Text4.Text)), adSeekFirstEQ"

I would drop this approach if I were you...

if i was you then i would have a table with the following columns...id would be a column set to auto-increment that is sure to be unique.

id - month - day - year - time - ampm - accno - accname - credit

1. the columns i would add are id, day, year, time, and "ampm", where you code whether it is am or pm.

2. set your column id to be of type integer, or bigint, and set it to auto-increment.

3. forget (seriously!) the code you are currently working with that loops through the number of rows in your grid control

4. loop through the table in your database and fill the data grid with the information you need.






0
 
bukkoCommented:

If your primary key was a composite of account number and month, then it would be unique.

For example:

Acc   Month  
A     1      (A*1)
A     2      (A*2)
B     1      (B*1)
B     2      (B*2)

However, you can also do as applayer suggests (twice) and use an entirely separate primary key.
Either was would work.
the only problem with using Seek is that you can only search one column at a time.
Better to use Find, e.g.:

rec.Find "acno='" & acc_no & "' and month=" & month

Regards

bukko
0
 
egkidsAuthor Commented:
hi all
applayer  i think u don't know what is the problme here
maybe i wasn't clear enough!!
no problme to fill flexgrid with data  i take that from another table and then user choose what enry he/she want to post to ledger and my code make some modification to this journal entry in grid that why make loop through the grid and post it to ledger
and can make trial balance but the problme i want make trial balance for each month only that mean i should sepreate ledger for each month but i won't do that i want make another table as i said in first post
month accno accname credit debit
1      101    a      100    150
2      101    a      50      50

i think i will try  bukko  suggestion
but does find working fast as seek
i use access xp can make composite with it

thanks for all suggestions :))

0
 
egkidsAuthor Commented:
hi all
thanks bukko for help
i add new col in table
Acc   Month  
A     1      (A*1)
A     2      (A*2)
B     1      (B*1)
B     2      (B*2)
and it is working good

u r great bukko :)))

may i ask something then will close this question

about vb data report " i don't use data environment"

will ask the user in my form to use date between ... and ....
just want to know how this two date can send from texbox to appear in header of my report in lable
can send sql to data sourcer of my report? how?

pleaase if u know any resourcer about data report tell me
thanks so much; u all great help

:))
0
 
bukkoCommented:

What are you using to produce the report?

bukko
0
 
egkidsAuthor Commented:
bukko you are the best :))
0
 
egkidsAuthor Commented:
use vb 6 data report
lol we add comment at same time
0
 
egkidsAuthor Commented:
very interested to talk with u if u like here it is my mail
sourcer28@hotmail.com
:))
0
 
bukkoCommented:

Have you tried something like:

DataReport1.Show
DataReport1.Sections(0).lblDateFrom.Caption = "01/01/01"

I'm not familiar with the data report, but looking on the web it seems as if you can modify the fields in each section manually.

Post back if this is no good.

bukko
0
 
egkidsAuthor Commented:
user put in textbox date (between ... and ...)
want this date show in report
i make my report like this
With DataReport1
          Set .DataSource = Nothing
              .DataMember = ""
          Set .DataSource = rec.DataSource
              With .Sections("section1").Controls
                 For i = 1 To .Count
                   If TypeOf .Item(i) Is RptTextBox Then
                             .Item(i).DataMember = ""
                     .Item(i).DataField = rec.Fields.Item(i - 1).Name
                     End If
                  Next i
               End With
             .show
            End With
0
 
bukkoCommented:

I assume from your code that .Sections("section1") is the report body.
Does that mean that .Sections("section0") is the header?

If so, try this:

Dim strFromDate As String
Dim strToDate As String

strFromDate = "2003/01/01" ' Get this from user input
strToDate = "2003/02/01"   ' ...and this
With DataReport1
         Set .DataSource = Nothing
             .DataMember = ""
         Set .DataSource = rec.DataSource
             With .Sections("section0").Controls
                ' You may need to modify these index values
                ' to be those in your header.
                ' Also, I'm guessing that the correct property
                ' to set is "text" - it might be "value", etc.
                .Item(0).Text = strFromDate
                .Item(1).Text = strToDate
             End With
             With .Sections("section1").Controls
                For i = 1 To .Count
                  If TypeOf .Item(i) Is RptTextBox Then
                            .Item(i).DataMember = ""
                    .Item(i).DataField = rec.Fields.Item(i - 1).Name
                    End If
                 Next i
              End With
            .show
           End With

Regards

bukko
0
 
egkidsAuthor Commented:
hi bukko
i add new question hope u can help me :))
i have table

KEY  VAL
5     100
2     20
2     35
5     70

duplicate value for each key
how can make aggregation for each key
i mean i want seek for each key >1 and take sum(val)and put in another table like this

key  val
5    170
2    55
thanks for help

actully it problme with tree view children
want take total val for some nodes :))
0
 
bukkoCommented:

The following SQL will return the values the way you want:
(You didn't say the table name, so I'll assume my_table)

SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key

To insert these into another table:
(e.g. new_table)
INSERT INTO new_table SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key

Let me know how you get on.

Regards

bukko
0
 
egkidsAuthor Commented:
hello bukko the best programmer on the web:)))
thanks so much for help

about the problme i make what i want but u know have feeling can do better and maybe working not perfect in future :((

let me tell u what is going on here
the problme i have trial table have all accounts and want make trial with total i mean customers accounts want it show under customers with total of them not individulas for each one and the same for payable and inventory
so i was try take nodes children accounts under the parent

my first table test it trial with detials
ACCNAME, total credit,total debit,parent of this node

i made another table test2 where i put the aggregation of node
db.Execute "insert into test2 SELECT test.parent, Sum(test.totatD) AS totatD,Sum(test.totalC) AS totalC,tree.ACCNAME FROM test INNER JOIN tree ON test.parent = tree.key WHERE (((test.parent)<'4_')) GROUP BY test.parent, tree.ACCNAME"

and then i take the rest of accounts i didn't make aggregation to them like capital

db.Execute "INSERT INTO test2 ( ACCNAME, totatD, totalC, parent ) SELECT test.ACCNAME, test.totatD, test.totalC, test.parent FROM test WHERE (((test.parent)>'4_'  And (test.parent)<>'6_'))"

sound silly i bet u have alot of things better than read what am i doing
any way i'm very greatfull to you
:))
egkids




0
 
bukkoCommented:

That looks good to me.
I'm unsure whether you are recreating your totals each time or whether you intend to update after each change.
If you update after each change, you might want to consider using a transaction. That way if you get an error, you can roll back the whole transaction and your totals will stay in sync with the individual records.

Regards

bukko
0
 
egkidsAuthor Commented:
hi bukko
about this part of sql
test.parent = tree.key WHERE (((test.parent)<'4_')) GROUP BY test.parent
is there any way to start Descending not Ascending
because i want start with biger number of parent to make aggregation  starting with last child ;
it start with next number after 4 and it is 2-bankeg
A-bank
    2-bankeg
       3-bankeg111(last child)


want start with 3 put in 2 then put 2 in A

LOL i bet u want shoot me now

:))))
0
 
egkidsAuthor Commented:
hi'
mostly i use insert into sql
have 2 question
if 2 user add new recoreds at same time can make problme
because i didn't use open table or adoptimistic?

2-about the case u told me
INSERT INTO new_table SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key
can use sync with it?

thanks
0
 
bukkoCommented:

To sort descending...
ORDER BY column DESC
You can also mix ascending and descending with different columns, e.g.:
ORDER BY column1 DESC, column2, column3 DESC, etc...

The reason I asked which database you are using is because most of your problems can be fixed more easily if you are using SQL Server. This is especially the case with multiple users.

bukko




0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now