Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38

    Unanswered: advice on Tables link

    Hi i have the following tables (check attached document) and how the tables work is:
    A tender(table) is placed(very much like an order) and all the items that are included in the tender are in the tenderlink table. if the tender needs to be edited. it is done via the change (table) and then any item that is edited is done on the changelink(table). this is linked to the tenderlink table. therefore anything in the tenderlink table can be selected using a drop down box on the changlink(table)

    the problem that i have now is that orders may need to be added to the current order and to do this they need to go through the change table. so it can be recorded.

    however i am having problems here as my table (tenderlink)does not allow me to make adittional order items via the change (table)

    does anyone have any suggestion that might help

    many thanks
    Attached Thumbnails Attached Thumbnails e-r diagram.jpg  
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You have a circular reference going on. Break it, and create a second instance of your tender table to link to the second table ...

    Regards

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    advice on Tables link

    Originally posted by namliam
    You have a circular reference going on. Break it, and create a second instance of your tender table to link to the second table ...

    Regards
    Namliam,

    Hey, just for the sake of duplicating this problem in the future, what is the Circular Reference? Where is it exactly and how do you break it and fix it? I think I have done this in the past somehow and fixed it accidentally without knowing how I did it as I try so many things at a time when in trouble. Please help educate a future database programmer.

    out of my diapers now....
    Bud

  4. #4
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    Hi

    many thanks for reply but what does Circular Reference mean?

    regards
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  5. #5
    Join Date
    Feb 2004
    Posts
    199
    There is no Relation problems, maybe structure and task decision isn't good, but anyway, you can add rows in your tables, probably you have problem related with filling all fields that are related with other tables with PK-s from that tables. what the error message you get trying to add a row.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  6. #6
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    Hi in relations to the question i have since now added another table(check attachment) and i have now done a append query but i am having problems with this now. as my append query does not allow me to save the changes.

    I think it is because of the PK

    thanks
    Last edited by kabir_hussein; 02-23-04 at 06:37.
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  7. #7
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    here is e-r diagram

    many thanks
    Attached Thumbnails Attached Thumbnails er.jpg  
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  8. #8
    Join Date
    Feb 2004
    Posts
    199
    so, your proble is wrong data in some FK field, check your action query, be shure that all FK data is in refernced table's PK, this is no due relationship you designed in wrong or correct way, this is due to relationship & wrond data in a field same time, that means you can solve your problem without changing your relationship, just fill all fields correctly!
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  9. #9
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    here is the SQL for my append query

    INSERT INTO tenderlink ( TenderlinkID, PartID, SupplierID, [Lead Week], [Basis Of Supply], [Tender Quantity], [Tender Ref], [Tender Unit Price], [Target Unit Price], [Tender Cost], [Cross Reference to Cleint Spec], New, TenderID )
    SELECT tenderNew.tenderlinkid, tenderNew.PartID, tenderNew.SupplierID, tenderNew.[Lead Week], tenderNew.[Basis Of Supply], tenderNew.[Tender Quantity], tenderNew.[Tender Ref], tenderNew.[Tender Unit Price], tenderNew.[Target Unit Price], tenderNew.[Tender Cost], tenderNew.[Cross Reference to Cleint Spec], tenderNew.New, tenderNew.tenderId
    FROM tenderlink INNER JOIN tenderNew ON tenderlink.TenderlinkID = tenderNew.tenderlinkid;

    this is the error message i get when i click on append query
    Attached Thumbnails Attached Thumbnails errormessage.jpg  
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  10. #10
    Join Date
    Feb 2004
    Posts
    199
    hey, what are you doing
    look, you're trying to fill tenderlink.TenderID field with tenderNew.tenderId BUT!, this field is related to another tables PK, be carefull! are you understand the quey you use?, I can't help you cose I don't know your whole task, first of all try to get some little knowledge before start designig DB structure and queryes.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  11. #11
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    HIYA

    thanks for the message but i no what im doing ;-)
    however due to the requirements of the database i can not think of a way of doing this,

    what i am trying to do is. the tenderlink is like an order and if a order is changed it needs to be recorded and saved. this is done by the changelink table. however i have only been told that the database must also allow new items to be ordered and the changes be saved.

    so to do this i thought id make another table and then append the table back to the main table. however i did not think of the PK clashing hence i have asked for some help if there is a way i can make the tenderNew not interfere with the tenderlink Pk. And let the append query save to the tenderlink table

    Do you understand what i mean now


    many thanks

    kabir
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

Posting Permissions

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