Report Output

[Steven Stewart]

Hi there,

On monthly reports, there are 7 records (7 rows). I want it to always
show these 7 rows, even if a record for a particular "Program" and
"Team" do not exist--and even if it didn't exist, I want it to show
zeros in the columns. As of now, I do this by creating a record whose
fields are all 0 in order to make it so these 7 rows always show up. Is
there a better way than creating these "0" records which unnecessarily
add to the size of the database?

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

Steven Stewart <o6***@unb.ca> wrote in message news:<3f*********************@news.frii.net>...

Hi there,

On monthly reports, there are 7 records (7 rows). I want it to always
show these 7 rows, even if a record for a particular "Program" and
"Team" do not exist--and even if it didn't exist, I want it to show
zeros in the columns. As of now, I do this by creating a record whose
fields are all 0 in order to make it so these 7 rows always show up. Is
there a better way than creating these "0" records which unnecessarily
add to the size of the database?

Thanks!

is this report based on one table or many? Could we see the SQL?
(Sorry, my psychic powers are failing...) Sounds like you're looking
for an outer join, but without a clearer description, it's hard to
tell.

[Steven Stewart]

There is an Employees table and DataRecords table. They are joined by
EmployeeID (INNER JOIN). It is a one-to-many relationship; one employee
can have many records.

The DataRecords table has a combined primary key that includes
EmployeeID, Program, Team, Date. There also are several fields for
statistics that are stored in the record.

Sometimes, there is no record for a given date for a particular program
and team, so in order to make it show up on the report, I have to create
a record for an employee named "work tracker" for that program/team/date
with all the stats at 0. This is necessary because even if the stats
are 0, they are supposed to show up on the report. If I didn't do this,
the row showing that program/team for a given date would not show up in
the report at all because there would be no record existing to show it.
By doing it the way I am now doing it, it will show up.

Rather than storing a bunch of records with the stats all set to zero, I
am wondering if there is a way to get it so that the report will
recognize that no record exists for a given program/team/date and
display this in the report without my having to create these extra
records.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!