Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    6

    Unanswered: Creating new entries using macro's & query's

    Hi all I'm not entirely sure if what I'm about to describe is possible. I have an input Table with most importantly: skills, dates, and hours as fields. Basically for a certain skill a given number of hours are needed for each month. However, in the current table not all months have hour entries for all skills. What I would like to do is create records for all date/skill combo's with hour value 0 if there is no record that exists.

    So:

    Skills......Dates......Hours
    -----entered data-----
    ...............................

    //append this onto it
    --remaining combos--0
    .............................0
    ...

    My thoughts for how to go about this is some sort of query against the Master Tables of Skills (and possibly a master Calendar Table). For all records that are unmatched I will need to create a new one in the Labor table w/ value 0 for hours. I am not sure whether I am on the right track for this; if there is a simpler or better way to go about it. I also am not sure how to go about querying for all possible combinations of Skill/Month or creating records with the given value 0 for hours.

    Thoughts?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like a flaky design to me.

    skill types is a table
    persons is a table
    then you need a thrid table to register wahtever things someone does, and waht skills credits that earns.. look up intersection tables
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2012
    Posts
    6
    Ok I don't think I explained properly. Let me go into a little more detail here.

    I currently have a form with a number of tables but the relevant one to my question is a Labor table that contains a Project, Hours, Skills, and Date Needed. Project and Skills reference other tables. There are other fields within this table but I'll disregard them as they are not relevant to the form I am looking to create. The setup of the form is as follows:

    Project (the user can select a project and the information below updates to relate to the selected project)

    Current Month Current Month +1 Current Month+2 ...
    Skill1 hours hours hours ...
    Skill2 hours hours hours ...
    Skill3 hours hours hours ...
    ... .... ... ... ...


    To create this format I have queries that query the Labor table. They query for the values of the current month and the next query collects the records of the next month and then the next month and so on. It lists these by alphabetically by skill. Therefore all of the queries will return the skills in the same order. That being said to maintain the matrix format in my form I want there to be records for all of the skills for each month for 12 months out from the current month. Or else some skills/hours will not be in some of the queries and they will not correspond to the skill order of the other queries. I want this for readability. So if there is not a record I want to create one with a value of 0.

    I already have a table of skills. My question has nothing to do with persons. I simply am looking at hours needed for each skill for each month.

    I currently have a normalized design and do not want to have to create temporary tables/denormalize it/ renormalize it.. to create the edittable matrix format that I am looking for. That being said is there a way to create test where I need to create a record and how do I do this?

    Many thanks

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    By the design you mentioned (Project, Hours, Skills, and Date Needed) it looks like you have a set of skills that are required for a project, an estimate of how many hours of that skill are needed, and a date when that skilled task needs to be done.

    From this I would expect the hours by month to be a result of the total hours needed divided by the number of months available. This wouldn't leave holes in your matrix though... so, how are the hours needed for each month being calculated?

    Steve

  5. #5
    Join Date
    Jun 2012
    Posts
    6
    So for a skill there are # many hours needed for a given month. It may not be the same every month. It may vary how many are needed depending on the month. The hours per month are inputs by the users and not calculated within the db.

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    You are better off keeping the data storage separate from the display and if any months are missing then handle that in your matrix query.

    Assuming you have two tables:
    Skill (skillID, skillName)
    Labor (laborID, skillID, Hours, Month)

    You could start with a query for each month that checks for null values on hours and replaces them with 0.

    Code:
    SELECT labor.skillid, nz([hours],0) AS JulyHours
    FROM labor
    WHERE labor.month = "July";
    After that you can do a left join between the skill table and the three queries by month and your matrix will be filled in with all the values. Then you can make the date range dynamic so you don't have to create new queries every month.

    Steve

Posting Permissions

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