A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to create a field that increments based on criteria



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2009, 11:00 AM posted to microsoft.public.access.tablesdbdesign
Doug
external usenet poster
 
Posts: 616
Default How to create a field that increments based on criteria

I hope the subject didn't throw anybody off...

I'm working on a database which is used for a local summer camp. The camp
has a little snack bar which is run completely manual. Each camper is
assigned a unique number (for that week's camp) which the snack bar operator
can then use to access the camper's account (all done on paper).

The database is used to register campers, track finance and assign camp
cabins. We'd like to also assign the snack bar number automatically... but
we want the number to reset for each camp (ie - for each camp keep a running
number). The reason we don't want it to continue to run is so the kids can
more easily remember a low number (150 max). To make things more confusing -
we are using the database to register kids to multiple camps at a time. Is
there a way to do this - or do we need to continue manually assigning the
numbers?
  #2  
Old May 14th, 2009, 01:35 PM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default How to create a field that increments based on criteria

On Thu, 14 May 2009 03:00:01 -0700, Doug
wrote:

You can use the often-discussed "DMax+1" solution. That way you can
reset the value to an arbitrary low number for each new camp. Of
course your table will have a CampID in addition to a CamperNumber,
and a unique index (or PK) over the combination of both fields.

Type "Access DMax increment counter" in your favorite search engine
and you should not have problems locating help.

-Tom.
Microsoft Access MVP


I hope the subject didn't throw anybody off...

I'm working on a database which is used for a local summer camp. The camp
has a little snack bar which is run completely manual. Each camper is
assigned a unique number (for that week's camp) which the snack bar operator
can then use to access the camper's account (all done on paper).

The database is used to register campers, track finance and assign camp
cabins. We'd like to also assign the snack bar number automatically... but
we want the number to reset for each camp (ie - for each camp keep a running
number). The reason we don't want it to continue to run is so the kids can
more easily remember a low number (150 max). To make things more confusing -
we are using the database to register kids to multiple camps at a time. Is
there a way to do this - or do we need to continue manually assigning the
numbers?

  #3  
Old May 14th, 2009, 04:34 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How to create a field that increments based on criteria

Doug

If someone asked me (or just about any kid) how to "break" your system, the
simplest approach is to use "Johnny's" number instead of my own. I do hope
you have a way to confirm who you're dealing with...

Since you're looking to automate this, why limit yourself to a sequential ID
#? You could just as easily list the campers by name, and select the
camper's name (along with other 'identifying features').

You've asked a "how do I" question (how do I come up with a re-setting
incrementing ID numbers). But you've also provided a decent description of
the business need (uniquely identify each camper during each camp session).
You might just find some folks here offering alternate approaches to solving
that business need!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Doug" wrote in message
...
I hope the subject didn't throw anybody off...

I'm working on a database which is used for a local summer camp. The camp
has a little snack bar which is run completely manual. Each camper is
assigned a unique number (for that week's camp) which the snack bar
operator
can then use to access the camper's account (all done on paper).

The database is used to register campers, track finance and assign camp
cabins. We'd like to also assign the snack bar number automatically...
but
we want the number to reset for each camp (ie - for each camp keep a
running
number). The reason we don't want it to continue to run is so the kids
can
more easily remember a low number (150 max). To make things more
confusing -
we are using the database to register kids to multiple camps at a time.
Is
there a way to do this - or do we need to continue manually assigning the
numbers?



  #4  
Old May 14th, 2009, 08:34 PM posted to microsoft.public.access.tablesdbdesign
Doug
external usenet poster
 
Posts: 616
Default How to create a field that increments based on criteria

I enjoyed your post, Jeff. I'd love to hear other possible creative
approaches. I'd love to automate the snack bar - but I think that's next
year's project.

As for why we do it this way... this is the way it's been done for years -
possibly since the camp opened! Campers who want a snack bar 'account'
deposit money (so they don't use it) and are assigned a number. The number
is written on their name card that they keep with them all week. The camper
shows the name card to the snack bar attendant and the snack bar attendant
then pulls out the camper's index card that is stored in a box numerically.
The card has printed boxes - each represents $.25. The snack bar attendant
then crosses off blocks as the camper purchases snacks. That's how it works
- I'm definately not saying it's the best system - but it is what they use!

"Jeff Boyce" wrote:

Doug

If someone asked me (or just about any kid) how to "break" your system, the
simplest approach is to use "Johnny's" number instead of my own. I do hope
you have a way to confirm who you're dealing with...

Since you're looking to automate this, why limit yourself to a sequential ID
#? You could just as easily list the campers by name, and select the
camper's name (along with other 'identifying features').

You've asked a "how do I" question (how do I come up with a re-setting
incrementing ID numbers). But you've also provided a decent description of
the business need (uniquely identify each camper during each camp session).
You might just find some folks here offering alternate approaches to solving
that business need!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Doug" wrote in message
...
I hope the subject didn't throw anybody off...

I'm working on a database which is used for a local summer camp. The camp
has a little snack bar which is run completely manual. Each camper is
assigned a unique number (for that week's camp) which the snack bar
operator
can then use to access the camper's account (all done on paper).

The database is used to register campers, track finance and assign camp
cabins. We'd like to also assign the snack bar number automatically...
but
we want the number to reset for each camp (ie - for each camp keep a
running
number). The reason we don't want it to continue to run is so the kids
can
more easily remember a low number (150 max). To make things more
confusing -
we are using the database to register kids to multiple camps at a time.
Is
there a way to do this - or do we need to continue manually assigning the
numbers?




  #5  
Old May 15th, 2009, 12:01 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How to create a field that increments based on criteria

I guess I misunderstood. I thought you posted a request for assistance
getting a way to record the snack bar purchases against camper accounts. We
may have differing definitions of "automate".

What is it that you'd like Access to be able to do to help your camp store?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Doug" wrote in message
...
I enjoyed your post, Jeff. I'd love to hear other possible creative
approaches. I'd love to automate the snack bar - but I think that's next
year's project.

As for why we do it this way... this is the way it's been done for years -
possibly since the camp opened! Campers who want a snack bar 'account'
deposit money (so they don't use it) and are assigned a number. The
number
is written on their name card that they keep with them all week. The
camper
shows the name card to the snack bar attendant and the snack bar attendant
then pulls out the camper's index card that is stored in a box
numerically.
The card has printed boxes - each represents $.25. The snack bar
attendant
then crosses off blocks as the camper purchases snacks. That's how it
works
- I'm definately not saying it's the best system - but it is what they
use!

"Jeff Boyce" wrote:

Doug

If someone asked me (or just about any kid) how to "break" your system,
the
simplest approach is to use "Johnny's" number instead of my own. I do
hope
you have a way to confirm who you're dealing with...

Since you're looking to automate this, why limit yourself to a sequential
ID
#? You could just as easily list the campers by name, and select the
camper's name (along with other 'identifying features').

You've asked a "how do I" question (how do I come up with a re-setting
incrementing ID numbers). But you've also provided a decent description
of
the business need (uniquely identify each camper during each camp
session).
You might just find some folks here offering alternate approaches to
solving
that business need!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Doug" wrote in message
...
I hope the subject didn't throw anybody off...

I'm working on a database which is used for a local summer camp. The
camp
has a little snack bar which is run completely manual. Each camper is
assigned a unique number (for that week's camp) which the snack bar
operator
can then use to access the camper's account (all done on paper).

The database is used to register campers, track finance and assign camp
cabins. We'd like to also assign the snack bar number automatically...
but
we want the number to reset for each camp (ie - for each camp keep a
running
number). The reason we don't want it to continue to run is so the kids
can
more easily remember a low number (150 max). To make things more
confusing -
we are using the database to register kids to multiple camps at a time.
Is
there a way to do this - or do we need to continue manually assigning
the
numbers?






  #6  
Old May 15th, 2009, 12:39 AM posted to microsoft.public.access.tablesdbdesign
Doug
external usenet poster
 
Posts: 616
Default How to create a field that increments based on criteria

Sorry for the miscommunication... Tom gave me exactly what I needed...
which was a number generator that reset for each camp. nz in conjunction
with dmax + 1 does the trick perfect for my needs which is to automate the
process of assigning numbers resetting back to 0 for each camp. I appreciate
your input. Thanks!

"Jeff Boyce" wrote:

I guess I misunderstood. I thought you posted a request for assistance
getting a way to record the snack bar purchases against camper accounts. We
may have differing definitions of "automate".

What is it that you'd like Access to be able to do to help your camp store?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Doug" wrote in message
...
I enjoyed your post, Jeff. I'd love to hear other possible creative
approaches. I'd love to automate the snack bar - but I think that's next
year's project.

As for why we do it this way... this is the way it's been done for years -
possibly since the camp opened! Campers who want a snack bar 'account'
deposit money (so they don't use it) and are assigned a number. The
number
is written on their name card that they keep with them all week. The
camper
shows the name card to the snack bar attendant and the snack bar attendant
then pulls out the camper's index card that is stored in a box
numerically.
The card has printed boxes - each represents $.25. The snack bar
attendant
then crosses off blocks as the camper purchases snacks. That's how it
works
- I'm definately not saying it's the best system - but it is what they
use!

"Jeff Boyce" wrote:

Doug

If someone asked me (or just about any kid) how to "break" your system,
the
simplest approach is to use "Johnny's" number instead of my own. I do
hope
you have a way to confirm who you're dealing with...

Since you're looking to automate this, why limit yourself to a sequential
ID
#? You could just as easily list the campers by name, and select the
camper's name (along with other 'identifying features').

You've asked a "how do I" question (how do I come up with a re-setting
incrementing ID numbers). But you've also provided a decent description
of
the business need (uniquely identify each camper during each camp
session).
You might just find some folks here offering alternate approaches to
solving
that business need!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Doug" wrote in message
...
I hope the subject didn't throw anybody off...

I'm working on a database which is used for a local summer camp. The
camp
has a little snack bar which is run completely manual. Each camper is
assigned a unique number (for that week's camp) which the snack bar
operator
can then use to access the camper's account (all done on paper).

The database is used to register campers, track finance and assign camp
cabins. We'd like to also assign the snack bar number automatically...
but
we want the number to reset for each camp (ie - for each camp keep a
running
number). The reason we don't want it to continue to run is so the kids
can
more easily remember a low number (150 max). To make things more
confusing -
we are using the database to register kids to multiple camps at a time.
Is
there a way to do this - or do we need to continue manually assigning
the
numbers?






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.