Member Avatar for slymdjay.gunshot

Hi geeks!! im working on a project (in visual studio 2008) where i need to create a reminder which will prompt every month for showing the actual state of fees a number of customers have left to pay. the project uses access as database and will have a fixed amount to pay, but when the customer gives an amount less than the fixed one, the reminder will prompt every month to remind who is owing (from the database) and what amount is left to pay. thanks for your help, im working on it till i see someone answering... ;) !!

Edited by slymdjay.gunshot because: Project
  • 4 Contributors
  • 6 Replies
  • 595 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by phorce

Dani AI

Generated

A practical, low-friction pattern that builds on ’s Balance_Owing idea and ’s scheduling suggestion: normalize payments, compute outstanding on demand, and trigger a single monthly check so reminders aren’t repeated.

Schema: Customers(CustomerID, Name, FixedAmount) and Payments(PaymentID, CustomerID, PaymentDate, Amount). Avoid keeping a cached Balance_Owing unless performance requires it; computing Balance = FixedAmount - SUM(Payments.Amount) removes sync bugs. For a small Access file the engine can do the grouping; Access SQL uses Nz() for null-safe sums.

Example Access query + VB.NET reader to populate a ListView (run at reminder time or on app start):

' Imports System.Data.OleDb

Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\Fees.accdb;Persist Security Info=False;"
Dim sql As String =
  "SELECT c.CustomerID, c.Name, c.FixedAmount, Nz(Sum(p.Amount),0) AS TotalPaid, " &
  "c.FixedAmount - Nz(Sum(p.Amount),0) AS Balance " &
  "FROM Customers c LEFT JOIN Payments p ON c.CustomerID = p.CustomerID " &
  "GROUP BY c.CustomerID, c.Name, c.FixedAmount " &
  "HAVING c.FixedAmount - Nz(Sum(p.Amount),0) > 0;"

Using cn As New OleDbConnection(connStr)
  cn.Open()
  Using cmd As New OleDbCommand(sql, cn)
    Using rdr As OleDbDataReader = cmd.ExecuteReader()
      ListView1.Items.Clear()
      While rdr.Read()
        Dim item As New ListViewItem(rdr("CustomerID").ToString())
        item.SubItems.Add(rdr("Name").ToString())
        item.SubItems.Add(rdr("Balance").ToString())
        ListView1.Items.Add(item)
      End While
    End Using
  End Using
End Using

Scheduling / dedup: run this checker once per month. For a desktop app, have the app check a LastNotifiedMonth value in the DB and only show the popup if that month is older than current; update it after showing. For automatic runs use a small console app and Windows Task Scheduler (monthly) to generate the report or email. Troubleshooting notes: Access is file-based—keep connections short and use Using blocks; compile the app to x86 on 64-bit systems when using Jet/ACE drivers or install the appropriate ACE driver; if multi-user scale grows, consider moving to SQL Server for reliable concurrency.

Recommended Answers

All 6 Replies

Member Avatar for Stuugie

I'm not sure and can be completely off my rocker here (someone will correct me if I'm wrong) but wouldn't the easiest solution be to have a field in your database called Balance_Owing or something like that and then have an if statement that is checking each Balance_Owing value to see if it is greater than 0? If it is you can do whatever, send a message to whomever needs to see it or whatever else the requirements might be.

Just my 2 pennies! :)

Edited by Stuugie
Member Avatar for slymdjay.gunshot

Thanks for your reply but im just not sure about dat. BalanceOwing is a good thing but here it's about big database but i just dnt wanna use SQL cause im doing it just for a project. let say, i have 100 as the price everyone should pay, then i have 4 customers paying less than that(something 30, 75, 47, 69), and now when the end of the month comes the reminder will tell me that, the first is owing 100-30, the second 100-75 and so on.. Meaning the reminder go into the access tables(im using 2 in relationship), check by line the fees not equals to 100 then do the substraction and gives a message box or something else with the name and the fees in a list view. I need the "code" to be sure that you're right or not..

Member Avatar for jay nadeem

i have also need help for set a fee reminder to remind me about not paid or defaulter students from database. can you help me plzzzz

Edited by happygeek because: email deleted - keep all help requests within the forum
Member Avatar for Stuugie

Hi jay nadeem, you need to start your own discussion with your own problems and someone will help you.

, I realize that you want to use .NET for this but either way you're going to have to query the database and I'm pretty sure that SQL is more efficient ie quicker to process than .NET will be. I think you could even create a view that you can call with .NET and the view can already have the parameters set to find the balances outstanding. I have an exam that I am studying for at the moment but when I am finished tomorrow I am going to make a mock db and help you as best as I can. I am also going to defer this to the real gurus of this excellent site to get their opinions.

Member Avatar for jay nadeem

thanx, plz help me as soon as posible.

Member Avatar for phorce

I'm not an expert in VB.net and I am not entirely sure on the nature of this question.

If you had let's say someone paid today, then you can do a calculation to check to see if someone has paid a month after this particular day. It would require basic calculations, i'm not sure how to do it in VB though, sorry.

The other solution maybe to have the details stored on a server (using mysql) and then having a cron job that ran every month, these results could then be parsed into the VB program.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.