mikedp

Hello,

I have been working on a charges/payment table as an expense sheet.

With a lot of help from you, I was able to create a grid table which included all individual transactions(balances/charges/payments) for each person, and the totals for all persons and their balances/charges/payments.

(But was unable to just show their subtotal on a gridview without showing every single transaction for each person)

I would like to have only the monthly report balance for each person on each line as opposed to all transactions for each person (because I don't need to know every transaction done, just the person's balance).

And, I am trying to figure out how to have the balances presented like a credit card, i.e. Total balance, 30 day, etc.for each individual as well as the entire population.

A grid view of each individual's balances, and a total of all at the bottom would be the goal:

Name Date of Charge Charge Date of Payment Payment SubBalance Indiv Balance 30 d 60 d

tom 5/5/07 100 5/8/07 50 50
tom 8/8/07 200 n/a 0 200 250 250 200

mike 3/3/06 100 3/7/07 100 0
mike 8/7/07 100 n/a 0 100 100 100 0


Total 500 150 350 150 200

Any help or direction would be extremely appreciated.Thank you, mike



Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

js06

Mike,

I know you haven't been helped on this one yet.

I was hoping someone would have gotten to you by now.

I was wondering what your plans are for the report.

Are you wanting to store the report, or are you just wanting to run the report to view it

I was thinking that maybe this would be easier to make this all work by exporting the data into a spreadsheet

then you can handle all the calculations fairly easily

It might be easier to store the reports the way you want them in a spreadsheet also

Maybe you can tell me what your plans are for it in more detail and i think i can help






Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

Steve2222

I assume that the data is stored in a table in a database. If so, setting up a view or query using a SQL statement something like this should give you the monthly sum you are after.

SELECT Name, SUM(SubBalance) AS IndivBalance FROM dbo.Charges GROUP BY Name

I haven't had a look at the past due balances yet but it can be done by adding more calculated fields to the SQL statement.





Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

mikedp

Hi J,

I work at a doctor's office, and am trying to figure out a better way to better keep track of charges, fees, overdue balances, etc. at both the individual level and overall level. There is software out there, but cost is in thousands. Thought there could be a better way by making my own, versus buying someone else's.

That's where my charges (e.g. sick visit, wart removal), payments (patient vs insurance), 30 day balances, and so on come in.

You are right...I just would need a printed report that would summarize balances so I could follow up on them. I don't need to save all the calculations...so a spreadsheet

I am not well versed in coding, so I am trying to start with a very simple concept, and work up to making it better and better. I am very interested in learning, and have done my best to read intro vb 2005 texts, online tutorials, and do a lot of hands on practice, but you all are heads and shoulders above what I can do...maybe I am in over my head and trying to do too much

Thanks for answering, Mike






Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

mikedp

Thanks Steve,

Yes, data is stored in a database. I will try your code today,

Mike




Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

Steve2222

I have spent a couple of hours trying to get the past due balances but it seems that SQL 2005 does not have an inbuild IIF function. I can do this in Access so if I explain the proceedure maybe someone else can supply the function.

You have a table with fields (columns) including SubBalance.

You need to add calculated fields to your table definition. Start by adding a new column called "daysoutstanding" and go down to the column properties. You will find a property called "Computed Column Specification (Formula)"

Enter the following expression in this property datediff(day,[datecharge],getdate())

If you save the definition and have a look at the table data you will see a new column with the number of days since the date of the charge.

The next bit does not work in MS SQL but does in Access.

In the table definition add another column called "over30" and add the following expression.

iif(30 < datediff(day,[datecharge],getdate()) < 60, [Charge],0)

If you get this to work, then you can build a view or query as I explained earlier.

Good Luck





Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

js06

Hi Mike,

When you are going to check the past due amounts, will this be on the total balance or on individual items

Say if one patient has 2 visits in a month, 1 at the beggining and 1 at the end

the first visit payment will be 30 days overdue but the last payment will not

What i was thinking as far as the spreadsheet was this

The very first program i made with vb express was a home budget program

I had a terrible time getting it to work with the datagridview

And as steve found out, the formula handling in computed columns is awkward

I tried an if function as an expression for a column in code but it won't accept it

