Database design conditional auto increment field

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
Assuming your eventno goes in column C, try placing the below mentioned formula in column C and drag down to the last row.

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

Hope that helps
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,066
Office Version
  1. 365
Platform
  1. Windows
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:
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 3, 2015
Messages
2,066
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,154
Messages
5,629,011
Members
416,358
Latest member
grsaltzman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top