If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design for Report Dates
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 |
#2
|
|||
|
|||
Table Design for Report Dates
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Pam" wrote: 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 |
#3
|
|||
|
|||
Table Design for Report Dates
Thanks, Jerry for your help.
"Jerry Whittle" wrote in message ... 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Pam" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|