Table Design for Report Dates

[Pam]

I need to create a report in a database that resembles a spreadsheet. Heres
the basic layout - rows for skills, training, etc. descriptions and columns
for progress at quarterly intervals, as below.

1stqtr 2ndqtr 3rdqtr 4thqtr
Listening
Solving
Selling

I know I need a table for salespersons, a table for row descriptions and a
table for code description, but I'm not sure the best way to design for
entries for each quarter - entries will be series of codes - M=master,
C=capable,etc.

If anyone has suggestions, it will be greatly appreciated.
Thanks in advance,
Pam

 

[Jerry Whittle]

Something like this:

Skills TheCode TheDate
Listening C 1 Jul 08
Listening M 1 Jan 09
Solving C 1 Jul 07
Selling C 1 Jul 08

Why? First of all the words Date and maybe Code are reserved in Access and
might cause problems. Putting something like "The" in front of them avoids
that problem.

Putting an actual Date instead of a quarter can help if your data goes over
a year. Otherwise you might put in duplicate entries for a quarter even
though it happened years apart. So unless you are only worried about the
quarter, a date works better. You could use criteria to avoid going back too
far in the past. Also you can put in the actual date that it happened and
with a little work convert it to a Quarter.

Now as far as making it look like you want, a crosstab query does the job.
It takes the 'down' look of a table and converts it to an 'across' look of a
spreadsheet. There are some problems with making a crosstab query work inside
a report, but it's do-able using a little tweaking.

 

[Pam]

Thanks, Jerry for your help.

Something like this:

Skills TheCode TheDate
Listening C 1 Jul 08
Listening M 1 Jan 09
Solving C 1 Jul 07
Selling C 1 Jul 08

Why? First of all the words Date and maybe Code are reserved in Access
and
might cause problems. Putting something like "The" in front of them avoids
that problem.

Putting an actual Date instead of a quarter can help if your data goes
over
a year. Otherwise you might put in duplicate entries for a quarter even
though it happened years apart. So unless you are only worried about the
quarter, a date works better. You could use criteria to avoid going back
too
far in the past. Also you can put in the actual date that it happened and
with a little work convert it to a Quarter.

Now as far as making it look like you want, a crosstab query does the job.
It takes the 'down' look of a table and converts it to an 'across' look of
a
spreadsheet. There are some problems with making a crosstab query work
inside
a report, but it's do-able using a little tweaking.