Auto increment field based on value between two numbers

[DCLAO]

I have inherited an Access DB that is supposed to be kept "alive" but not made much better.
On a weekly basis I get an Excel CSV that I'm supposed to import into one massive table; that works just fine.
However, there is another query that takes values from a table named "New Users From Import Table" and appends them to the current "User" table.
But before running that query I have to manually update a field called "Display Name ID" with the next highest number from the User table but only the highest value that isn't in the 9000 number range.
For example, my next highest "Display Name ID" is 1144, which of course does not include the records that range between 9000 - 9008.
I had to manually enter 89 "Display Name ID" values yesterday and I know there has to be a way to update that field when I pull the data over from the temp table without having to do it all manually.

Here's the query that pulls the data into the "User" table (after I've manually entered the incremented values).

INSERT INTO Users ( [Display Name ID], [User Type], Organization, [Display Name], [Alias Name] )
SELECT [New Users From Import Table].[Display Name ID], [New Users From Import Table].[User Type], [New Users From Import Table].Organization, [New Users From Import Table].[Display Name], [New Users From Import Table].[Alias Name]


FROM [New Users From Import Table];

I have been working with MSSQL so long that I can't seem to wrap my head around how to do this in Access. Any help you could provide would be GREATLY appreciated.

[June7]

Those 89 records needed the same ID for 1 new user or were those 89 individual new users?

[DCLAO]

The 89 records are for 89 new users. I'm thinking that I may need to make a form to update the Display Name ID but I'm not sure how to go about it in the most effective manner. Thanks!

[June7]

I don't think what you want can be done in the INSERT action.

There is a way to run an UPDATE action that will increment a value and populate field.

[DCLAO]

I'm still stuck...

Here's the query I'm using to get the next ID number:

SELECT Max(Users.[Display Name ID]) AS MaxID
FROM Users
WHERE (((Users.[Display Name ID])<9000));


I thought it would be relatively easy to get that new value into my table but when I created this query, Access complained:

INSERT INTO Users (([Get Next Display Name ID LT 9000].[MaxID] + 1) as Display Name ID)), [User Type], Organization, [Display Name], [Alias Name] )
SELECT [New Users From Import Table].[Display Name ID], [New Users From Import Table].[User Type], [New Users From Import Table].Organization, [New Users From Import Table].[Display Name], [New Users From Import Table].[Alias Name]
FROM [New Users From Import Table], [Get Next Display Name ID LT 9000];


Any ideas what I'm doing wrong? I just need that Display Name ID to auto increment to the next available number when the data is pulled from the New Users From Import Table into the Users table.

Any suggestions would be much appreciated.

[June7]

AFAIK, can't do that in an INSERT action for multiple records.

Could run an UPDATE action that would save an incremented value into field of existing records.

[DCLAO]

June7,

Would the update look something like this:

UPDATE [New Users From Import Table] SET [New Users From Import Table].[Display Name ID] = [Get Next Display name ID LT 9000].[MaxID] + 1 ;

Would it work?

[June7]

This works for me:

UPDATE Airports SET Airports.ID = DCount("*","Airports","FAAID<'" & [FAAID] & "'")+1;

That will renumber every record in the table. You want to update only selected records - those where the ID field is empty and you want the seed to be something other than 1. So your situation is much more complicated. I haven't figured that one out yet.

[DCLAO]

This works for me:

UPDATE Airports SET Airports.ID = DCount("*","Airports","FAAID<'" & [FAAID] & "'")+1;

That will renumber every record in the table. You want to update only selected records - those where the ID field is empty and you want the seed to be something other than 1. So your situation is much more complicated. I haven't figured that one out yet.

June7,

I'm probably making this harder than it needs to be...

Here's what I'm trying now but isn't working.

UPDATE [New Users From Import Table]
SET [New Users From Import Table].[Display Name ID] = DCount("*", "[New Users From Import Table].[Display Name ID]", "[Max ID]<'" & [Max ID] & "'")+1
WHERE ((([New Users From Import Table].[Display Name ID]) Is Null));


It popups and asks me for the MaxID.
I wanted it to pull from the query I built but it won't.
So a popup that I input data into isn't unreasonable but it still won't work.

The table [New Users from Import Table] contains only the new users and doesn't contain any [Display Name ID] values, yet, so the WHERE isn't really needed, I guess.
This table is where I'd hand-jammed the [Display Name ID] but after entering 89 of them by hand with the last data dump, I thought a query would be better.
All I need to do is to take a number (pulled from a query or entered into a popup) and have the [Display Name ID] increment that value for all the records in this table.
Later I do an append to the main User table but since the Display Name ID is a key field in the User table I'm trying to set it in the [New Users From Import Table].

What am I missing? I just know it can't be that hard but I can't see it.

[June7]

So I tested updating only records where ID was empty and was simpler than I expected. Didn't have to lookup max ID. This works for me:

UPDATE Airports SET ID = DCount("*","Airports","FAAID<'" & [FAAID] & "'")+1 WHERE ID Is Null;

[DCLAO]

June7,

This mostly works.

UPDATE [New Users From Import Table]
SET [New Users From Import Table].[Display Name ID] = DCount("*","[New Users From Import Table]","[Display Name ID]<'" & [Display Name ID] & "'")+1
WHERE [Display Name ID] Is Null;

How would I tell it to use a specfic number for the starting count? The [New Users From Import Table] only contains the users that were not in the database as compared to the import table. So the ID is always blank but it wants to start with number 1 and I want it to start with 1145 (which is my next [Display name ID]) in the User table. I only append the records to the User table once the [Display Name ID] is updated in the [New Users From Import Table] and that table is deleted before every new import.

Would it be here that I'd enter the value each time? For this example 1144?

UPDATE [New Users From Import Table]
SET [New Users From Import Table].[Display Name ID] = DCount("*","[New Users From Import Table]","[Display Name ID]<'" & 1144 & "'")+1
WHERE [Display Name ID] Is Null;

[DCLAO]

I even tried this:

UPDATE [New Users From Import Table] SET [New Users From Import Table].[Display Name ID] = DCount("*","[New Users From Import Table]","[Display Name ID]<'" & [Display Name ID] & "'")+1145
WHERE ((([New Users From Import Table].[Display Name ID]) Is Null));

But it set all of the IDs to 1145...

[June7]

Sorry, worked because my record count coincided with the ID value. This did get complicated.

Had to create and save a query object that selected only the records where ID was empty:

SELECT * FROM Airports WHERE ID Is Null;

Then:
UPDATE Q1 SET Q1.ID = DCount("*","Q1","FAAID<'" & [FAAID] & "'")+DMax("ID","Airports","ID<9000")+1;

Note that all my examples require another existing unique ID - in my db that is FAAID (ANC, SEA, SFO, etc).

In your case, maybe that is company name?

[DCLAO]

June7,

THANKS!!!! That worked!!!

Here's how I had to write my code, thank you SO much for your help!

UPDATE [New Users From Import Table]
SET [New Users From Import Table].[Display Name ID] = DCount("*","[New Users From Import Table]","[Alias Name]<'" & [Alias Name] & "'")+DMax("[Display Name ID]","Users","[Display Name ID]<9000")+1;