Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Red face New Database Set up - Help needed

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?

  3. #3
    Join Date
    Sep 2009
    Posts
    7
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  5. #5
    Join Date
    Sep 2009
    Posts
    7
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    and / or take a screen dump of the relationships view and post that
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2009
    Posts
    7
    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...}

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •