| re: Empty report grouping
Brian,
There may be another way, but offhand this is how I'd do it.
1) You'll need a table listing all the available scrap dates in it,
sequentially. Call it tblScrapDates, and add a field named ScrapDate
2) Your scrap details table. tblScrapDetails, with the fields ScrapDate and
ScrapCost
SELECT D.ScrapDate, iif(isnull(Sum(S.ScrapCost)),0,Sum(S.ScrapCost)) AS
DailyScrapCost
FROM tblScrapDates D LEFT JOIN tblScrapDetails S ON D.ScrapDate =
S.ScrapDate
GROUP BY D.ScrapDate;
Substitute your actual table and field names.
Output would resemble:
ScrapDate DailyScrapCost
01/15/03 $200.00
01/16/03 $0.00
01/17/03 $250.00
"Brian Coy" <briancoy@accessky.net> wrote in message
news:acf8bb5a.0312041212.e9840f4@posting.google.co m...[color=blue]
> I am creating a database to track scrap on a daily basis at my plant.
> I have to provide a weekly scrap report with the amount of each part
> scrapped per day. I have the basic database set up, and am trying to
> generate a report. I have created a report that will show all the
> data. But I need to show either an empty group for the data on days
> that there is no scrap or a comment relating as much.
>
> My report is set up with a weekly grouping, a daily grouping and a
> grouping for each program we run parts for. I have a query using
> Between [Beginning Date] and [Ending Date] to select the records for
> this.
>
>
> When I run the report it shows everything as I had expected, but the
> days that we didn't scrap any parts did not have the headers or the
> footers show up for that day. is there a way to force this? I have
> checked the help and the MS Knowledge base, but I am not sure if it is
> a problem with the report or the query.
>
> Thanks for any help.[/color]
| 
