Simple Append if not exist

[CoffeeGuru]

It seems today that everyuthing I am trying to do smacks me around the face...
I have tblForcastMaster which is updated monthly via an Excel Linked table ForecastUpdate

Both tables have the same fields
firstly a query is run that will update all existing forecast multipliers with ammended values I have got this to work

Now I need to Append records from ForecastUpdate into tblForecastMaster where there is no matching record

this is my attempt but I cant even get a select to work

SELECT 
ForecastUpdate.Territory, ForecastUpdate.Tier3, ForecastUpdate.Period, ForecastUpdate.Multiplier, tblForecastMaster.Period
FROM 
ForecastUpdate 
INNER JOIN 
tblForecastMaster 
ON 
ForecastUpdate.Period = tblForecastMaster.Period
WHERE 
(((tblForecastMaster.Period) Is Null));

ForecastUpdate
Country | Code | Period | Value
GB | A123 | 01/011/2013 | 1.000789
GB | A123 | 01/012/2013 | 0.001234
GB | A125 | 01/012/2013 | 1.440739
IB | A123 | 01/012/2013 | 1.678832

tblForecastMaster
Country | Code | Period | Value
GB | A123 | 01/010/2013 | 1.12789
IB | A123 | 01/011/2013 | 1.740944
DE | A125 | 01/011/2013 | 1.846490
GB | A123 | 01/011/2013 | 0.783099

 

[CJ_London]

try changing your inner join to a left join

 

[CoffeeGuru]

Hi CJ London

Apologies, I ended up leaving work early on Friday so I have only just seen your help.
Unfortunatly LEFT JOIN did not work

I have made a small change but I still get no select, however using your LEFT JOIN I now get

A message box to Enter tblForecastMaster Parameter Value

SELECT ForecastUpdate.*
FROM tblForecastMaster 
LEFT JOIN ForecastUpdate
ON (tblForecastMaster.Period = ForecastUpdate.Period) 
AND (tblForecastMaster.FCMultiplier = ForecastUpdate.Multiplier) 
AND (tblForecastMaster.Territory = ForecastUpdate.Territory) 
AND (tblForecastMaster.Tier3 = ForecastUpdate.Tier3)
WHERE ((([tblForecastMaster]) Is Null));

now I'm totally confused.

 

[JHB]

You forgot the "Period".

WHERE (((tblForecastMaster[B][COLOR=Red].Period[/COLOR][/B]) Is Null));

 

[CJ_London]

you may also need to change your join round to

FROM ForecastUpdate
LEFT JOIN tblForecastMaster

 

[CoffeeGuru]

OK I think I may have the root cause of my issue...
You cannot change, add, or delete data in tables that are linked to an Excel
workbook.
http://support.microsoft.com/kb/904953/en-us


time to rethink my problem

UPDATE:
yep problem solved...
Thank you very much everyone who took the time to try and help though. I do appreciate it.

 

[CJ_London]

You cannot change, add, or delete data in tables that are linked to an Excel
workbook.

Correct, but in your original post you were updating tblForecastMaster - I assumed this is a table in your db and not a linked table.

If it is linked, then you need to not link it but have it as a table in your db. Once you have completed updating it, you can then export it to excel

 

[CoffeeGuru]

I am indeed updating tblForecastMaster and this is an Access Table
The data it is getting the values from was ForecastUpdate which was an Excel Linked Table.

Apologies if I did make that clear

I have created an import table which over writes each time from Excel
and then update tblForecastMaster from the import table
a bit long winded but nothing a macro can't handle.

 

[CJ_London]

you can import direct from a linked excel table but if you have found a way, that's all that matters.

There is a benefit of importing to a temporary table in that you can add indexes etc which can make a big difference to performance - one of my clients imports around 1/2m records/month and it reduces running time from a couple of hours to less than a minute!

 

[CoffeeGuru]

- one of my clients imports around 1/2m records/month and it reduces running time from a couple of hours to less than a minute!

That is music to my ears
i'm currently building a pilot case
But when this goes live (hopefully in Jan 2014) I will have at least that maybe more.
I still have to set it up for the data to be stored on SQLserver, Access will then just be the front end, I have no idea how to do that yet