Alpha Video Training
Results 1 to 8 of 8

Thread: Set Design Problem

  1. #1
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,628

    Default Set Design Problem

    Trying to create a many to many set as follows:

    Names
    ==Weekdays
    ----Time

    Weekdays = sun,mon,tue, etc. for a total of seven static records in the table.

    The Time table has a "week_ending_date" field as well as a date field for the particular day of the week. The week_ending_date feld allows a query of records for any given week.

    Time has one record per Name per date, but not usually for all days of the week.

    The trouble is that weekdays is generic, whereas names & Time share a common ID field.

    The bottom line is that I need to print a weekly report that list all 7 days of the week for each name, and lists the corresponding time data for each day of the week.

    In other words the report output should look like this:

    John
    ==Sun--8 hours
    ==Mon--8 hours
    ==Tue--8 hours
    ==Wed--7 hours
    ==Thu--8 hours
    ==Fri--8 hours
    ==Sat-- (i.e. no Time record)
    ==Sun-- (i.e. no Time record)

    Sue
    ==Sun--6 hours
    ==Mon--8 hours
    ==Tue--8 hours
    ==Wed-- (i.e. no Time record)
    ==Thu--8 hours
    ==Fri--8 hours
    ==Sat-- (i.e. no Time record)
    ==Sun-- (i.e. no Time record)

    ETC, ETC.

    Does that make any sense? Is that doable somehow?

  2. #2
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,628

    Default RE: Set Design Problem

    Oops. I showed a duplicate Sun record above.

    John
    ==Mon--8 hours
    ==Tue--8 hours
    ==Wed--7 hours
    ==Thu--8 hours
    ==Fri--8 hours
    ==Sat-- (i.e. no Time record)
    ==Sun-- (i.e. no Time record)

    Sue
    ==Mon--8 hours
    ==Tue--8 hours
    ==Wed-- (i.e. no Time record)
    ==Thu--8 hours
    ==Fri--8 hours
    ==Sat-- (i.e. no Time record)
    ==Sun-- (i.e. no Time record)

    ETC, ETC.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default RE: Set Design Problem

    Peter,

    There's probably a more elegant solution, but the first thing that comes tomind here is that the intermediate table is full of nothing but constants (days of the week). I would examine whether such a structure is needed.

    For example, couldn't you do a two table set, with Names as parent table and Workdays as child, linked one to many on the employee idnumber. The Workdays table would contain both the date worked and the hours worked. The date worked could be used to break out the records on a week by week basis couldn't it?

    If you need to list all seven days of the week, even if no hours are worked on some days, this sounds like more of a report design problem, than a data entry design issue. Perhaps you could export the records for a week to an intermediate table. Then run a script to stuff zero hours worked entries in records for the missing workdays... then base your report on the intermediate table.

    Like I said, others may have more elegant solutions, but perhaps this will give you some ideas.

    -- tom

  4. #4
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,628

    Default RE: Set Design Problem

    >>Perhaps you could export the records for a week to an intermediate table. Then run a script to stuff zero hours worked entries in records for the missing workdays... then base your report on the intermediate table.

  5. #5
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,628

    Default RE: Set Design Problem

    Just thought of an easier way. Scrap the set design issue. I will pust labels on the report for the 7 days and use report calc fields to evaluate the DOW(date)=tbl.date(), and then print the corresponding field values. If there is no corresponding record to the day of week, I will still get printed values for my form, which is what I want.

    Whew!

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default RE: Set Design Problem

    Peter,

    You may find that determining

    ' If there is no corresponding record to the day of week '

    is hard to do. It's like finding something that isn't there. Let us know how it turns out.

    -- tom

  7. #7
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,628

    Default RE: Set Design Problem

    Tom,

    Actually, "my" method works, because it doesn't depend on the existance of actual records. I merely place labels as needed in a group footer, and I create corresponding summary calc fields to display the appropriate field values for when there are records. This method requires two calc fields. The first just captures the field value when the DOW evaluates to the corresponding day of week date, the second calc filed is a summary of the first calc field - usually using the "maximum" function. This last field is what is actually displayed in the group footer. This method is calc field intensive. In my case, for 7 days of the week, a total of 14 calc fields are required for each field you wish to display. But the report generator calcs those values instantaneously. Works very well.

    Peter

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,300

    Default RE: Set Design Problem

    Ahh! I see it now. Thanks. -- tom

Similar Threads

  1. Problem with posting Design
    By crimmelcp in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-05-2004, 12:35 PM
  2. Strange problem in Forms ? Design
    By Richard Nagle in forum Alpha Five Version 4
    Replies: 2
    Last Post: 09-08-2001, 04:22 PM
  3. Form Design Problem
    By davej in forum Alpha Five Version 4
    Replies: 23
    Last Post: 07-16-2001, 10:32 PM
  4. Ver 4.5 problem - Form/Report design
    By DonWalton in forum Alpha Five Version 4
    Replies: 1
    Last Post: 03-11-2001, 08:14 PM
  5. Report Design Problem
    By rfha in forum Alpha Five Version 4
    Replies: 5
    Last Post: 05-26-2000, 07:03 AM

Bookmarks

Posting Permissions

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