Hi,

I am very new to access so please forgive me if my question is basic.

I am trying to create a database for a visual basic 'shift storage' project. I want to create a table that stores details of employees, and for each employee have a 'sub table' that stores various bits of information about their daily shifts. So far I have come across sub data sheets but this seems to associate the same table of data with all employee entries.

Is there a way to create a table template that I can re-use for every employee - i.e. only stores the parent employees shift details? So basically I want sub datasheets that have the same structure as each other but only contain values that are relevent only to the parent entry (not table).

Can someone point me in the way of a topic or tutorial that may help me as I don't know what I am looking for.

Cheers

Andy

Hi Andy,

can you give examples of the differences in the detail information?

Hi,

Differences would just be things such as the shifts that particular person is working on that particular day, the number of hours that shift relates to, if they actually did work the shift or not, if they didn't work the shift who covered it etc.

Thanks

Andy

Hi Andy,

these should not be structure changes ... post a relationship diagram of your database according to guidelines in this document:

Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1595005
8-part free tutorial that covers essentials in Access

Hi,

sorry I have been away for a few days.

Without having a 'Record' table for each personnel I don't know how to create a relationship between the tables. The following picture shows my two tables and their fields but no relationship links due to this.

I have made something similar in excel with personnel as column headers and dates as row headers - and then had a sheet for each of my 'Record' fields - a very messy way to do it which is why I want to explore Access ways of achieving the same result.

Cheers

Andy

hi Andy,

"I don't know how to create a relationship between the tables"

read Access Basics -- pay close attention to the Normalization and Relationships sections

do not use DATE as a fieldname, it is a reserved word

Problem names and reserved words in Access, by Allen Browne
http://www.allenbrowne.com/AppIssueBadWord.html

Personnel should have a primary key (PK) -- is this [Engineer Number]?
btw, do not use spaces in fieldnames, it is allowed, but they create problems

what is the data type for [Engineer Number]?

If you need to relate Personnel to Record, you need to put the PK for the Personnel table into the Record table as a foreign key (FK)

For instance, if you would have a field such as this in Personnel and denote it as the PK:

PersID, autonumber

then you would put -->
PersID, long integer

into the Record table

don't use ? or any other special character except underscore _ in fieldnames

consider shorter fieldnames

[covering TR Telephone Number] would be more appropriate for a field DESCRIPTION -- fieldname mighe be something like 'Phone'

anyway, you have several problems with this design -- think they will be cleared up if you take the time to read Access Basics. It is only 100 pages and has lots of screen shots