Retrieve auto-generated number to linked table (1 Viewer)

Garbensor

New member
Local time
Today, 07:22
Joined
Dec 9, 2019
Messages
5
Hello, I'm beginning to learn Access and I can't seem to find the answer so I think I'm approaching this in a wrong way.

I have an Excel linked table(excel table) with 4 columns.

Name, Address, Amount, ID

From this table I run an apend query to an access table (Table1) with the same columns.

This table also generates an auto number that I then copy and paste back into the excel linked table. This is so I can append the data from (excel table) to another table (Table 2)

I want to be able to retrieve the auto-generated numbers from Table 1 without having to copy-paste. Thanks!

Excel table-->table 1-->excel table(retrieve auto generated number)-->table 2
 

isladogs

MVP / VIP
Local time
Today, 14:22
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF.
You shouldn't be storing the same Excel data in two separate tables as you are duplicating data.

If you need that autonumber field together with some other fields in a separate table then create a relationship between tables 1 & 2 with referential integrity and using cascade update. You should then be able to populate the same field in table 2 automatically. Depending on the other data involved, a number field may be better for the common field in table 2.
 

Garbensor

New member
Local time
Today, 07:22
Joined
Dec 9, 2019
Messages
5
I see, but I can't use that suggestion

Once table 1 is updated with the info from excel, I just need to move it to table 2, but I dont know how to do move only the rows from the excel, as table 2 has to take in duplicates. So if I append table 1 to table 2, it will add 14k rows :/


Thats why I thought it was simpler to retieve the auto gen numbers and then append only the rows from the excel to table 2.
 

isladogs

MVP / VIP
Local time
Today, 14:22
Joined
Jan 14, 2017
Messages
18,186
Sorry but I don't follow that
1. Why can't you use my suggestion?
2. Why import to Table1 only to move the data to Table2?
Does that mean you then empty Table1? Why not import direct to Table2?
3. Do you want duplicate records imported or are you trying to prevent them?
4. 14k records isn't many for an Access db
 

Garbensor

New member
Local time
Today, 07:22
Joined
Dec 9, 2019
Messages
5
1. Some data that will be inputted in table 1 shouldn't be in table 2. Its only specific data that needs a user to decide based on non access parameters.
2. Table 1 generates an auto number for the row. After that I can move the row toTable 2. As table 2 needs that auto number, then it will generate amother auto number
3. If I import the data from table 1 it will create duplicates in table 2 because duplicates have to be allowed as table 2 takes data from many sources.
4. True.
5.this database was created 12 years ago, nobody understands it as everyone that created left. Now internboy has to fix that :(
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:22
Joined
Feb 19, 2002
Messages
42,970
Being an intern is a dirty job but somebody has to do it :)

As everyone is trying to explain, it is just wrong to store the same data in multiple places. If the application is not currently doing that, you should not be modifying it to duplicate the data. Clearly we don't understand your explanation of what the purpose of importing into table1 and then moving some of the data to table2 is. For starters, if table1 is a temporary table, then rather than importing to it and then copying to table2, link to the spreadsheet and run an append query that takes criteria to append only the required data to table2. That is only one of the many possible solutions.

A question though -- Is the ID in the spreadsheet unique? If it is, we can use that to help you to the correct solution.

A second question -- What is the criteria to copy a row from table1 to table2?

A third question -- Why do you need to copy the PK from table1 when you append to table2? Are you using that to join back to table1 for some reason?
Let's start with those 3.5 questions to see if we can narrow down a good solution for you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:22
Joined
Oct 29, 2018
Messages
21,357
Hi. I don't want to add any more confusion into the mix, so I'll just say: Welcome to AWF!
 

Garbensor

New member
Local time
Today, 07:22
Joined
Dec 9, 2019
Messages
5
Being an intern is a dirty job but somebody has to do it :)

As everyone is trying to explain, it is just wrong to store the same data in multiple places. If the application is not currently doing that, you should not be modifying it to duplicate the data. Clearly we don't understand your explanation of what the purpose of importing into table1 and then moving some of the data to table2 is. For starters, if table1 is a temporary table, then rather than importing to it and then copying to table2, link to the spreadsheet and run an append query that takes criteria to append only the required data to table2. That is only one of the many possible solutions.

A question though -- Is the ID in the spreadsheet unique? If it is, we can use that to help you to the correct solution.

A second question -- What is the criteria to copy a row from table1 to table2?

A third question -- Why do you need to copy the PK from table1 when you append to table2? Are you using that to join back to table1 for some reason?
Let's start with those 3.5 questions to see if we can narrow down a good solution for you.


