Do I need to do this in code

[Geoff Codd]

I have the following query

SELECT Points.Id, DateAdd("d",-1,DateAdd("m",1,"01/" & Format(DateAdd("d",-15,[Date]),"mm/yyyy"))) AS [Month], DataWater.Units, Int(([DataWater].[Units]/([Date]-(DateAdd("d",0,DMax("[Date]","DataWater","[Point_Id] = " & [Points].[Id] & " And [Direct] = 'D' And [Date] < #" & Format([Date],"mm/dd/yyyy") & "#")))))*Int(Format([Month],"d"))) AS Apportioned, Int([Apportioned]*0) AS CO2
FROM Points INNER JOIN DataWater ON Points.Id = DataWater.Point_Id
WHERE (((DataWater.Direct)="D"))
ORDER BY Points.Id, DateAdd("d",-1,DateAdd("m",1,"01/" & Format(DateAdd("d",-15,[Date]),"mm/yyyy")));

which gives the following

Id Month Units Apportioned CO2
9482 31/08/2003 215 196.00 0.00
9482 30/09/2003 555 574.00 0.00
9482 31/10/2003 681 681.00 0.00
9482 30/11/2003 555 616.00 0.00
9482 31/12/2003 645 512.00 0.00
9482 29/02/2004 948 482.00 0.00

The problem I am having is that I need it to show a figure for every month so the report should look like this

Id Month Units Apportioned CO2
9482 31/08/2003 215 196.00 0.00
9482 30/09/2003 555 574.00 0.00
9482 31/10/2003 681 681.00 0.00
9482 30/11/2003 555 616.00 0.00
9482 31/12/2003 645 512.00 0.00
9482 31/01/2004 599.00 0.00
9482 29/02/2004 948 482.00 0.00

So that i get an apportioned figure for each month if if a reading is not taken

I can't seem to find a way of doing this in a query, do i need to do it in code.

If you would like me to attach my db let me know

Thanks
Geoff

 

[namliam]

You could try doing something with a union query....

But you would have to have the end-month dates somewheres....

Regards

 

[Geoff Codd]

I've included a sample db with the two tables and the query associated with them, if someone could have a look that would be great

Thanks
Geoff