Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: tables (2002)

  1. #1
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tables (2002)

    I redesign websites and have a list of about 30 tasks. As I perform each task, I note the start and end time. On MS Excel it's a cinch. But I need help from a veteran on ACCESS.
    TABLES ARE: SITES (fields: sitename, task), TASKLIST (fields: task), TIMES (fields: task, start, end).

    Example: I have 100 websites, and must perform all 30 tasks on each, recording respective times.

    I don't know if my table designation is right or what relationships need to be there. Can you help me?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tables (2002)

    Does each task get completed in one period of work, or do you sometimes have more than one?
    i.e. does each task have just one start and end time, or might there be several?

    If just one the table design would be:

    SITE: SiteID, Sitename
    TASK: TaskID, SiteID, Task, Starttime, Endtime
    Regards
    John



  3. #3
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tables (2002)

    Thank you. It is just a single start & end for each task, since each task can be completed in a matter of minutes. For siteID and taskID, is that a one-up numbering system? Why not just use sitename and taskname?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tables (2002)

    SiteID and TaskID would be AutoNumber fields that act as primary key. The advantages of using an AutoNumber field are:
    - It is automatically assigned by Access, the user doesn't have to worry whether the primary key field has been filled in.
    - The value of an AutoNumber field never changes once it has beem assigned, so even if the name of the site or of the task changes, the ID will remain the same.
    - An AutoNumber field only takes up 4 bytes, while a text field such as SiteName takes up (the number of characters)+1 bytes.

  5. #5
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tables (2002)

    Access gives me an error message in a couple of instances:
    1)when trying to put siteid and taskid into the task table design, it only allows one "autonumber" field

    2) so, if i make siteid simply a "number" field in the task tablewhen trying to join the tables, it says it is only allowed to join fields of the same type

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tables (2002)

    SiteID should be an AutoNumber field in the Sites table, and a Number field with field size Long Integer in the TaskList table.
    TaskID should be an AutoNumber field in the TaskList table.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tables (2002)

    If the list of Tasks for any Site is pretty much standard it is worth having a third table.

    TblSites: SiteID autonumber, sitename Text
    TblTasks: TaskID autonumber, Taskname text
    TblSiteTasks: SiteTaskID autonumber ,siteID Number, TaskID Number, Starttime, Endtime.


    Have at look a the attachment.

    Data is actually entered via frmSites, which provides a dropdown list of tasks to choose. Even though this dropdown shows you the name of a task, it actually stores its number.
    Regards
    John



  8. #8
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tables (2002)

    I admire your ability! This way, even if I need to spend more time on a task, it is able to record it. If you don't mind, I'd like to ask one more question.
    I would like to have a couple of fields called "keywords1" and "keywords2." Would I make another table like this:
    TblSiteKeyword: SiteKeywordID autonumber ,siteID Number, KeywordID Number ?

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tables (2002)

    In addition to:

    TblSiteKeyword: SiteKeywordID autonumber ,siteID Number, KeywordID Number

    You would need :
    tblKeywords: KeywordID autonumber, Keyword text

    to actually hold the words you want to use.
    Regards
    John



  10. #10
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tables (2002)

    Great! Almost have it!
    Could you please take a look at it and see why it is malfunctioning:
    **Data in the Keywords Tables are not showing in the subform
    **The subform is not logically accurate in appearance
    (should be KeywordOne KeywordOneRank KeywordTwo)

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tables (2002)

    Instead of having two keyword fields, and two separate lists of keywords, there is just one of each. To associate two keywords with a site you create two separate records in tblsiteKeywords.

    The rank is not attached to the keyword itself - instead it attaches to the use of a word for a site, so it is in tblSiteKeywords.

    The form for showing keywords draws its data from tblsitekeywords, which does not have the actual words, so the combo box is used, which brings in the actual words.
    Regards
    John



  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: tables (2002)

    I wouldn't use two separate keyword tables, but a single one.
    What is the purpose of the KeywordOneRank field?

    The subform frmSiteKeywordssub should be linked to the main form on SiteID (and this field should be in the record source of frmSiteKeywordssub)
    I would make frmSiteKeywordssub a continuous form, it will then look more like the other subform, and the controls will appear as in design view.

  13. #13
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tables (2002)

    *For each website, there is a list of keywords that I want to emphasize (higher-ranked keywords are usually more content-related, hence more valuable).
    **There is also a list of keywords that I may want to defer putting in the site, but I want to keep them on file. Or, they may be misspelled words.
    ***I have attached such keyword lists as examples for two sites: Ballscrews and Photoetching.

    ****I will always first have the lists composed in Excel, and then copy/paste into ACCESS tables. Then, when I need to look at them, they will appear in the form, or I can print them out in a report for the site.

    This is a very logical process in ACCESS to structure, and I appreciate your help, as you are strong in that area.

  14. #14
    Lounger
    Join Date
    Feb 2004
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tables (2002)

    I did some work on it.

    All ranks will be in currency format.

    Problems I have not been able to resolve are that: BadKeywords should be different for every site, but they show up the same in every site, on the sites form. Also, how can i make the subform keywords contain the information in the spreadsheet "photoetching ranked keywords". Don't I need a table that looks like that spreadsheet? Please help me.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: tables (2002)

    The idea behind having a Keywords table and a separate sitekeywords table is that keywords can be reused from one site to another without having to retype them, and to ensure that you always spell the same keyword the same way.

    Now it looks to me that your keywords are very site specific, so it might be better to have a simpler structure, where the keywords link directly with a site.
    To deal with good and bad keywords, I would have just a single list, but a yes/no field to indicate whether it is good or bad. The subforms are bound to queries that select just the good and bad keywords. This way you can move a word between the lists by ticking or unticking the box.

    If you want to paste in a list of words from Excel, you would need to add a siteid to the excel data to say what site the words belong to, and indicate that words are good or bad.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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