Custom Autonumber based on lookup

[luce725]

I am re-creating an access database to include several years of data and would like to change the way the autonumbers work.

The sales year needs to be noted in the ID number but the sales year isn't the same as a calendar year.

For Example the 1st record for sales year 12 should have the id SY12-0001

Sales year 2012 is from 10/1/11 - 9/30/12



I have a table for Sales year dates (tbl_sales year dates) thinking I may be able to use it in a lookup function.

My main table to house each record (tbl_tracker) has the following fields:

ID
Sales Year
Start Date
Record ID

My inital thought is if there a way to code the Sales Year field to lookup the sales year based on the Start date and the sales year time frames in the tbl_sales year dates (similar to a vlookup in excel), then concatenate the Sales Year and ID in the Record ID field.

Can someone help? I am not very good with VBA (can edit fairly well but not proficient at writing it)

I appreciate any thoughts!

[rpeare]

So you have a bunch of existing records and you want to create an artificial numbering system so you have sequential items rather than using the autonumber key?

I assume the 'start date' field is the date of the sale?
recording the sales year is redundant and not necessary

so really what it comes down to is that you have a table like this:

tbl_tracker
ID (autonumber)
StartDate (date/time, date of the sale?)
RecordID (you want to populate this with your artificial numbering system)

tbl_Tracker
ID StartDate RecordID
1  1/2/2011  
2  4/1/2011
3  3/1/2011
4  1/15/2010

now the question is, how do you want your existing items numbered?
Should the first STARTDATE get the first number for the new year? IF so what happens when you have two ID's with the same startdate? should the one with the lower ID value get the first sequential number available?

For items like ID number 4, do you intend to start the numbering system new in each year?

[luce725]

This database is to track the progress of a document that is created. Each record is a tracking record of a physical document.

The answer to your first question is yes, I need an artificial numbering system for sequential items.

The "Start Date" is the date the document is requested and would ideally trigger what Sales year the document is requested in. The reason I need both the sales year and the ID number is because too many numbers confuse end users. For example SY11-158 and SY12-158 are easier to differentiate than 110811-158 and 120512-158.

To answer your second question, I would like the existing records to be in order of start date and then alpha sorted by customer name.

I would like the numbering to start over each sales year.

I'm not sure if any of this is possible, but I figure it never hurts to ask.

[rpeare]

it's possible for the history to label them in sequential order by customer name but not for new records unless you routinely go through and re-number things which is an extremely bad practice.

What I need to know is your table structure, I'm assuming you have a customer table with a primary key and another table where the foreign key is the customer ID and that table has the sales date in it?

if can outline the structure it'd be easier to give you code that will work in your situation.

[luce725]

Maybe it will help if I explain the database purpose a little better.

The only table I really need is the Tracker table. Eventually I will be adding more tables for customer retention based on the documents requested but i'm not sure yet what that will entail.

I receive a request to put together a document for a customer. I enter that in the tracker table via form. I manually enter the customer name, customer ID number, and other information about who the document is for. I then process the document request using feels such as information requested and received dates, approval dates, etc. I process roughly 1500 of these in a sales year and never twice for the same customer within a sales year.

I am still in the conceptual rebuilding stage and only have my current database that houses only one year of data to work from. It will be changing dramatically and I'm not sure of every field and table I will have.

[rpeare]

Do you have a table that lists all of your customers? It doesn't sound like you do if you are entering customer ID's and names by hand

What I'm talking about is this type of structure:

tblCustomers
CustID CustName  CustInternalID-----> other information related to customer
1      Customer A C-00001
2      Customer B C-00002

tblSales
SaleID CustID SaleDate -----> other sale related information
1      1      1/1/2011
2      1      1/1/2010
3      2      2/1/2011

if your tables are set up this way there's no need to type in the customer name or a customer internal number, it is just referenced by the primary key of the customer table.

That's what I'm talking about in your example you only list the fields

ID I don't know if this refers to the customer ID or it's a primary key (autonumber) for this table
SalesYear (which is a calculated value and doesn't need to be stored)
StartDate (which I still don't know if it's the date of the sale or not, and
RecordID which is the field you want to populate.

[luce725]

I understand what you mean but my tables are not set up that way because of the volume of customers and the rate at which they change.

ID right now is an autonumber field to give the document record a primary key.
Start Date is the date the document was requested. This database does not track sales of any kind.

Record ID is the field i would like to to display as SY11-158 as an example.

[rpeare]

Look you're just flat out not giving me enough information to work with. So here's an example you can adapt it to your field/table names.

If you do this to your historical data that's one thing. If you intend to apply this same numbering system to any new records that's another matter entirely and you can not do it easily if you continue to enter your data on tables as opposed to using forms.