Writing a little app to modify a file

15 Apr 2005 12:33 PM

Michael D.

Hello all!

I'm trying to design and write a little application for someone
in my office, but I'm having some trouble. I have the following database
structure (Note: just some sample data, obviously):

SystemNumber    SystemNumberMAX    SystemName    SystemKey
9007404                                        Windows Test 1        A
9007405                                        Windows Test 1        A
9007406                                        Windows Test 1        A
9008683                                        Windows Test 45        A
9008684                                        Windows Test 45        A
9017616                                        Windows Test 4595bg        A
9019528                                        Windows Test 1        B

The SystemNumberMAX field is to be populated with the maximum SystemNumber
for a group, where a group is a unique System Key and System Name combo. The
SystemNumberMAX is only to be filled when there is more than 1 System Number
for a group. Thus, when the module is complete, the above file set should
look like this:
SystemNumber   SystemNumberMAX    SystemName    SystemKey
9007404             9007406               Windows Test 1        A
9008683             9008684                          Windows Test 45        A
9017616                                        Windows Test 4595bg        A
9019528                                        Windows Test 1        B

I'm totally stumped though! I've only done stuff like affecting forms and
switchboards in Access/VBA, and wrote a VB.NET tic-tac-toe game. I'm
thinking the steps are:

1. Read the file in, and split it by commas (meaning I'll ascii-delimit it
from Access first)
2. Select the max SystemNumber # for the group and write it to a variable.
3. Delete the records in the range between the minimum and maximum
SystemNumber for the group (including the record for the max SystemNumber)
4. Write the max SystemNumber into the SystemNumberMAX field.

Can anyone offer guidance or example code for something similar?

Thanks! -Mike

15 Apr 2005 2:30 PM

Cor Ligthert

Michael,

I made a sample for you that needs a datagrid,
I have made the table, however that you can read of course direct from your
access database.

\\\\
Private Sub Form1_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As New DataTable
        dt.Columns.Add("Serial")
        dt.Columns.Add("Name")
        dt.Columns.Add("Key")
        Dim dtresult As New DataTable
        dtresult.Columns.Add("Serial")
        dtresult.Columns.Add("SerialMax")
        dtresult.Columns.Add("Name")
        dtresult.Columns.Add("Key")
        dt.LoadDataRow(New Object() {"9007404", "Windows Test 1", "A"},
True)
        dt.LoadDataRow(New Object() {"9007405", "Windows Test 1", "A"},
True)
        dt.LoadDataRow(New Object() {"9007406", "Windows Test 1", "A"},
True)
        dt.LoadDataRow(New Object() {"9008683", "Windows Test 45", "A"},
True)
        dt.LoadDataRow(New Object() {"9008684", "Windows Test 45", "A"},
True)
        dt.LoadDataRow(New Object() {"9017616", "Windows Test 4595bg", "A"},
True)
        dt.LoadDataRow(New Object() {"9019528", "Windows Test 1", "B"},
True)
        Dim dv As DataView = dt.DefaultView
        dv.Sort = "Name, Key"
        Dim dr As DataRow
        For Each drv As DataRowView In dv
            If dr Is Nothing Then
                dr = dtresult.NewRow
                dr(0) = drv(0)
                dr(1) = drv(0)
                dr(2) = drv(1)
                dr(3) = drv(2)

            Else
                If dr(2).ToString = drv(1).ToString And dr(3).ToString =
drv(2).ToString Then
                    dr(1) = drv(0)
                Else
                    dtresult.Rows.Add(dr)
                    dr = dtresult.NewRow
                    dr(0) = drv(0)
                    dr(1) = drv(0)
                    dr(2) = drv(1)
                    dr(3) = drv(2)
                End If
            End If
        Next
        dtresult.Rows.Add(dr)
        DataGrid1.DataSource = dtresult
    End Sub
///

I did not really check it, however the result looks for me something what it
has to be.

I hope this helps,

Cor

15 Apr 2005 2:35 PM

Larry Lard

One quick comment: If the data is already in Access, you should do this
with a query within Access rather than exporting and processing it. If
this is an acceptable solution just say if you want help with the query

:)

Michael D. wrote:
Show quoteHide quote

