Creating Incremental Receipt Numbers

[JorgeCUC]

I have limited Access skills but have a broad understanding of database
queries and programming.

I need to add an incremental receipt number to a table.
All Gifts belonging to the same member get the same Invoice Number:
Currently the Invoice Number is blank but it should look like this:

MemID Gift Invoice Number
1 Fund A 2011-000001
2 Fund B 2011-000002
3 Fund B 2011-000003
3 Fund C 2011-000003
3 Fund B 2011-000003


7 Fund B 2011-000004
7 Fund B 2011-000004
8 Fund C 2011-000005
10 Fund B 2011-000006
15 Fund A 2011-000008

What is the best way to do this?

Thank you.

[RuralGuy]

See if this link gives you some ideas: http://www.baldyweb.com/CustomAutonumber.htm

[JorgeCUC]

Thanks, but that still doesn't solve the issue of assigning two different records the same Invoice Number.

[RuralGuy]

That sounds like a 1:m relationship that is best handled with a Form/SubForm arrangement.

[JorgeCUC]

Would this require another table?

[RuralGuy]

How many tables are we dealing with at the current time?

[JorgeCUC]

There's a Member Table, and a Donation table, so 2.

[RuralGuy]

The Member table would be bound to the MainForm and the Donations table would be bound to the SubForm. Using the LinkChild/MasterFields you can get Access to fill in the Invoice Number in the Donations table.

[JorgeCUC]

The problem with that is that a Member can have more than one Invoice Number.

[RuralGuy]

The relationship is between Invoices and Donations right?

[JorgeCUC]

Yes.
Member gets a receipt listing all donations made for one month with one Invoice Number.

[rpeare]

If you're using a bound form you're likely going to have to perform some sort of lookup (either dlookup or with a SQL statement, I prefer the SQL statements) and see if there as an existing invoice number for the combination of donor and fund. If there's more than 0 just apply the existing invoice number to the new record, otherwise create a new one.

[JorgeCUC]

Records are not given Invoice numbers until the end of the month.

At the end of the month, ideally, I'd like run a query/macro that will update the db checking for all records without an invoice number, and assigning them one (making sure multiple Donations are assigned the same Invoice number.

[rpeare]

Here's an example. You will obviously have to adapt it for you dataset. However, I want to point out a couple of things. Is there a reason you aren't, at the beginning of a month, creating an invoice for a customer when they order their first item, then just add items to that invoice as the month progresses? Doing it the way you're proposing is a little bit backward.

[JorgeCUC]

Thanks for your help. I'll try this out with my db.

I can't think of any reason we don't add an Invoice number right away.
It used to be done yearly, and now we're to do it monthly. This is a db I inherited, and am trying to fix, so adding that functionality might be an good option.

Thanks again.