update+insert data table from query

[Sportman1975]

First of all: I have build some nice queries but I'm stuck right now. And I think it's relative easy to solve.

Table 1: Stamtabel clients (clients master table [no unique id])
Field name
1 Klantnaam (text)
2 klantnummer (text)
3 Max age (number)
4 Finance (text)
5 Nog te vorderen (number)

Query: data (all invoices)
Field name
1 Debtor number (text) [same as klantnummer]
2 Debtor name (text) [same as klantnaam]
3 Max age (number) [same as Max age]
4 Finance (text) [same as finance]
5 Open amount (number) [same as nog te vorderen]

Now I want to update the master table.
1) Add new numbers if applicable;
2) Update existing numbers with the new data

Can someone explain me how to do this?

 

[lcook1974]

sounds like you need a form and subform...

use the Master client table as your main form with the klantnummer as the unique ID

Then attach the subform and link the fields for klantnummer on Main and Child form.

Then you should be able to pick your client and the subform will show all related records to that client. Edit them as your choose, since you'll be writing directly to the tables.

Larry

 

[JANR]

You can use an update query to do both in the same run IF you got an unique value in the receiving table you can join on like Klantnummer. It will probably give you some performance issues if both datasets are large.

ex.

UPDATE qryInvoice AS A LEFT JOIN [Stamtabel clients] AS B ON A.[Debtor number] = B.klantnummer
SET B.Klantnaam = A.[debtor name], B.klantnummer = A.[Debtor number], B.[Max Age] = A.[Max Age], B.Finance = A.[Finance], B.[Nog te vorderen] = A.[Open amount];

Marked red are the [DATA] query. It will overwrite existing records in [Stamtabel clients] and add any new records from the query.

Obviously test on a COPY first.

JR