data not showing where no records

[Chimp8471]

data not showing where no records
I have 2 tables...

tblEvents
tblFMForEradication

i am trying to run a query to generate the number of events that have happened for each code that appears in the tblFMForEradication table.

however when i run the query i only get 17 records returned wheras there are 30 records in the tblFMForEradication table.

If the code has not occured i need it to display zero,

but i always need all the codes in the tblFMForEradication to be listed in this case there should always be 30 records in the list.

please help

Andy

 

[workmad3]

Hmm... I'm not sure if what you want is possible using just SQL. SQL is for finding data, not for changing it according to criteria (which is essentially what you wnat). I'll try and think of a way, but don't be surprised if what you want isn't possible

 

[CraigDolphin]

Well, not sure if this is what you want but you can do this by combining a select query (using criteria) and a crosstab query.

Here's the sql for the select query:

'Query name qryLimitedbyCriteria
'
SELECT tblEvents.FaultcodeLognumber, tblEvents.DayCode, tblEvents.Line, tblEvents.EventCode, tblEvents.MinorStop, tblEvents.MajorStop, tblEvents.Breakdowns, tblEvents.CIP, tblEvents.ProductChange, tblEvents.Maintenance, tblEvents.EventNotes, tblEvents.EngineerNotes
FROM tblEvents RIGHT JOIN tblFMForEradication ON tblEvents.EventCode = tblFMForEradication.Eventcode
GROUP BY tblEvents.FaultcodeLognumber, tblEvents.DayCode, tblEvents.Line, tblEvents.EventCode, tblEvents.MinorStop, tblEvents.MajorStop, tblEvents.Breakdowns, tblEvents.CIP, tblEvents.ProductChange, tblEvents.Maintenance, tblEvents.EventNotes, tblEvents.EngineerNotes
HAVING (((tblEvents.DayCode) Between 6148 And 6154));

and the crosstab based on that query and the tblFMforEradiacation

TRANSFORM Nz(Count([FaultcodeLognumber]),0) AS [Count]
SELECT tblFMForEradication.Eventcode, Sum([Count]) AS TotalEvents
FROM tblFMForEradication LEFT JOIN qryLimitedbyCriteria ON tblFMForEradication.Eventcode = qryLimitedbyCriteria.EventCode
GROUP BY tblFMForEradication.Eventcode
PIVOT qryLimitedbyCriteria.Line;

The field that I think you're trying to generate is named TotalEvents in the crosstab query. Hope it helps