Normally the data is introduced in Access through a form that does double entry bookkeeping. This data goes to Table 1 and Table 2.

I want to upload the data in batches so that we don't have to spend hours entering it manually.

Table 1 generates an autonumber ID (allows duplicates) for 1 or 10k rows and then Table 2 assigns the unique ID(allows duplicates).

Criteria: every row that goes into Table 1 will get an ID (duplicates allowed) and then it will go to Table2. I only want the rows I import from Excel to do this and not the rows that will be added by the user form.

If I append the data from Table 1 to Table 2 is going to generate a lot of duplicates. Thats why I would like to only append the rows from the excel file. E.g. if I append 20 rows to Table 1, I would only like to append those 20 rows to Table2.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:22
Joined
Oct 29, 2018
Messages
21,357
Normally the data is introduced in Access through a form that does double entry bookkeeping. This data goes to Table 1 and Table 2.

I want to upload the data in batches so that we don't have to spend hours entering it manually.

Table 1 generates an autonumber ID (allows duplicates) for 1 or 10k rows and then Table 2 assigns the unique ID(allows duplicates).

Criteria: every row that goes into Table 1 will get an ID (duplicates allowed) and then it will go to Table2. I only want the rows I import from Excel to do this and not the rows that will be added by the user form.

If I append the data from Table 1 to Table 2 is going to generate a lot of duplicates. Thats why I would like to only append the rows from the excel file. E.g. if I append 20 rows to Table 1, I would only like to append those 20 rows to Table2.
Hi. I think this was already asked before, but I'm not sure if you've answered. Why can't you just import the Excel data directly to Table2 as well? Why do you need to store Table1's ID in Table2?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:22
Joined
Feb 19, 2002
Messages
42,970
@Garbensor,
It is most helpful to us if when we ask specific questions, you try to answer the questions. We now have a better idea of what you are trying to do but why you are doing this in Excel rather than Access is not clear. Also, I don't understand how, if you are doing this data entry in Excel you will identify which rows were added via Excel. Aren't they all added via Excel?
 

Cronk

Registered User.
Local time
Tomorrow, 01:22
Joined
Jul 4, 2013
Messages
2,770
Seems to me that the OP has data in some system that is being made redundant by the Access db and wants to import the data from the old system into Access.


@Garbensor, what defines a duplicate record? It's possible in an Access table to create a composite index of multiple fields and make that index unique. This will prevent the import of an Excel row which already exists in Table1.



So if fields F1, F2 and F3 being all equal in 2 records, means that the records are duplicates, create a composite index using those 3 fields.


Search "access create composite index" to see how to create a composite index but make sure you set Unique in the properties of the index.
 

Garbensor

New member
Local time
Today, 07:22
Joined
Dec 9, 2019
Messages
5
Hi. I think this was already asked before, but I'm not sure if you've answered. Why can't you just import the Excel data directly to Table2 as well? Why do you need to store Table1's ID in Table2?

Table 1 generates an autonumber ID that I need so I can put the data in Table 2.

Table 1 groups data. So:

1. John. 100. Alaska
1. John. 200. Alaska
2. Charles.300. Texas

Table 2 asigns the unique number

5. 1. John. 100. Alaska
6. 1. John. 200. Alaska
7. 2. Charles. 300. Texas


@Garbensor,
It is most helpful to us if when we ask specific questions, you try to answer the questions. We now have a better idea of what you are trying to do but why you are doing this in Excel rather than Access is not clear. Also, I don't understand how, if you are doing this data entry in Excel you will identify which rows were added via Excel. Aren't they all added via Excel?

The data is generated in Excel. Once it reaches a certain amount is starts to get transfered to Access through a form, 1 record at a time, which takes hours and hours. But sometimes 1 record in Table 1 has 10k records in Table 2. Im only focusing on those records that are 1:1

The form stores the data both in Table 1 and Table 2. (I dont understand the code and the people that made it are no longer around)

So I want to be able to transfer the data in batches to stop doing manual entry.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:22
Joined
Feb 19, 2002
Messages
42,970
I'm still confused by how the data is stored in Excel and I'm not sure why you would want to restrict this to just the 1:1 records.

In your example are the 1 and 2 generated by Excel or by Access? Autonumbers in Access are unique so you couldn't have two rows with the same autonumber.

Are there TWO spreadsheets involved? Can you upload a sample set of data and show us how you want to see it in table1 and table2?

None of this data needs to be retyped which is what it sounds like you are doing. Why is the data originally typed into a spreadsheet? Why is it not entered directly into Access?
 

Users who are viewing this thread

Top Bottom