Results 1 to 4 of 4

Thread: Two tables (97)

  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two tables (97)

    Hi,
    I have two tables called Alpha and Beta.

    Alpha contains the fields Team, Names, and 11/01/03 all the way to 15/01/03, in the dates fields you can have one of 3 different letters E=Early L=late and AL=Leave, these correspond to what an individual person is doing on that particular date.

    Beta Contains the fields Team,11/01/03 all the way to 15/01/03, in the dates fields you have different SECTORS where a person is working on that particular date eg Shop, Planning, Costing etc...

    What id like to do is create a table which i can print out which has the following info

    Fields that contain the sector name eg Shop cost etc... and under each sector column you have who is working in that sector. and another field called team stating what team the individual is in.

    Id be very greatful if anybody could help me out with this one as im a rookie when it comes to access.
    Cheers,
    Rob.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Two tables (97)

    Hungry,

    You really need to revisit your database design. You should not have those separate date fields in your tables. You should be using a child table that allows you to enter 1 row per date. For example, an Alpha_Dates table would have these 3 fields:
    Team, ActionDate, Action. You would create a one-to-many relationship between Alpha and Alpha_Dates based on the Team field in each.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Two tables (97)

    In the first place, I would organize the data in the tables differently, if possible. I wouldn't have separate columns (fields) for the dates, but one column for the date and one for the activity (or sector or ...), and a separate record for each day for each person. For example, the table Alpha could look like this:

    <table border=1><td align=center>Team</td><td align=center>Name</td><td align=center>Date</td><td align=center>Activity</td><td>A</td><td>John</td><td align=right>11/01/03</td><td>E</td><td>A</td><td>John</td><td align=right>12/01/03</td><td>L</td><td>A</td><td>John</td><td align=right>13/01/03</td><td>E</td><td>A</td><td>John</td><td align=right>14/01/03</td><td>L</td><td>A</td><td>John</td><td align=right>15/01/03</td><td>AL</td><td>A</td><td>Mary</td><td align=right>11/01/03</td><td>L</td><td>A</td><td>Mary</td><td align=right>12/01/03</td><td>AL</td><td>A</td><td>Mary</td><td align=right>13/01/03</td><td>E</td><td>A</td><td>Mary</td><td align=right>14/01/03</td><td>E</td><td>A</td><td>Mary</td><td align=right>15/01/03</td><td>E</td></table>
    In the second place, you want to create a new table; I would create a query instead. But it is not clear to me what the result should look like. Should the date feature in it, i.e. John works in sector Planning on 11/01, and in sector Costing on 12/01 etc.?

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two tables (97)

    Unfortunatley table alpha is given to me in this format and would take me ages to change it to the way you were suggesting.

    Team Names 11/01/03 12/01/03 13/01/03 14/01/03 15/01/03
    T1 J.Jones E E L E L
    T1 S.Ray E E L E L
    T1 T.Mullen E E L E L
    T2 H.Trent E E L E E
    T2 O.Fizt E E L E L
    T2 T.Lynn E E E E L
    T3 B.Kay E E E E L
    T3 R. Mc Coy L AL E L L
    T3 C. Raffery L AL E L AL

    Just for completeness table beta looks like this

    Team 11/01/03 12/01/03 13/01/03 14/01/03 15/01/03
    T1 Planning Costing Planning Shop Planning
    T1 Planning Shop Costing Shop Planning
    T1 Planning Shop Costing Shop Planning
    T2 Planning Shop Costing Shop Planning
    T2 Planning Shop Costing Shop Planning
    T2 Planning Shop Costing Shop Planning
    T3 Planning Shop Costing Costing Planning
    T3 Shop Shop Costing Costing Planning
    T3 Costing Shop Costing Costing Shop


    The ideal this would be if i could create an icon which would open up access and bring me to a screen where i can load these two files. Once loaded a button could be clicked to create the query. Ideally id like the finished result to look like this.

    Planning Shop Costing
    T1 J.Jones S.Ray
    T2 H.Trent
    T1 T.Mullen
    T3 B.Kay

    Any help will be appreciated,
    Cheers,
    Rob.

Posting Permissions

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