Hi

I'm wondering if someone would be able to help advise how best to set up some tables/relationships for an access 2003 database?

In Access currently we have a form set up that lists all the possible tasks someone in our team can complete throughout the day. The team members then open this form, select their name from the drop down box on the first tab and fill in the number of times they have done each task as they go. This records their productivity stats throughout the day. After they save each day we are left with a record against their name and a total for every task.

We set this up as a single basic table with a list of all the tasks. However this is then causing problems when we write queries to extract information about specific tasks.

Can you advise the best way to set up a list of employees and a list of tasks so that we are able to extract information by person, by task and by/between dates?

The only employee information we hold is a name, and the only task information we hold is a task name and a corresponding numerical box to hold the number of times they have completed that task today.

Please can you post the structure of the table or DDL SQl to create it? I am not clear exactly what the table structure is.
Also, you are asking for a query and not database design help, yes?

Hi Pootle Flump

I believe the table set up is incorrect which is why I can't get my queries to work - I can't pull out single task information as all the tasks are lumped into one big table.

The structure of the table is very basic there are only 2 columns, Field Name and Data Type

The field names include like Payment, Journals, Conversion Mails, etc and for each field name there is a data type which is Number as this is what will hold the number of times each task is done. However it also has a field name called Name which has corresponding data type Text which holds the employees name.

I think I need to split my table into at least 2 tables, one table of tasks and one table of employees but I'm not sure if I need to go further than that and split each task into its own table? It seems like an awful lot of work (we have approx 100 tasks) when all the information we hold is task name and number?

Thanks,
Heather

It does sound wrong however again I don't understand your table structure. Please can you write it out definitively using standard syntax rather than a narrative? If you don't know what standard syntax would look like then let me know and I'll dig up a link.

I am not sure how to write it out using syntax, if you could give me an example link I would be grateful

Thanks,
Brekky

TableName {primary key column #1, primary key column #2, non-key attribute 1, non-key attribute 2, non-key attribute 3}
Primary key column(s) are in bold, everything else normal weight.
After the definition, write out any alternate keys and foreign keys including the table they reference.
Data types are rarely needed.

Repeat for all relevant tables.

and / or take a screen dump of the relationships view and post that

That's the problem - I only have one table with one Primary Key - ID which is an auto number. So I have no relationships either. Every field on the form that employees fill in is stored in this one big table.

Daily Stats Checklist { ID, Date, Name, Start, Lunch Out, Lunch In, Finish, Hours Worked, IPB Breaks, IPB Breaks mails, Payment Checking, Payments Processed, Receipts Processed, IPB PAY mails, etc etc...}