Making a column in existing table into an autocounter

[bps]

Hello, I started developing an Access database for my company and using the current database on excel as a basis for it.

One of the main columns in our excel sheet is the catalog number for our products. However, with the new program we also want to auto-generate catalog numbers, and add it to list of existing products. The existing numbers are none sequential, so I was thinking of making the new cat numbers start from 100000.



I have tried to create another table starting from 100000 and using UNION all to join the tables, but was wondering if there was a more efficient method to do this.

Kind Regards!

[Micron]

You probably should post a picture of your relationships window if you have created relationships. The fact that you mention Excel and refer to Union queries as a means of joining tables makes me think you have gotten off on the wrong path when it comes to design. You should also state how important it is to have sequential numbers, and maybe more about the business case. I'm also wondering why you chose such a number design as 100589 as a product number since that won't tell you anything about it at all. Maybe that's OK for you.

[bps]

Business case: company has several excel sheets in the production line, one for each group. This leads to mis-communication between each group about time-frames and deadlines, so its a complete mess right now. They want to merge all the company's processes into one program that the whole company can see what is happening.
I never have used Access before, and was tasked to start the process of creating this database (CEO is cheap...)

It isnt necessary that the numbers to be sequential. But I am using the catalog numbers as a primary key to link it with the rest of the information in the database, as on the relationships image below.

[Micron]

Looks better than I had imagined. Usually, when someone posts certain keywords the immediate impression is that the db has been designed with a spreadsheet mentality. Not knowing the terms in the tables, I think that the approach ought to be tbl_Purification.Catalog Number linked to Main_Proteins, then to cloning as you have it. I don't see Catalog Number being needed in tbl_Purification if you do this; in fact having it in both tables might prove to be promlematic. By not using the record id's (e.g. MainProteins.ID on ProteinDatabase.MainProteins_FK) you could be faced with an update dilemna. Should you ever need to change a catalog number, you will have to cascade update that number throughout the entire db. You may have addressed this via the relationships but I don't see it (more on what I see below after the quoted text).

Aside from that, having special characters (save the underscore) in names is frowned upon (you have at least 5 types), as is having spaces in names. Units (mg) are unnecessary as well. The form label should be the identifier for units if needed. Having said that, you might want to consider tblUnits from which users specify when entering data. That can be added later if it becomes more attractive than it probably is now.

I am using the catalog numbers as a primary key

If by that you mean Parent_Catalog_Number, then on my laptop it looks like the joins are going the wrong way, but I'm not sure. It does not look like they are PK's anywhere else, so that's my guess. Your Access version is ahead of mine, thus how the joins are represented might be the cause for an incorrect guess on my part. Also, database is a reserved word and should not be used as a db object name. See http://allenbrowne.com/AppIssueBadWord.html and https://access-programmers.co.uk/for...d.php?t=225837

There are a few ways to accomplish sequential numbering; just don't do any of them in the table itself. The main consideration is whether or not you can foresee concurrent users creating a record more or less at the same time. I presume you have or will split the db, which makes this more probable than not. Then the issue comes down to when to create the catalog number - at the beginning or end of a record creation process. At the beginning, you run the risk of two or more users creating the same number concurrently, and the last one to save the record gets a write conflict error. At the end, you eliminate that, but can be faced with not having related records in other tables at the moment that number is saved, assuming you have designed things that way. If the record is canceled/deleted, you end up with gaps, which you've already stated is OK. More details about the user environment would be useful for us to suggest the more favorable approaches.

[orange]

bps,

I just glanced at your post. We had a long thread a while back dealing with the same general subject matter(proteins/vials etc).
You may get some ideas/background from the thread. It may be way off base, but then again....