and you cannot have one column that is computed by another another computed column (you can't overlap formulas)

then i found spreadsheet gear which solved all my problems because i was able to design everything visually

I created data entry and reports all in the same sheet, side by side

I can give you some code that will work a couple of ways

I have code that will filter your basic data to a gridview

and i have code that i helped Jon1s with that will filter your data to a speadsheet and add the formulas

His code works very well

I also can help you automate the spreadsheet that is a little more direct with the commands

(move here, place formula, move there, copy data, etc...)

The first program i made was for construction estimating in VBA with excel where i automated the spreadsheet with the formulas added by code and also updating themselves with the built in calculation features.

The spreadsheets do a lot more thinking in the background for you as i'm sure you know

I think in the end you can make all this work, it will just take some planning and playing

What you may be able to do is to start with the spreadsheet, doing it more visually and then converting to the datagridview. or work in conjunction with both.

You don't have to store the spreadsheet data, just use it to handle the calculations for you

Either way the code i have will get you started if you just want to filter to a datagridview

My next project i will be working on will be combining the data storage with sql and the calculations of the spreadsheet all in one.

I can't say what is the best way for you to do it, but you have many options depending on how you want to see everything.

Let me know what your thoughts are i can give you what i have






Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

mikedp

Thank you!




Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

mikedp

Hi J,

Thank you for your help.

To answer your question...ideally the program should act just like my credit card statement, i.e. statements will be based on individual items, not total balance due; that way you are still given a "grace period" (30 days) to pay on your transaction, not on your total balance.

Example: If balance is 30+ days, but a new transaction just occurred, then the most recent transaction will not be placed on the 30+ balance (but will show up in total balance due).

I think I can try and work with the spreadsheet...it may be easier for me to understand and work with.

But, whatever you think would be easier for me to work with and learn with as well.

Thank you, Mike





Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

js06

Mike,

I didn't forget about you

I am trying to get a little more information together on a few things

In the mean time

If you would go to your registration benefits portal then download & install spreadsheet gear

Once that is done then i can give you some code and you can see what you can do with both the spreadsheet and also the datagridview

You can play with them and see if you want to use just the spreadsheet, just the datagridview or both together

In order for you to get the download, you need to have registered your vb express

In your registration email from microsoft you will have a link to the benefits portal

Here is the link, but it will not let you enter unless you have registered

http://go.microsoft.com/fwlink/ LinkId=52054

Let me know when you have it installed






Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

mikedp

Thank you. I have installed it.

Mike




Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

js06

Ok, now you need to add the items to your toolbar

in your toolbox at the bottom you can right click and choose add tab

name it spreadsheet gear

then inside the tab - right click and click choose items

in the .net framework components tab - put a check next to formulabar and workbookview

then click ok

Now you can drag the formulabar and the workbookview to your form

I have mine setup where the datagridview is on the top of the form and the formula bar and workbookview are underneath to see them both work at the same time

i also have two comboboxes, and two buttons

1 combobox for the firstname and 1 for the lastname

1 button is to filter the datagridview and 1 is to populate the spreadsheet

i used the code i gave in the other thread to fill the comboboxes with the name items

and then i used the code for filtering in conjunction with the name selected in the combobox

Right now i only have it working for one the last name only, so it filters all the records with that name

this is fine unless you have multiple customers with the same last name

although you Maybe could use the customer id to run the filter

I am working on the statement to get the records from a particular individual only

ReneeC is helping on that one

I will post the code all together in another post in a few minutes






Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

js06

This first code is to populate your comboboxes

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.Table1TableAdapter.Fill(Me.Database1DataSet1.Table1)

For Each r As DataRow In Me.Database1DataSet1.Table1.Rows()

For Each firstnameitem As String In r("FirstName")

If Me.ComboBox1.Items.Contains(r("FirstName")) = False Then

Me.ComboBox1.Items.Add(r("FirstName"))

End If

For Each lastnameitem As String In r("LastName")

If Me.ComboBox2.Items.Contains(r("LastName")) = False Then

Me.ComboBox2.Items.Add(r("LastName"))

End If

Next lastnameitem

Next firstnameitem

Next r

End Sub

This is to filter the gridview with the data based on the selection in your combobox (firstname)

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim SQLtext As String = "SELECT FirstName, LastName, Charge, Payment, Balance FROM Table1 WHERE(FirstName = @par1)"

Dim conn As New SqlClient.SqlConnection(My.Settings.Database1ConnectionString)

Dim cmd As New SqlClient.SqlCommand(SQLtext, conn)

Dim Val1 As String = Me.ComboBox1.Text

cmd.Parameters.Add(New SqlClient.SqlParameter("@par1", SqlDbType.NChar)).Value = Val1

conn.Open()

Dim dataadaptor As New SqlClient.SqlDataAdapter(cmd)

'this line will clear the grid before filling, if you remove this it will add the filtered data below what is there already

That may come in handy if you plan on using the gridview to create a balance list report of all customers

Me.Database1DataSet1.Table1.Clear()

dataadaptor.Fill(Me.Database1DataSet1.Table1)

conn.Close()

End Sub

This will populate the workbookview and also add the formula for the balance (Charge - Payment)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim dr As SqlClient.SqlDataReader

Dim i As Integer = 1

Dim SQLtext As String = "SELECT FirstName, LastName, Charge, Payment, Balance FROM Table1 WHERE(FirstName = @par1)"

Dim myCon As New SqlClient.SqlConnection(My.Settings.Database1ConnectionString)

Dim myCom As New SqlClient.SqlCommand(SQLtext, myCon)

Dim val1 As String = Me.ComboBox1.Text

myCom.Parameters.Add(New SqlClient.SqlParameter("@par1", SqlDbType.NChar)).Value = val1

myCon.Open()

dr = myCom.ExecuteReader(CommandBehavior.Default)

Me.WorkbookView1.GetLock()

Do While dr.Read()

With Me.WorkbookView1.ActiveWorksheet

.Cells(i, 0).Value = dr("FirstName")

.Cells(i, 1).Value = dr("LastName")

.Cells(i, 2).Value = dr("Charge")

.Cells(i, 3).Value = dr("Payment")

'.Cells(i, 4).Value = dr("Balance")

'Add 1 to i to stop references getting thrown off

.Cells(i, 4).Formula = "=Sum(C" & i + 1 & "-D" & i + 1 & ")"

'.Cells(i, 6).Formula = "=Sum(B" & i + 1 & "*D" & i + 1 & ")"

'.Cells(i, 7).Formula = "=Sum(1-(E" & i + 1 & "/F" & i + 1 & "))"

i += 1

End With

Loop

myCon.Close()

Me.WorkbookView1.ReleaseLock()

End Sub

You can see what is happening with the formula in .cells(i,4).formula when you run it and click on cell E2

you will see the formula in the formula bar

Since you will be using dates, you can have formulas that will subtract the current date from the charge date and have it show you if it is 30, 60, 90 days past due

To make some of this easier, you can create a template spreadsheet either with excel or spreadsheet gear

(spreadsheet gear not the workbookview)

and then you can set it up visually and instead of coding the formulas you can type them in the cells

and add the column headers and whatever else you want

then you can open the workbook into the view and just populate the data with code

the formulas will be there already and will update automatically

This will give you an idea of how to lay it out

then you can code it all once you have it planned out

and then you can add the totals at the bottom in the correct places automatically

You can do the totals by automating the template sheet, but it is just a little more efficient to do it when you add it with the code above.

To open a workbook in the designer do the following

right click on the workbookview on your form

click workbook explorer

right click on workbook set and click open workbook

browse for your workbook and open it

you can remove the book1 by right clicking and choose close

I know i have given you a lot right now

play with it and see what you think

Let me know how you get along with it






Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

Steve2222

Have finally worked out the database query to extract the data from your table.

SELECT

Names,

SUM(SubBalance) AS SubBalance,

SUM(CASE WHEN (datediff(day, [DateCharge], getdate())) BETWEEN 30 AND 60 THEN [SubBalance]

ELSE 0 END) AS over30,

SUM(CASE WHEN (datediff(day, [DateCharge], getdate())) > 59 THEN [SubBalance] ELSE 0 END) AS over60

FROM Charges

GROUP BY Names

MSSQL now uses a CASE function instead of IIF.

If you run this query on your table you will end up with a table in the form you require for your report.

Name Balance over 30 over 60

Mike 100.0000 100.0000 0.0000
Tom 250.0000 0.0000 50.0000

It is fairly simple once you work out the query.

Stephen





Re: Visual Basic Express Edition Charges/Payments Grid With 30, 60 day past due balances

mikedp

J,

Thank you...I will try and digest it..

I am appreciative of your help and expertise,

Mike