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

thedawn

Registered User.
Local time
Today, 23:16
Joined
Mar 29, 2010
Messages
30
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:

Code:
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
 
Last edited:
See if the select query ,in the append, has a count...

If Dcount("*","qsData")=0 then docmd.openQuery "qaAddDefault"
 
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

Code:
 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
 

Users who are viewing this thread

Back
Top Bottom