Getting result Query into table

[HV_L]

Hi,
I have this query which in facts Concatenate a Bill number (factuurnummer)
I want to save this value for later reference, but how to get the value (Factuurnummer) into my table (registratie.factuurnr)?
This is the query that produces the Billing number:

SELECT Year([datum]) AS jaar, 
 projecten.projectid AS Project,  
 DatePart("ww",[datum],2,2) AS Week,  
 periode.Periode_naam AS Per,  
 [jaar] & [Project] & [Per] & [Week] AS Factuurnummer 
FROM periode, projecten INNER JOIN registratie ON projecten.projectid = registratie.projectid 
WHERE (((registratie.datum) Between [periode].[Periode_startdatum] And [periode].[Periode_einddatum]));

 

[John Big Booty]

You could convert your query to either an Update or Append query.

 

[HV_L]

Yes have tried both, but I can't get it right.
This is what I have which isnt'working

UPDATE Facturen, projecten INNER JOIN registratie ON projecten.projectid = registratie.projectid SET registratie.factuurnr = [Facturen].[Factuurnummer]
WHERE (([registratie].[projectid]=[projecten].[projectid]));

It set the same value for all records in registratie while there are differnt for records in Facturen..?? Beats me..

 

[John Big Booty]

You problem lies in the highlighted portion of your SQL;

UPDATE Facturen, projecten INNER JOIN registratie ON projecten.projectid = registratie.projectid SET registratie.factuurnr = [Facturen].[Factuurnummer]
WHERE (([registratie].[projectid]=[B][COLOR="Red"][projecten].[projectid][/COLOR][/B]));

Your SQL probably needs to look something like;

UPDATE Facturen, projecten INNER JOIN registratie ON projecten.projectid = registratie.projectid SET registratie.factuurnr = [Facturen].[Factuurnummer]
WHERE (([registratie].[projectid]=[B][COLOR="DarkGreen"][Forms]![YourFormName]![YourControlName][/COLOR][/B]));

This would only update the record related to the current record on your form.

 

[HV_L]

Oke Big Booty
Going to try this later this evening. Thanks for replying.