Access Payment Database

[AntMac]

I'm looking for some general ideas about how to set up this database. I want to switch to using Access where I can control the reports a whole lot better than my last program. My main question is what's the best way to setup my tables in order to accomplish the tasks below.

The situation is this will be a payment tracking database for multiple people over multiple weeks. I would like an update form that allows me to see and update the main table with the payments for the people on the current week. And based on this I would like to be able to make reports that can show me how much they are behind or ahead as of the current week. And a report that can show what they have paid in total and how close they are to the total amount they have to pay. My first pass I made one table with all the people and a field for each week. That idea worked great for the report to show what they have paid so far and for the year against the corresponding amounts. But now to be able to have a form that brings me back all the people and one column to put in what they paid today and to know that today is the week 3 column for example is where I am stuck.I can't think of an easy way to do this the way the table is structured. And I would like to be able to have multiple people on the same page of the form if possible, if not I can live without that whistle.

 

[alansidman]

I would start with first understanding the concepts of a RDBMS. Here is a good place to start

http://sbuweb.tcu.edu/bjones/20263/access/ac101_fundamentalsdb_design.pdf

Then here is a web site with many schema to look at that may help in setting yours up

http://www.databaseanswers.org/data_models/

 

[xenou]

A field for each week would be more of a spreadsheet design. In a database, you will have one field called Week (Or possibly even just the transdate since all date-related data can be derived from a simple date).

 

[AntMac]

I know the basics of a RDBMS. That's why I wanted to use Access as my alternative. I know I can make an inefficient model, but I was hoping for tips on making an efficient one.

I would start with first understanding the concepts of a RDBMS. Here is a good place to start

http://sbuweb.tcu.edu/bjones/20263/access/ac101_fundamentalsdb_design.pdf

Then here is a web site with many schema to look at that may help in setting yours up

http://www.databaseanswers.org/data_models/

 

[AntMac]

I started thinking about something like this after I had the original setup of course lol The only thing I don't know about this setup in Access is how to get it to either update an existing row in a table or add a row based on this setup. So, I'm thinking a setup like this would be one table with all the people. Then a form that pulls in the people and let's me enter what they owed for the week and what they paid that week. Then update a different table that has all the payments and amounts owed. I can see this working as the efficient method. Now just to google and do more learning on updating a table based on a form. Do you know if it's easy to do this for more than one person at a time. Like I can have the form pull up 10 people so I can enter in multiples of 10 vs one at a time? Thanks though already for the vision.

A field for each week would be more of a spreadsheet design. In a database, you will have one field called Week (Or possibly even just the transdate since all date-related data can be derived from a simple date).

 

[xenou]

So, I'm thinking a setup like this would be one table with all the people.

How many people (i.e. if 10 people or several hundred it makes a difference in what you can get away with)

Then a form that pulls in the people and let's me enter what they owed for the week and what they paid that week

Do you plan on making entries only once a week or as payments are received (daily)?

In general, this can be very simple or very complex (handling payments can range from your local backyard garage where you fix a few cars a month to amazon.com billing and payment processes). It somewhat depends on how bulletproof (or dummyproof) you need it to be. Also how you want to recover from errors (which can be as simple as deleting records, which would never happen in a real business-ready payment system, or as complicated as posting correcting entries with appropriate audit trails)

 

[AntMac]

Some of the flexibility I'm willing to waiver on. I have the general setup you provided now with one table with a list of people (bowlers in my case) and a second table designed to get all their payments. I have an append query for new entries. It gives me all the bowlers with the default weekly fee and a spot to enter what they paid for that week. For corrections as you mentioned, I'm thinking an update append query that can pull up a week in question and let me double check and update any records as needed. I was thinking of grouping by team, but I can have a report confirm the totals by team to make sure I didn't enter something wrong. I will go through last year's data as a double check, but this seems like it will handle what I'm looking for. The only thing that I'm thinking might be difficult from this setup is a running weekly history balance type report. But I'll see how easy/hard that is with the data like this. Thanks again for your help. I'll have more tomorrow or late tonight.

How many people (i.e. if 10 people or several hundred it makes a difference in what you can get away with)


Do you plan on making entries only once a week or as payments are received (daily)?

In general, this can be very simple or very complex (handling payments can range from your local backyard garage where you fix a few cars a month to amazon.com billing and payment processes). It somewhat depends on how bulletproof (or dummyproof) you need it to be. Also how you want to recover from errors (which can be as simple as deleting records, which would never happen in a real business-ready payment system, or as complicated as posting correcting entries with appropriate audit trails)