I need to add a default record if append query has a no value

[thedawn]

Hi There

I am using append queries to populate a results table using various imported spreadsheets. Some months there are two spreadsheets that could be blank they being Leavers and Starters. If these are blank I would like the append query to add a default record with 2 columns. The first being say 'Leavers' and the second being zero.

I have tried using NZ but this doesnt seem to be working so I am assuming I have got the query itself wrong

Here is my current query:

SELECT "Leavers" AS Measure, Nz(Count([DD_LEAVERS]![Emp No]),0) AS [Emp No]
FROM DD_LEAVERS
GROUP BY "Leavers";

If there are records in the DD_LEAVERS table this query works

I have converted this to a select query to just see if I could get records

Thanks in advance

Rich

 

[Ranman256]

See if the select query ,in the append, has a count...

If Dcount("*","qsData")=0 then docmd.openQuery "qaAddDefault"

 

[CJ_London]

sounds like your table design could do with an improvement

Have a table called measures with a single text field called say measure and populated with 'Leaver', 'Starter' etc

then left join this in your query

 SELECT Measures.Measure, nz([Emp No],0) AS EmpNo
 FROM Measures LEFT JOIN (SELECT Measure, Count([DD_LEAVERS]![Emp No]) AS [Emp No]
FROM DD_LEAVERS
GROUP BY Measure) CT ON Measures.Measure=CT.Measure