Help a newbie keep track of lunch money

[ericturnerpd]

We have a very small private school, no money to purchase pos software. I need to keep up with kids in different grades or classes and their lunch money. Parents send checks weekly and monthly. Our problem is keeping a running balance + or - any info on how we may build this database would be great!!!

[Xipooo]

It depends on how involved you want the database to be.

For this it almost sounds like all you need are two tables. 1 for holding information about the children such as who the parents are, phone number, address, grade, etc. Then another table that just records transactions. A typical design is to have two fields, one debit and one credit. When a check is received you add it to the credit field. Each purchase made adds to the debit field.

If you want to calculate how much money is currently available, you use a query to subtract the total debits for the child from the credits for the child.

tblChildren

• ID
• Name
• MotherName
• FatherName
• Grade
• PhoneNumber
• AddressLine1
• AddressLine2
• City
• State
• Zip

tblTransactions

• ID
• ChildID
• Debit
• Credit
• CheckNumber
• Timestamp

You could potentially be much more normalized than this and include more tables to hold other data, but with such a simple database I don't see any need to go much further than this.

[ericturnerpd]

So can this be set up where the operator or administrator can select the child from a drop down list or type their name in like a form to select the child then decide whether they are paying or charging their lunch? If so can it be made to automatically deduct, credit or pay for the lunch. Also will you be able to run reports for students in ranges:

Negitive balance
Positive balance
By grade
Daily deposit (cash received)
Daily, weekly, monthly or yearly reports

Sorry if iI sound ignorant about access, I am! I have used it for very simple databases before and trust the database.

[June7]

As Xipooo said, as complicated or as simple as you want. The more functionality and the more user-friendly, the more code. All can be done with the proper data structure and coding.

Ideally, there is no 'deduction'. The student's balance (difference between payments and charges) would be calculated when needed.

[Xipooo]

The thing you need to understand is the separation of data from calculations. Tables are meant to maintain data but not running totals. Instead you use queries to extract data from your tables and run calculations on it.

If you want to know the total amount a child has on their account you take the total amount they have paid and subtract how much they've bought. That's why there are two columns, a credit and a debit.

A query like this would be run against your tables.

SELECT [Name], sum([Credit])-sum([Debit]) as Balance FROM tblTransactions INNER JOIN tblChildren ON tblTransaction.ChildID = tblChildren.ID GROUP BY Name

This would result in a list of all the children and their balances. I don't expect you do understand how this query works I just want to illustrate that we write a query to get the data you need and in the format you need it. I will point out the part sum([Credit])-sum([Debit]) which takes the sum of Credits and subtracts the sum of Debits to give you a value we called "Balance".

You can use the Query Builder to design a query like this one to calculate the information.

[June7]

Superb description but couple things to nitpick - would not group by name, use ID for grouping.

And Name is a reserved word, should avoid reserved words in naming convention. Name parts should be in separate fields. Also avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

[ericturnerpd]

Thank you Xipooo,

I am in the process of purchasing a new office suite 365, Im not a developer by any means or stretch of the imagination... My wife works as the lunch supervisor every day to help the school out, the school is having a hard time financially so we are attempting to make this better and keep up with things as cost effectively as we can. She is a full time nurse and I am a Police Officer. Im going to attempt to do this, crossing my fingers....lol I guess Access for dummies or something of the sort would be a place to start looking into queries? If i am un-successful in my endeavor are there people who could build this for me at a reasonable cost?

[June7]

Reasonable is subject to perception. Anywhere from $50 to $100 an hour unless you find someone to build for a flat fee. Might find a college student to take it on as class project. Problem is, free-lancers often disappear midway through development.

Then you have something you don't understand and the 'pro' may never be heard from again. Maybe better off taking a class and working through tutorials yourself.

[ericturnerpd]

Thanks June7, I live in a rural area, its funny but about the only thing people around here use a computer for is facebook and candy crush lol.... I may search for some online classes or tutorials..... if you know of a good place to start please let me know. Spare time is slim to none because of work.

[June7]

Depends how much you want to spend. There is a lot of free stuff on-line. I've never used paid on-line tutorials. I search for a particular issue.

Search through this forum for a start. There are some beginner tutorials in the Tutorials subforum.

Here is one with some references.
https://www.accessforums.net/program...ook-36304.html

A starting point for programming
http://office.microsoft.com/en-us/ac...010341717.aspx

[ericturnerpd]

Thanks June7!!! We shall see....... You guys may get tired of me asking dumb questions!

[Xipooo]

Superb description but couple things to nitpick - would not group by name, use ID for grouping.

And Name is a reserved word, should avoid reserved words in naming convention. Name parts should be in separate fields. Also avoid spaces and special characters/punctuation (underscore is exception) in naming convention.

Yer right about the Name part... Probably shouldn't use that as a field name... derp on me. However, gotta put name in the group by since it's part of the SELECT.


Eric, you can hire a professional consultant to put something together for you. Like June7 said, they're likely to pay anywhere from $50 to $100 per hour. My company charges in the $100-125 range but we do websites, and desktop apps as well as Access. I suspect most of the pro's here do consultation work. I would not suggest Free-lance or Craigslist.... or a friend of a friend who knows something. Almost half of the jobs we've picked up were because someone was hired to build a database and didn't finish it properly.

This really isn't a big database so I don't think a professional would take much to build it for you, but that also means it's a great "primer" for your learning. It's simple enough to learn the basics with. If you REALLY want to learn, I'd say just do it yourself. Nothing teaches better than hands on experience.

[ericturnerpd]

Xipooo, I do want to learn. I just don't want to have to become a programmer to do this one project......

[June7]

Ooops, yeah forgot about all non-aggregating fields need to be in group by clause. Need the ID just in case of multiple students with same name

On the other hand, build a report using Grouping & Sorting features with aggregate calcs in ID group footer section - something else for you to learn about, eric. Access has pretty good intrinsic reporting functionality.

A database can be built without any code (macro or VBA). I have one like that. But this means relying on intrinsic Access functionality for filtering and searching and opening objects directly from the navigation pane.

The more complex and user-friendly, the more code.

Need a basic understanding of database principles, Access functionality, programming concepts.

[Xipooo]

There are a ton of tutorials online for this. You can Youtube them or sign up for a site like Pluralsight. There are some here in the forum too.