Relationship question...

Discussion in 'Microsoft Access' started by ChrisBat, Nov 11, 2003.

  1. ChrisBat

    ChrisBat Guest

    ...and no, I'm not referring to the communication style I have with my
    wife! :)
    I'm trying to build a relationship between three tables. The first has
    Billing Fields 1 - 10; a billing code can show up under any one of the
    10 fields, and may show up multiple times. As well, a billing code may
    or may not appear under a grouping title.
    My problem is that I'm trying to establish a link between the Billing
    Codes and the Billing Fields, and the Billing Groups with the Billing
    Codes.
    For example:

    Table 1
    Record 1: 889123 887123 886456 889001
    Record 2: 887124 889123 889124
    Record 3: 889003

    Table 2
    889123 | Replace Car muffler
    889124 | Replace Windshield
    887123 | Touch up scratch on paint
    887124 | Repaint portion of car
    887125 | Repaint entire car
    886456 | Repair transmission
    886457 | Repair engine block
    889001 | Clean upholstery
    889002 | Clean carpets
    889003 | Clean windows

    Table 3
    889___ | Replace car part
    887___ | Paint job on car
    886___ | Engine repair (partial or complete)
    88900_ | Clean interior and/or exterior of car

    What I'm looking to do is build a relationship that will enable me to
    translate the numbered billing codes to the Billing Description or to
    the Group name. The problem I'm having is that when I try and link
    Table 2 multiple times to Table 1, I get a message saying "A
    relationship already exists. Do you want to edit the existing
    relationship. To create a new relationship, click No."
    I click No, and I continue on with my relationship building. When I
    try and run a query or report based on these relationships, I get a
    blank report or query - I think it's because Access is looking for
    records that match ALL TEN criteria, not ANY or ALL of the criteria.
    Any suggestions? I appreciate any and all advice.
    Thanks,
    Chris



    ------------------------------------------------
    ~~ Message posted from http://www.MSAccessForum.com/
    ~~ View and post usenet messages directly from http://www.MSAccessForum.com/
     
    ChrisBat, Nov 11, 2003
    #1
    1. Advertisements

  2. ChrisBat

    Gary Miller Guest

    Well, you will never have any success with the table design
    you are using now. I strongly suggest you stop in your
    tracks here and rethink your table designs.

    Table 3, your task groups, is OK. Table 2 should have one
    more field to hold the 'key' field of Table 3...

    tblTasks
    TaskID GroupID Task
    889123 889 Replace Car muffler

    Now GroupID is your 'relationship' or Foreign Key between
    tblTasks and tblTaskGroups.

    Your main table is where your real problems lay. Your are
    storing tasks in 10 fields which will totally kill you when
    you go to try and report or summarize what has been done.
    Also, what happens when you get a big job that has 20 tasks?

    You should break this up into two tables so that you will
    have total flexibility on how many tasks are performed.

    tblJob
    JobID (Autonumber, Primary Key)
    CustomerID (LongInteger to store the ID from a Customer
    table)
    JobDate (Date/Time) ' Don't know if you want a start and end
    Date/Time???
    ' Any other fields you want that are specific to the job
    such as Employee

    tblJobTasks
    JobTaskID (Autonumber, Primary Key)
    TaskID (Foreign Key - Here is the relationship to the
    tblTasks)

    Now if you create a main form for the job, you would drop a
    subform on it to allow you to put in one record for each
    task performed, no more, no less.

    Also, I would suggest changing the task group codes from
    889___ to 889 so that you can keep them numeric if possible.
    --

    Gary Miller
    Gary Miller Computer Services
    Sisters, OR
    ________________________
    "ChrisBat" <> wrote
    in message news:...
    > ..and no, I'm not referring to the communication style I

    have with my
    > wife! :)
    > I'm trying to build a relationship between three tables.

    The first has
    > Billing Fields 1 - 10; a billing code can show up under

    any one of the
    > 10 fields, and may show up multiple times. As well, a

    billing code may
    > or may not appear under a grouping title.
    > My problem is that I'm trying to establish a link between

    the Billing
    > Codes and the Billing Fields, and the Billing Groups with

    the Billing
    > Codes.
    > For example:
    >
    > Table 1
    > Record 1: 889123 887123 886456 889001
    > Record 2: 887124 889123 889124
    > Record 3: 889003
    >
    > Table 2
    > 889123 | Replace Car muffler
    > 889124 | Replace Windshield
    > 887123 | Touch up scratch on paint
    > 887124 | Repaint portion of car
    > 887125 | Repaint entire car
    > 886456 | Repair transmission
    > 886457 | Repair engine block
    > 889001 | Clean upholstery
    > 889002 | Clean carpets
    > 889003 | Clean windows
    >
    > Table 3
    > 889___ | Replace car part
    > 887___ | Paint job on car
    > 886___ | Engine repair (partial or complete)
    > 88900_ | Clean interior and/or exterior of car
    >
    > What I'm looking to do is build a relationship that will

    enable me to
    > translate the numbered billing codes to the Billing

    Description or to
    > the Group name. The problem I'm having is that when I try

    and link
    > Table 2 multiple times to Table 1, I get a message saying

    "A
    > relationship already exists. Do you want to edit the

    existing
    > relationship. To create a new relationship, click No."
    > I click No, and I continue on with my relationship

    building. When I
    > try and run a query or report based on these

    relationships, I get a
    > blank report or query - I think it's because Access is

    looking for
    > records that match ALL TEN criteria, not ANY or ALL of the

    criteria.
    > Any suggestions? I appreciate any and all advice.
    > Thanks,
    > Chris
    >
    >
    >
    > ------------------------------------------------
    > ~~ Message posted from http://www.MSAccessForum.com/
    > ~~ View and post usenet messages directly from

    http://www.MSAccessForum.com/
    >
     
    Gary Miller, Nov 11, 2003
    #2
    1. Advertisements

  3. ChrisBat

    ChrisBat Guest

    Hi Gary,

    Thanks for your response.
    There are a few problems with your suggestion, given the situation I'
    in (which I did not explain in my original post).
    First - As far as the 10 fields go, this is not my doing. The databas
    I'm exporting the data from is a DOS-based program that was develope
    back in the mid-80's. I have been trying to get management to realis
    the impediments caused by this, but no one around here either know
    what I'm talking about or cares.
    1 letter of instruction can have anywhere from 0 to 10 billing edit
    tied to it. If there are going to be more than 10, we create a -
    ticket off the original serial number. Each serial number i
    completely unique, and will never be replicated (the first two digit
    are the year, e.g. 23 for this year, followed by the 5 digit sequentia
    number). There can be as many dash tickets as required in order to pa
    the contractors.
    Second - The groupings, again, are not my doing, but rather the manage
    who designed the databases. In the example above, I went too simple
    the first three digits are actually not in any way correlated with th
    Billing Edit. The billing edits have been set up to be sequential, s
    if we've added 15 codes this year, they will just fall into place
    regardless of what they are going to be used for. I apologise for th
    confusion - I didn't want to complicate my post, but ended up causin
    more problems...
    Is this a pain in the a**? Yes. There are a group of us here that ar
    trying to break through this problem for different analytical purposes
    and as I have registered with this site, I opted to post our problem.

    I appreciate your assistance.
    Chri


    -----------------------------------------------
    ~~ Message posted from http://www.MSAccessForum.com
    ~~ View and post usenet messages directly from http://www.MSAccessForum.com
     
    ChrisBat, Nov 11, 2003
    #3
  4. ChrisBat

    Gary Miller Guest

    Chris,

    Don't envy you here. On to your relationship problem, you
    say this is data that you are importing into Access that you
    are trying to do analysis on. You may never be able to
    create true relationships from what I see of the format. If
    this is not realtime data, how about creating a routine to
    'normalize' it from it's 'unnormalized' state?

    If you agree with the table structures that I posted earlier
    being a solution to the problem, I would consider writing a
    routine to loop through your main table and break it up into
    the two tables...
    *********
    tblJob
    JobID (Autonumber, Primary Key)
    CustomerID (LongInteger to store the ID from a Customer
    table)
    JobDate (Date/Time) ' Don't know if you want a start and end
    Date/Time???
    ' Any other fields you want that are specific to the job
    such as Employee

    tblJobTasks
    JobTaskID (Autonumber, Primary Key)
    TaskID (Foreign Key - Here is the relationship to the
    tblTasks)
    *******

    .... that I referred to earlier. You could pull the JobID,
    date etc from the table and poke it into tblJob and then
    check fields 1 - 10 to see if they have data and if so
    create a new record for tblTasks with the Task# and the
    JobID. Ex:

    Your data as is...
    Record 1: 889123 887123 886456 889001

    would populate the second table
    Record 1 889123
    Record 1 887123
    Record 1 886456
    Record 1 889001
    Record 2 887123
    ...etc...

    Write the code once and you can just reprocess again any
    time you reimport. Now you can do your reporting and
    analysis on something that you can get your teeth into.

    --

    Gary Miller
    Gary Miller Computer Services
    Sisters, OR
    ________________________
    "ChrisBat" <> wrote
    in message news:...
    > Hi Gary,
    >
    > Thanks for your response.
    > There are a few problems with your suggestion, given the

    situation I'm
    > in (which I did not explain in my original post).
    > First - As far as the 10 fields go, this is not my doing.

    The database
    > I'm exporting the data from is a DOS-based program that

    was developed
    > back in the mid-80's. I have been trying to get

    management to realise
    > the impediments caused by this, but no one around here

    either knows
    > what I'm talking about or cares.
    > 1 letter of instruction can have anywhere from 0 to 10

    billing edits
    > tied to it. If there are going to be more than 10, we

    create a -1
    > ticket off the original serial number. Each serial number

    is
    > completely unique, and will never be replicated (the first

    two digits
    > are the year, e.g. 23 for this year, followed by the 5

    digit sequential
    > number). There can be as many dash tickets as required in

    order to pay
    > the contractors.
    > Second - The groupings, again, are not my doing, but

    rather the manager
    > who designed the databases. In the example above, I went

    too simple -
    > the first three digits are actually not in any way

    correlated with the
    > Billing Edit. The billing edits have been set up to be

    sequential, so
    > if we've added 15 codes this year, they will just fall

    into place,
    > regardless of what they are going to be used for. I

    apologise for the
    > confusion - I didn't want to complicate my post, but ended

    up causing
    > more problems...
    > Is this a pain in the a**? Yes. There are a group of us

    here that are
    > trying to break through this problem for different

    analytical purposes,
    > and as I have registered with this site, I opted to post

    our problem.
    >
    > I appreciate your assistance.
    > Chris
    >
    >
    >
    > ------------------------------------------------
    > ~~ Message posted from http://www.MSAccessForum.com/
    > ~~ View and post usenet messages directly from

    http://www.MSAccessForum.com/
    >
     
    Gary Miller, Nov 11, 2003
    #4
  5. The simple fact is that you CANNOT create relationships in the way that you
    want to. Having multiple columns that store the same type of data violates
    the most fundamental rule of relational database design--the First Normal
    form. If you won't adhere to that, don't be surprised if your database
    can't do things that a relational database should, because it isn't.

    Gary is correct and his solution (or something very like it) is the only one
    available. OK, so you don't have the ability to fix the source, but you CAN
    import this non-normalized data into a normalized structure.

    Just because something has always been that way does not mean it should
    always stay that way.

    --
    --Roger Carlson
    www.rogersaccesslibrary.com
    Reply to: Roger dot Carlson at Spectrum-Health dot Org

    "ChrisBat" <> wrote in message
    news:...
    > Hi Gary,
    >
    > Thanks for your response.
    > There are a few problems with your suggestion, given the situation I'm
    > in (which I did not explain in my original post).
    > First - As far as the 10 fields go, this is not my doing. The database
    > I'm exporting the data from is a DOS-based program that was developed
    > back in the mid-80's. I have been trying to get management to realise
    > the impediments caused by this, but no one around here either knows
    > what I'm talking about or cares.
    > 1 letter of instruction can have anywhere from 0 to 10 billing edits
    > tied to it. If there are going to be more than 10, we create a -1
    > ticket off the original serial number. Each serial number is
    > completely unique, and will never be replicated (the first two digits
    > are the year, e.g. 23 for this year, followed by the 5 digit sequential
    > number). There can be as many dash tickets as required in order to pay
    > the contractors.
    > Second - The groupings, again, are not my doing, but rather the manager
    > who designed the databases. In the example above, I went too simple -
    > the first three digits are actually not in any way correlated with the
    > Billing Edit. The billing edits have been set up to be sequential, so
    > if we've added 15 codes this year, they will just fall into place,
    > regardless of what they are going to be used for. I apologise for the
    > confusion - I didn't want to complicate my post, but ended up causing
    > more problems...
    > Is this a pain in the a**? Yes. There are a group of us here that are
    > trying to break through this problem for different analytical purposes,
    > and as I have registered with this site, I opted to post our problem.
    >
    > I appreciate your assistance.
    > Chris
    >
    >
    >
    > ------------------------------------------------
    > ~~ Message posted from http://www.MSAccessForum.com/
    > ~~ View and post usenet messages directly from

    http://www.MSAccessForum.com/
    >
     
    Roger Carlson, Nov 11, 2003
    #5
  6. ChrisBat

    ChrisBat Guest

    Thank you Gentleman.
    It's 12 minutes till hometime for me - I'll pick this up tomorrow.
    And no, I don't envy me either! :)
    Appreciate all your help.
    Chri


    -----------------------------------------------
    ~~ Message posted from http://www.MSAccessForum.com
    ~~ View and post usenet messages directly from http://www.MSAccessForum.com
     
    ChrisBat, Nov 11, 2003
    #6
  7. While you can't make the relationships, you can MOST certainly make a query,
    and drop in the table 10 times, and use left joins. So, you can have each of
    the 10 fields return values from the other table.

    This will at least give you lookups to the other tables.

    Note the best, but the query builder will do this form you, and then you can
    use this for reports.

    So, fire up the query builder, and start dropping in the table 2. Draw a
    join line from field to the table 2 (double click on the join line, and
    select the option that says join on any table1, but not necessary a value
    in table two). You can continue this process for 10 times, and thus you will
    have a query that lets you display the other values. (you will have to
    repeatedly drop in table 2 for each field).

    So, you can't define relationships to do this, but you can certany build
    querys that pulls the data togther for you.

    --
    Albert D. Kallal (MVP)
    Edmonton, Alberta Canada

    http://www.attcanada.net/~kallal.msn
     
    Albert D. Kallal, Nov 12, 2003
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Aardvark

    Relationship silly Question

    Aardvark, Mar 20, 2004, in forum: Microsoft Access
    Replies:
    5
    Views:
    182
    Aardvark
    Mar 20, 2004
  2. Veli Izzet

    Newbie relationship question (rather long)

    Veli Izzet, Jun 21, 2005, in forum: Microsoft Access
    Replies:
    6
    Views:
    170
    Veli Izzet
    Jun 21, 2005
  3. Guest

    Entity Relationship Question

    Guest, Jul 3, 2005, in forum: Microsoft Access
    Replies:
    3
    Views:
    338
    Graham R Seach
    Jul 4, 2005
  4. Guest

    Relationship Question

    Guest, Jul 27, 2005, in forum: Microsoft Access
    Replies:
    1
    Views:
    172
    John Vinson
    Jul 27, 2005
  5. Guest
    Replies:
    1
    Views:
    355
    Guest
    Oct 3, 2005
Loading...

Share This Page