Solved Starting Appended Data at 1 (2 Viewers)

[dullster]

I have a Database that I use as a Template to load new data into when setting up for other people. When I append the new data it does not start at 1. With the database empty, I have created a new ID with Auto Number in each table and I have compacted the Database. The appended Data does still not start at 0. I done a lot of reading and it appears what I have done should work but it isn't.

That results in me checking 4 queries and putting in the New ID number with every set up.

Any ideas how to get each table to restart at 1?

 

[MajP]

As far as I know no access autonumber starts at 0. They all start at 1 unless set to random.

 

[dullster]

As far as I know no access autonumber starts at 0. They all start at 1 unless set to random.

Ok, then I revise my question to say start at 1.

 

[MajP]

Where is it starting? Is it some random number or where you left off before deleting out all the records and then C&R?

 

[theDBguy]

Any ideas how to get each table to restart at 1?

As @MajP was trying to say, the Autonumber field should restart at 1 if you empty the table and do a C&R before appending the new records.

 

[plog]

Also, who cares? Why must your numbers start with a particular value?

Autonumbers should only be unique. If you have that they will serve their purpose. If you are using them for another purpose other than uniqueness, you shouldn't.

So, what exactly is the issue if they don't start with a particular value?

 

[dullster]

Where is it starting? Is it some random number or where you left off before deleting out all the records and then C&R?

I believe it is starting off from where i left off deleting out all the records.

 

[dullster]

Also, who cares? Why must your numbers start with a particular value?

Autonumbers should only be unique. If you have that they will serve their purpose. If you are using them for another purpose other than uniqueness, you shouldn't.

So, what exactly is the issue if they don't start with a particular value?

When i load new data. I have to go in and change ID numbers in specific Queries that would not have to do. If they always start at 1, then my ID in the queries should never change.

 

[dullster]

After more searching I tried this and it works. Exactly what i needed. I delete the relationship to the table it is related to, run the query and recreate the relationship.

ALTER TABLE [tblDemoUCAs]
ALTER COLUMN [DemoUCAID] Counter (1,1)

 

[MajP]

I am wondering if you delete the relationship if C&R would do the same thing.

 

[dullster]

I am wondering if you delete the relationship if C&R would do the same thing.

Do you mean delete the relationship before appending data?

 

[MajP]

No just like you did. Remove the related table. C&R. Add the related table back. I am guessing that is needed in both methods.

 

[dullster]

No just like you did. Remove the related table. C&R. Add the related table back. I am guessing that is needed in both methods.

After I load another database I will check and see if this starts out at 1 without running the reset query. If it doesn't go back to one I'll try that and see if that does it. But I think it's just as easy to run the reset query

 

[theDBguy]

After I load another database I will check and see if this starts out at 1 without running the reset query. If it doesn't go back to one I'll try that and see if that does it. But I think it's just as easy to run the reset query

Depending on what you're actually doing, I think the main idea is: if the C&R doesn't require you to reestablish table relationships, then wouldn't it be a simpler approach than the reset query? In other words, compare the following two approaches:

Reset Query Steps

1. Delete Table Relationship
2. Empty Table
3. Run Reset Query
4. Recreate Table Relationship
5. Append New Records

C&R Steps

1. Empty Table
2. Perform C&R
3. Append New Records

However, I am curious why you had to delete the relationship at all. Doesn't the reset query run if you kept the relationship intact?

 

[dullster]

Depending on what you're actually doing, I think the main idea is: if the C&R doesn't require you to reestablish table relationships, then wouldn't it be a simpler approach than the reset query? In other words, compare the following two approaches:

Reset Query Steps

1. Delete Table Relationship
2. Empty Table
3. Run Reset Query
4. Recreate Table Relationship
5. Append New Records

C&R Steps

1. Empty Table
2. Perform C&R
3. Append New Records

However, I am curious why you had to delete the relationship at all. Doesn't the reset query run if you kept the relationship intact?

The tables are all empty in my template. I do not need to delete the relationship to run the reset query. I can run it with the relationship already there. I only need to run it on one table. I don't care how high the other numbers get on any of the other tables. It just keeps me from going in to look and see what the ID is on 4 items and then going into the query and putting the new ID into 4 queries.

 

[theDBguy]

The tables are all empty in my template. I do not need to delete the relationship to run the reset query. I can run it with the relationship already there. I only need to run it on one table. I don't care how high the other numbers get on any of the other tables. It just keeps me from going in to look and see what the ID is on 4 items and then going into the query and putting the new ID into 4 queries.

If you don't mind, can you show us the SQL statement for one of those queries that has the ID field value that needs to stay at 1? Thanks!

 

[dullster]

If you don't mind, can you show us the SQL statement for one of those queries that has the ID field value that needs to stay at 1? Thanks!

I needed it to start at 1. I posted a SQL above but will share it again.

ALTER TABLE [tblDemoUCAs]
ALTER COLUMN [DemoUCAID] Counter (1,1)

 

[theDBguy]

I needed it to start at 1. I posted a SQL above but will share it again.

ALTER TABLE [tblDemoUCAs]
ALTER COLUMN [DemoUCAID] Counter (1,1)

No, I meant one of the four (4) queries you said you would have to constantly update if the ID didn't start with 1.

 

[dullster]

No, I meant one of the four (4) queries you said you would have to constantly update if the ID didn't start with 1.

I need the table to have the same ID numbers so I don't have to go in and change the ID numbers in 4 queries every time I append new data.

SELECT tblEmployees.EmployeeID, tblDemoClients.ClientFullName, tblEmployees.[Adjusted Basic Salary], [Amount from Column A]-(([Adjusted Basic Salary]-[tblpayrolltaxes].[Lower])*[Percent of Withholding]) AS [Net Wage], [Adjusted Basic Salary]-[Net Wage] AS [Net Wage1], IIf([Net Wage1]<0,0,[Net Wage1]) AS [Net Wage2], tblEmployees.[Number of Exempt], [tblEmployees].[Number of Exempt]*[tblpayrolltaxes].[Exemption credit] AS Exemptions, tblEmployees.[Marital Status], tblpayrolltaxes.MyUCA, tblEmployees.[Salary Pay Period]
FROM tblDemoClients INNER JOIN (tblpayrolltaxes INNER JOIN tblEmployees ON tblpayrolltaxes.[Marital Status] = tblEmployees.[Marital Status]) ON tblDemoClients.DemoClientID = tblEmployees.DemoClientID
WHERE (((tblEmployees.[Adjusted Basic Salary]) Between [tblpayrolltaxes].[Lower] And [tblpayrolltaxes].[Upper]) AND ((tblpayrolltaxes.MyUCA)=391))
ORDER BY tblEmployees.EmployeeID;

 

[gemma-the-husky]

@dullster

If you want the record ID to be kept in sequence and the sequence to be intact, then you can't use an autonumber. You need to manually control the sequence. I imagine this has been pointed out already. You also can't delete records, or you will get gaps.