Database design conditional auto increment field

[jimrward]

I have a lot of historical data both in excel and an external 3rd party product which I can export as csv the excel data is a prev load of the csv
i am exploring putting it into access, the initial load will be a bulk load from the above sources thereafter will either be by csv import or by forms
i am trying to create an auto increment value in a field set at load point that is conditional on other fields as I do not want to use auto number and there is no date or time stamp available from the source data but it exported in chronological order
in its simplest form the table will be custid, year, eventno
I would like the eventno to increment per custid and by year with eventno generated automatically
custid, year, eventno
1, 2016, 001
1, 2016, 002
2, 2015, 001
1, 2016, 003

is this possible otherwise I will just have to prepare the data before hand in excel

 

[fadee2]

Assuming your eventno goes in column C, try placing the below mentioned formula in column C and drag down to the last row.

=TEXT(COUNTIFS($B$2:B2,B2,$A$2:A2,A2),"00###")

Hope that helps

 

[jimrward]

Thanks I am aware I can do it in excel by a number of ways I am looking for a method in access in table design

 

[Micron]

I don't see why you changed from 002 to 001. It could be because you went from 1 to 2, or it could be because you went from 2016 to 2015. I read "increment per custid and by year" but I think that's subject to interpretation. Any solution I've seen for this sort of thing involves vba. The problem seems to be that you'd need to nest a SELECT query (one that gets the DMax for your custID field) into the main query. However, while you can do this when the outer (main) query is another SELECT, I do not think you can do it with an UPDATE.

Perhaps something like this would get you started:

Automatically Increment a Fields Value | Database Solutions for Microsoft Access | databasedev.co.uk

Database Solutions for Microsoft Access - Automatically Increment a Fields Value in a database: Microsoft Access contains the AutoNumber data type that will automatically increment a value by 1 when set to New Values: Increment in the properties of the field. This may not always be what the...

www.databasedev.co.uk

 

[jimrward]

It changed from 002 to 001 as it was a change of custid and year
I have decided to use a single auto number field for all data so as to keep the data in sequential order and when I pull a subset I can always order by that to maintain order for a customer I will just have to sort by year for historical data before initial load.
currenty I have the data per year on separate worksheets in excel

 

[JonXL]

I have decided to use a single auto number field for all data so as to keep the data in sequential order and when I pull a subset I can always order by that to maintain order for a customer I will just have to sort by year for historical data before initial load.

If you're only adding this field for the purposes of sorting, I can't say I see real value to that.

Because this field can be predictably derived from other fields in your data, you should not really be storing it separately.

Instead, you should be calculating this on the fly or - even better in my opinion - just use the real criteria in your queries for sorting (year and custid).

That would solve your problem out of existence and also help avoid future issues you may not be expecting yet.

What happens if, for example, you have a 2015 and 2017 record for a customer and number them 001 and 002 but later find data for that customer for 2016? Do you renumber? Leave the records out of their 'natural' order?

 

[jimrward]

I am adding this field to maintain sequence order as apart from year there is no date and time stamp
the initial load will be in sequence order and there will be no back fill of missing data as it’s all present and correct only moving forward in time would I need to increment the counter per customer per year
so for all customers for a new year the eventno starts again at 001

 

[Micron]

I have decided to use a single auto number field for all data so as to keep the data in sequential order

As long as you don't mean contiguous and just need it to provide a sort order, perhaps OK. Autonumber fields are not guaranteed to be contiguous, nor should they be used for meaningful data. Perhaps review these just in case...
- UtterAccess.com
- General: Use Autonumbers properly

 

[xenou]

Although I have always read that autoincrement Ids cannot be relied upon, I have never seen any case where they do not always start at 1 and always increase (assuming you haven't reseeded the numbers which is not easy to to by accident). That said, the documentation on autonumbers in Access is clear and as micron said, they are not guaranteed to do anything except be unique.

I think you could add datetime timestamp to your data as a way to track sequence/order in terms of when data is added. Not ideal but could be serviceable. Or you can roll your own IDs based on some kind of max() logic at the time of insertion. Or indeed you could consider some other database that can do the job more reliably (vis-a-vis autoincrement values - such as pretty much almost any other database )

 

[jimrward]

I think I might have to prep the current data for previous years in excel by adding my key using countifs or sumproduct to count by customer and year
each year I will probably have to repeat the process with the current years data which would reset the counters anyway so I wouldn’t have to do the previous years as there will be no additions or subtractions the data would be static going forwards and backwards