Incrementing a field

[Wayne311]

I am trying to create and incremeted field based on a stored number. The query is returning the max value, but I need the max value + 1. Whats wrong here:

SELECT CaseNumber.CNumber, CaseNumber.CRNumber, CaseNumber.DateYear
FROM CaseNumber
WHERE (((CaseNumber.CNumber)=DMax("[CNumber]","CaseNumber","CNumber + 1")));

Thanks
Wayne

[jzwp11]

A SELECT query will only display existing records. If you want to create a new record that has a field where you want its value to be 1 greater than an existing record you will need an append query.

May I ask where you are trying to do this? Via a form perhaps?

[Wayne311]

This is only a part of a process that will be in a form to create a field value comprised of a upper case letter and a partial year (YY) and the incremented number. IE C11-123.

Wayne

[jzwp11]

To increment the number in the field you could use the following code in the Before Insert event of the form.

me.formcontrolname = DMax("myautoincrement", "tblMyTable") + 1

How is the upper case letter determined?

Technically speaking since your ultimate value is a calculated value: uppercase letter & year(date) & myautoincrement, you would only store the incrementing value in the field not the entire value such as C11-123 since calculated values are only rarely stored in the table.

The "me." above is a shorthand notation referring to the form from which the code is execute. You could also use the full form reference:

forms!formname!formcontrolname

[Wayne311]

Thanks for the suggestions.

The letter will always be an upper case "C". So far I am using a one record table with 3 fields... the incremented number, the complete number (C11-123) text, and the partial date (2011) number fields.

This will get more complicated as I learn forms. The form I am using has a subform where this incremented text number (C11-123) will be inserted into a junction table related to the previously described one record table.

[jzwp11]

So, it sounds like you are using the value as a primary key value in the first table and a foreign key in the related table. Although that is possible, I have heard that the primary key should not have any significance to the user which I believe your value does. I would probably use an autonumber field as the primary key and use that to link to the related table. You can always display your value as needed in any form or subform. The other thing to note is that relational database are a little more efficient with numeric fields rather than text fields.

[Wayne311]

The attached file has the tables and the relationships.

Wayne

[jzwp11]

I've attached an example of the relationships using autonumber primary key fields in each table and how they join to corresponding foreign keys in related tables.

Just as another observation, it is best not to use spaces or special characters in your table and field names. Having them forces you to enclose each name in square brackets in queries which will become tedious when you have many queries. The square brackets would also have to be used in any code or domain aggregate functions.

[Wayne311]

It looks like you are suggesting that table CaseNumber use auto increment for the case number...that will not work. And in table Districts, I would think that field District needs to be linked to the junction table with the same field. It is used to link to charges for each service. Just my first impressions....

Thanks for the input.
Wayne

[jzwp11]

The pkCaseNumberID field is an autonumber field where Access assigns the number, it would have no bearing on the field you want to increment. You would still use the DMax() function I mentioned previously to increment the value in that field (I was not sure whether it was CNumber or CRNumber in your structure). You would not store the actual constructed case number, only the components from which it is constructed (i.e. the incrementing field and the date field)

I've attached an example database illustrating how the data would appear in the tables as well as a form illustrating how the case number is constructed

When adding a new record via the form, you must enter the date to construct the case number

[Wayne311]

So other than the AutoNumber field I did change the relationships...I think this is what you were suggesting. See attached.

Wayne

[Wayne311]

I need a number field to incriment +1 (don't I?) and a text field to build the whole case number.

Wayne

[Wayne311]

Thanks for the DB samples. I appreciate your time. Unfortunately the case number is not always created. The clerk takes in money for a service. If new, a case number is generated. If it is a payment on an existing case, the old case number is used. If one payment is made for several cases some or all will be generated.

Also with your table relationships, I cannot have my one record table. Somewhere all case numbers must be stored as a lookup table for other functions (in a string format). It will have to be the CaseNumber table now...which is OK.

I like the simplicity of your idea...
Thanks again.
Wayne

[jzwp11]

You can still do that with the structure I presented. You can still add new detail records that relate back to old cases. The basic case information will be stored in the case number table. You can use a query to build the string field you need and display that in a combo or list box in a form.

[jzwp11]

The clerk takes in money for a service. If new, a case number is generated. If it is a payment on an existing case, the old case number is used

Are the old case numbers you refer to above in the same format as your example (C11-123)?

Do you need to reset the sequential number at 1 at the start of each new year?