Best method to add multiple records to table at once

[Doug]

Background:
I have an Access 2007 database used to manage a small summer camp. The
database has a table which tracks financial transactions. We don't want
campers to carry (and therefore lose) cash during the week, so we encourage
them to deposit snack-bar funds to a snack bar account. Right now, my
database does not handle individual snack-bar purchases (that's next year's
project)... but it does record all of their deposits.

Need:
We need a way to handle camp purchases and camp refunds of unused snack-bar
funds. I'd like a form - preferably a continuous form - which shows each
camper with a positive snack-bar balance. The way I see it - it would list
each camper's name along with the camper's balance. There would then be an
empty input box next to the balance that the snack-bar director would
populate at the end of camp for each camper's total purchases at the snack
bar. At the bottom of the form would be a button that would go through the
form and add a record into the transaction table for each camper's total
snack-bar purchases. From there, the database could easily compute the
amound needed to be refunded to the camper as unused funds.

Question:
What would be the best way to accomplish this task? I'm new at this, so I
may need a thorough explanation. Thanks for your help!

Doug

 

[Noëlla Gabriël]

Hi,

if you don't want to start registering the purchases of the campers, and
only enter once, at the end of the camp the total amount, just add a new
field to the campers table: total snack purchases. You can easily make a
continuous form showing the campers name and this field for the snack-bar
director to fill in.
That's all you need. Next thing to do is to make a totals query showing the
total amount deposited for each camper, and then make a second query based on
this totals query linked to the campers table and automatically calculate the
refunds by subtracting the amount purchased from the total deposits.

 

[Doug]

Hmm... that is a good option that I haven't considered. However... doing it
that way won't cause a line-item to go into the register with all other
charges/payments. But I suppose this is a step in the right direction. Any
other ideas and/or a method I could google? Thanks for the help - this forum
has been invaluable!

 

[Jeff Boyce]

Doug

A few other factors to consider ...

Who (and how many who's) will be using this application?

What level of experience do they have? (and what level of user-proofing
will you need to build in?)

Single-use/single-user PC or network?

By what process have you determined that a relational database is what you
need? Are you quite certain a spreadsheet or a commercial accounting (e.g.,
Quicken or Quickbooks) won't do the job?

How much time do you have to devote to this? (and your level of experience
building relational databases?)

If you haven't answered yourself these questions along with many others, you
don't (yet) have a clear picture of the requirements you're trying to build
to...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Doug]

Jeff,

This is a single-user, single-PC application (hence why I'm using Access).
They have used spreadsheets for years... but I'm trying to relieve some of
their headaches... such as manually trying to keep track of family camp
payments and church scholarships (it is a church camp)... Medications...
assigning cabins... keeping track of who is at camp and easily being able to
pull up parental information in case of emergencies, keeping track of the
snack-bar fund (deposits, purchases and reimbursements) as well as keeping
track of bank deposits. I also want to save time for the administrators who
enter and track camp registrations. There is 1 camp 'Dean' and 2 camp
Directors as staff running the administrative side - all pastors using their
own time for the camp. This is a non-profit camp with approximately 200 kids
total for 3 separate weeks - 1 week each for primary, middle-school and high
school students.

As for me... I am a stay-at-home dad who has a computer science degree from
a 4-year public university (though have been out of work for 4 years with
kids). I have basic understanding of relational databases (though never used
Access...) as well as a good understanding of programming (though almost all
of my work is with C++ and other object-oriented languages). I have 3 kids 4
years old and under and have been using the few spare hours of my day to
create this application to make life easier at the camp.

So yes... I have thought through many of the issues you have outlined. My
little app is 95% complete - this is the last major function I'm trying to
encorporate. I am just looking for an easy solution regarding how to create
a continuous form that one of the directors can use to easily enter each
camper's total snack-bar purchases for the week. I wanted an un-bound column
so that whoever is doing the entry can easily enter, check and verify before
'committing' the form (by pushing a button) which creats entries into my
transactions table. If somebody could easily start me off, I can go from
there... I just don't do this for a living (now) and don't have an easy
solution off the top of my head regarding how to do this.

PS - I want the application to grow and plan on continuing the project to
encorporate snack-bar purchases next year. When I get to this point, there
will probably be 2 users (probably 2 separate front-end applications to the
same back-end data - though because there is no network, it will not be used
by multiple users concurrently). When I am at this point, there will be no
need for the form in question.

 

[Jeff Boyce]

Doug

Thanks for the further description.

Have you laid out the entities and relationships using paper/pencil? That
seems to work well for me as a starting point.

The first paragraph below may contain all that, but doesn't present it in a
way that readily leads to an ER diagram.

Another idea, rather than an unbound control, might be to leave it as a
bound control, but allow the user to "fix" it, if necessary. This would cut
down on complexity and might give the user more of a sense of control.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Doug]

Thanks.

Yes - All relationships are defined. I coded the form so that the user
clicks a list-box to select a camper and then an unbound text box allows the
user to enter the amount to be reimbursed. From here - the program figures
how much the camper purchased (total deposits - reimbursed amount) - and
enters data into my transaction table. It doesn't work exactly like I had
invisioned, but it meets our needs for the time being.

I also created a procedure that closes all accounts without having to go
through them manually. This way - after camp is closed and all campers who
had money left in their account have been reimbursed, the camp director can
click a button and the program will add rows to my transaction table for all
remaining accounts with balances left in their accounts for camp purchases
that are equal to the sum of all of their deposits.

This process will work fine for us until we bring the snack-bar online next
season

 

[Jeff Boyce]

Best of luck on future "enhancements"!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[shuailang]

Best of luck on future "enhancements"!

Regards

Jeff Boyce
Microsoft Office/Access MVP