> Hello all!
>
> I'm trying to design and write a little application for someone
> in my office, but I'm having some trouble. I have the following
database
> structure (Note: just some sample data, obviously):
>
> SystemNumber    SystemNumberMAX    SystemName    SystemKey
> 9007404                                        Windows Test 1        A
> 9007405                                        Windows Test 1        A
> 9007406                                        Windows Test 1        A
> 9008683                                        Windows Test 45        A
> 9008684                                        Windows Test 45        A
> 9017616                                        Windows Test 4595bg        A
> 9019528                                        Windows Test 1        B
>
> The SystemNumberMAX field is to be populated with the maximum
SystemNumber
> for a group, where a group is a unique System Key and System Name

combo. The
Show quoteHide quote

> SystemNumberMAX is only to be filled when there is more than 1 System
Number
> for a group. Thus, when the module is complete, the above file set
should
> look like this:
> SystemNumber   SystemNumberMAX    SystemName    SystemKey
> 9007404             9007406               Windows Test 1        A
> 9008683             9008684                          Windows Test 45
A
> 9017616                                        Windows Test 4595bg        A
> 9019528                                        Windows Test 1        B
>
>
> I'm totally stumped though! I've only done stuff like affecting
forms and
> switchboards in Access/VBA, and wrote a VB.NET tic-tac-toe game. I'm

> thinking the steps are:
>
> 1. Read the file in, and split it by commas (meaning I'll

ascii-delimit it
Show quoteHide quote

> from Access first)
> 2. Select the max SystemNumber # for the group and write it to a
variable.
> 3. Delete the records in the range between the minimum and maximum
> SystemNumber for the group (including the record for the max
SystemNumber)
> 4. Write the max SystemNumber into the SystemNumberMAX field.
>
> Can anyone offer guidance or example code for something similar?
>
> Thanks! -Mike

15 Apr 2005 2:57 PM

Michael D.

Yea, it IS already in Access... Actually, it is an Excel spreadsheet that I
put into access. However, I was thinking about writing a little VB app that
they could put on their desktop, click a button to select the file, and then
output a new/fixed file for them. The reason for that is they get the data
from an outside source on a montly basis, so it's not a 1-time thing... Does
that make sense? A query or VBA module would work either though, if I have
the query, I'm good enough (JUST!) to build the vb.net app, I think...

Thanks a lot!
Mike

Show quoteHide quote

"Larry Lard" wrote:

>
> One quick comment: If the data is already in Access, you should do this
> with a query within Access rather than exporting and processing it. If
> this is an acceptable solution just say if you want help with the query
> :)
>
> Michael D. wrote:
> > Hello all!
> >
> > I'm trying to design and write a little application for someone
> > in my office, but I'm having some trouble. I have the following
> database
> > structure (Note: just some sample data, obviously):
> >
> > SystemNumber    SystemNumberMAX    SystemName    SystemKey
> > 9007404                                        Windows Test 1        A
> > 9007405                                        Windows Test 1        A
> > 9007406                                        Windows Test 1        A
> > 9008683                                        Windows Test 45        A
> > 9008684                                        Windows Test 45        A
> > 9017616                                        Windows Test 4595bg        A
> > 9019528                                        Windows Test 1        B
> >
> > The SystemNumberMAX field is to be populated with the maximum
> SystemNumber
> > for a group, where a group is a unique System Key and System Name
> combo. The
> > SystemNumberMAX is only to be filled when there is more than 1 System
> Number
> > for a group. Thus, when the module is complete, the above file set
> should
> > look like this:
> > SystemNumber   SystemNumberMAX    SystemName    SystemKey
> > 9007404             9007406               Windows Test 1        A
> > 9008683             9008684                          Windows Test 45
> A
> > 9017616                                        Windows Test 4595bg        A
> > 9019528                                        Windows Test 1        B
> >
> >
> > I'm totally stumped though! I've only done stuff like affecting
> forms and
> > switchboards in Access/VBA, and wrote a VB.NET tic-tac-toe game. I'm
>
> > thinking the steps are:
> >
> > 1. Read the file in, and split it by commas (meaning I'll
> ascii-delimit it
> > from Access first)
> > 2. Select the max SystemNumber # for the group and write it to a
> variable.
> > 3. Delete the records in the range between the minimum and maximum
> > SystemNumber for the group (including the record for the max
> SystemNumber)
> > 4. Write the max SystemNumber into the SystemNumberMAX field.
> >
> > Can anyone offer guidance or example code for something similar?
> >
> > Thanks! -Mike
>
>