update a multiple fields

[GregB]

I have a field in a table A: [order number] and a filed in table B: [serial
number]
I also have an excell sheet with two columns corresponding to the two fields
mentioned.

An order number can have many serial numbers
For example in my excel sheet a i might have the followging
1 | 12345
1 | 12412
1 | 12515
1 | 12152
2 | 12355
2 | 12611

How can I write a query to update my tables with the information from the
excel sheet?

Thanks for the help!

 

[John W. Vinson]

I have a field in a table A: [order number] and a filed in table B: [serial
number]
I also have an excell sheet with two columns corresponding to the two fields
mentioned.

An order number can have many serial numbers
For example in my excel sheet a i might have the followging
1 | 12345
1 | 12412
1 | 12515
1 | 12152
2 | 12355
2 | 12611

How can I write a query to update my tables with the information from the
excel sheet?

Thanks for the help!

What is in your table now? If there are already ordernumbers and serial
numbers in the table, how can Access reliably determine WHICH of the four 1
ordernumbers needs to be updated with 12345, and which needs to be updated
with 12152? More context please!

If you just want to create all new records with the data from Excel, then you
can use File... Import to import the spreadsheet into your table, or File...
Get External Data... Link to link to it, and then run an Append query.

 

[GregB]

there are alread records the order numbers, and I need to insert the serial
numbers into them..
so all the records exsist but or missing the serial numbers.
For example it dosn't matter which order the serial number # 12345 goes
into, as long as it goes into the one of the 1 records

Is there anyway to do this..

I have a field in a table A: [order number] and a filed in table B: [serial
number]
I also have an excell sheet with two columns corresponding to the two fields
mentioned.

An order number can have many serial numbers
For example in my excel sheet a i might have the followging
1 | 12345
1 | 12412
1 | 12515
1 | 12152
2 | 12355
2 | 12611

How can I write a query to update my tables with the information from the
excel sheet?

Thanks for the help!

What is in your table now? If there are already ordernumbers and serial
numbers in the table, how can Access reliably determine WHICH of the four 1
ordernumbers needs to be updated with 12345, and which needs to be updated
with 12152? More context please!

If you just want to create all new records with the data from Excel, then you
can use File... Import to import the spreadsheet into your table, or File...
Get External Data... Link to link to it, and then run an Append query.

 

[John W. Vinson]

there are alread records the order numbers, and I need to insert the serial
numbers into them..
so all the records exsist but or missing the serial numbers.
For example it dosn't matter which order the serial number # 12345 goes
into, as long as it goes into the one of the 1 records

Is there anyway to do this..

I have a field in a table A: [order number] and a filed in table B: [serial
number]
I also have an excell sheet with two columns corresponding to the two fields
mentioned.

You'll still need SOME way to uniquely identify each record in your table. If
you have ten records, all with null Serial numbers and all with the same Order
number, Access won't have any way to identify which record needs updating! You
may need to write VBA code to open a Recordset (with some arbitrary order) and
step through it:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iNext As Integer
Set db = CurrentDb
strSQL = "SELECT [Serial Number] FROM yourtable WHERE [Serial Number] IS NULL"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
iNext = DMax("[Serial Number]", "[yourtable]") ' find highest existing serial
Do Until rs.EOF
rs.Edit
iNext = iNext + 1
rs![Serial Number] = iNext
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing