Sorting Dates and zero values.

[Guest]

Hi there,

I have asked this question before but I didnt really get the understanding
that I need in order to complete this. I am doing an NCR incident reporting
system. Everytime there is an incident where a component is rejected by the
buyer due to damages, there is a date, name of responsible employee, and
dollar value of the loss. I wish to have the employee name on the left side
of a datasheet, and sorted dates on the top, and the dollar value between
them. When it is added to the database, it is recorded as a singular date;
however, I want to have the dates split up by week on the top (columns). I
dont want to mess around with coding to determine what a week really is
relative to the year, I'd rather write a query for each month instead. So I
would like "jan 1-6", "jan 7-12", etc. The reporting period will be monthly.
Also, within the body of the datasheet (dollar value of error), I want to
keep $0 values for each cell where there is no dollar value attributed to the
employee. For example, joe may have had an error on the 8th, and I want that
value in the datasheet, but I would like to see a $0 value for jan 1-6, and
jan 13-18, and so on. I've been building variations of the dollar value by
date within a single query but no data will appear because there are no
records where someone has a dollar value for each week, only 1 or 2 max.
Help, I'm stuck. I need to know how to attribute zero values.

 

[[MVP] S.Clark]

I append the data to a table, with one extra column. In that extra column,
I give each record a value that will later be used for grouping of the data,
and also becomes the column header for the crosstab.

The reason you don't get a straight, easy to understand answer, is that this
is pretty tricky. Start by making a couple of crosstabs, such that you'll
understand what you need to do to your data to get it from normalized to a
reporting layout.