Help on Access For a Beginner

[jbhakta]

Hi,

I need some help on access database. I have a table in Access with following Fields:

CODE - Text (150 different Codes)
PRODUCTION PHASE - Text (10 different phases)
START TIME - Date
END TIME - Date
AVERAGE HEADCOUNT - Numeric


the data under the fields looks something like this:

eg1 : CODE1; Phase 1; 20/10/2003; 15/02/2004; 15
eg2 : CODE1; Phase 2; 16/02/2004; 25/03/2004; 5
etc etc through to
eg3 : CODE1; Phase 10; 23/11/2004; 06/01/2005; 20

eg4 : CODE2; Phase 1; 15/11/2003; 10/12/2004; 3
through to
eg 5 : CODE2; Phase 10; 25/12/2004; 12/1/2005; 15


and on on for all 150 code and for each phases.


this table is a linked table to some data in an excel file. This data continually changes.

I want to create another table from above table. My new table has the following Fields:

CODE - 150 of unique code
JAN 05 - numeric
FEB 05
MAR 05 Etc...
through to SEPT 09.

I need to work out from the first table the average headcount that fall into the fields of months above.

eg 1 : CODE1; and then average headcount (calculated from the first table) under the relevant months
eg 2 : CODE2; and then average headcount (calculated from the first table)under the relevant months

etc...


the CODES are not repeated in this second table.

please let me know if anyone knows how to calculate this second table. I am a beginner on ACCESS.

J.

 

[SydneyGeek]

I want to create another table from above table. My new table has the following Fields:

CODE - 150 of unique code
JAN 05 - numeric
FEB 05
MAR 05 Etc...
through to SEPT 09.

This is not a good idea. Databases work best with the structure that you have already set up (Start Date, End Date).
You can use queries to get the result you are after -- they allow you to get different views of the data in the tables.
Try a crosstab query (there's a Wizard to help). In the first screen of the query, insert CODE. In the second screen of the query, insert START DATE (or END DATE, whichever is best for you). You get the option (a small button on the screen) to group the results. Click this and select Month. In the third screen, insert HEADCOUNT and use the Sum calculation.

You'll get each code by row, with months across the top and total headcount in the grid.

Denis