Inserting last auto-generated ids

[Vbanoob98]

I have a linked table from excel which I refresh every month and export the data to another Access table. This table generates an autoid for each row which I copy and then paste back into the excel. After this I export the data with the ID to another Access table.

I was wondering if there was a way to autmate this to reduce user error. Thanks a lot

Excel linked table-->access table 1 (generates ID)--->back to excel linked table-->access table 2

 

[Vbanoob98]

Yes thats very useful. How can I do this for lets say 200 rows instead of just 1? Or will this do it for all the rows?

And how could I join the result to my other table?

 

[xenou]

Possibly you are looking for something like this if you want to join two tables - where Table2 has the ID

select t2.ID, t1.Name, t1.Amount, t1.Address, t1,Total
 from Table1 t1
 inner join Table2 t2
on t1.Name = t2.Name
and t1.Amount = t2.Amount
and t1.Address = t2.Address
and t1.Total = t2.Total

 

[welshgasman]

I have a sneaking suspicion this is cross posted with Retrieve auto-generated number to linked table - Access World Forums

 

[Vbanoob98]

Possibly you are looking for something like this if you want to join two tables - where Table2 has the ID

select t2.ID, t1.Name, t1.Amount, t1.Address, t1,Total
from Table1 t1
inner join Table2 t2
on t1.Name = t2.Name
and t1.Amount = t2.Amount
and t1.Address = t2.Address
and t1.Total = t2.Total

Thank you, I will try this tomorrow.

 

[Vbanoob98]

Possibly you are looking for something like this if you want to join two tables - where Table2 has the ID

select t2.ID, t1.Name, t1.Amount, t1.Address, t1,Total
from Table1 t1
inner join Table2 t2
on t1.Name = t2.Name
and t1.Amount = t2.Amount
and t1.Address = t2.Address
and t1.Total = t2.Total

I've been playing with this and it seems it requieres a unique identifier to work properly? Because when I put all the fields it just returns the headers.

 

[Vbanoob98]

I've been playing with this and it seems it requieres a unique identifier to work properly? Because when I put all the fields it just returns the headers.

I do have blank cells in both tables, Im guessing thats the issue

 

[xenou]

When you say it just returns the headers that means that the query resulted in no records (an empty set). That suggests that there is no match on the join criteria. You may need to examine one or two examples you think should be in the result and work out why they were excluded based on the join criteria. I'm doing a lot of guessing about your data - not able to say anything specific without an some sample data.