Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

relationships

Discussion in 'Information Technology' started by Ronnie, Nov 13, 2005.

  1. Ronnie

    Ronnie
    Expand Collapse
    Guest

    I am trying to set up many-to-many relationship with 4 tables. For some
    reason, each time I set the relationship from the Contact table to the
    Purchase Order table it appears correct , but when I close the relationship
    screen then go back, the relationship is deleted. Does anyone have any ideas
    of what I am doing wrong. The purchase orders are not appearing under the
    customers + table.
    --
    Ronnie
     
  2. Loading...

    Similar Threads Forum Date
    I'm open-minded about romantic relationships... how do I explain to family? Love & Marriage Aug 22, 2015
    Need advice...Sikhi in relationships Love & Marriage Nov 17, 2011
    Peer Pressure, Western Traits and Relationships Announcements Aug 23, 2010
    Peer pressure, western traits and relationships Sikh Youth Aug 23, 2010
    What is the Commitment in Relationships? Relationships Jan 24, 2010

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Your last statement "The purchase orders are not appearing under the
    customers + table." has me wondering what you think having a relationship
    will do for you. It does not cause data to be replicated in other tables.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Ronnie" <Ronnie@discussions.microsoft.com> wrote in message
    news:5A2CAC34-B839-48EB-8737-F6845A61AB92@microsoft.com...
    >I am trying to set up many-to-many relationship with 4 tables. For some
    > reason, each time I set the relationship from the Contact table to the
    > Purchase Order table it appears correct , but when I close the
    > relationship
    > screen then go back, the relationship is deleted. Does anyone have any
    > ideas
    > of what I am doing wrong. The purchase orders are not appearing under the
    > customers + table.
    > --
    > Ronnie
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Perhaps that is a reference to subdatasheets?

    ______________________________________________

    "Douglas J. Steele" wrote:

    Your last statement "The purchase orders are not appearing under the
    customers + table." has me wondering what you think having a relationship
    will do for you. It does not cause data to be replicated in other tables.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Ronnie" <Ronnie@discussions.microsoft.com> wrote in message
    news:5A2CAC34-B839-48EB-8737-F6845A61AB92@microsoft.com...
    >I am trying to set up many-to-many relationship with 4 tables. For some
    > reason, each time I set the relationship from the Contact table to the
    > Purchase Order table it appears correct , but when I close the
    > relationship
    > screen then go back, the relationship is deleted. Does anyone have any
    > ideas
    > of what I am doing wrong. The purchase orders are not appearing under the
    > customers + table.
    > --
    > Ronnie
     
  5. Ronnie

    Ronnie
    Expand Collapse
    Guest

    I am trying to set it up so that I can apply a purchase order to a customer.
    The purchase order should show each item that applies to that purchase order.
    There are 4 tables:
    Customer ID Table - ID, Name, Address, etc.
    Items Table - Item #, Product Name, Unit Price
    Purchase Order Table - Purchase Order No, Customer ID
    Purchase order Details Table - PO Details ID, PO No., Item #, Quantity
    I set up 2 queries:
    Order with Customer Info - Purchase Order No, Customer ID
    Order with Product Info - Item No., Product Name, Unit Price, Quantity

    I set up a form with a subform:
    Form - Customer Info with Purchase Order No.
    Subform - PO Details ID, Item #, Product Name, Unit Price, Quantity

    When I try to add a record to the form, this is the response: "You cannot
    add a record because a related record is required in the Purchase Order
    Table. I'm not sure what record to add.
    --
    Ronnie


    "Douglas J. Steele" wrote:

    > Your last statement "The purchase orders are not appearing under the
    > customers + table." has me wondering what you think having a relationship
    > will do for you. It does not cause data to be replicated in other tables.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Ronnie" <Ronnie@discussions.microsoft.com> wrote in message
    > news:5A2CAC34-B839-48EB-8737-F6845A61AB92@microsoft.com...
    > >I am trying to set up many-to-many relationship with 4 tables. For some
    > > reason, each time I set the relationship from the Contact table to the
    > > Purchase Order table it appears correct , but when I close the
    > > relationship
    > > screen then go back, the relationship is deleted. Does anyone have any
    > > ideas
    > > of what I am doing wrong. The purchase orders are not appearing under the
    > > customers + table.
    > > --
    > > Ronnie

    >
    >
    >
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Ronnie,

    Is this the same issue that you reported in your initial post? You wrote:

    "For some reason, each time I set the relationship from the Contact
    table to the Purchase Order table it appears correct , but when I close
    the relationship screen then go back, the relationship is deleted."

    It would be helpful if you identify the primary keys and the data types that
    you used for these key fields. For example:

    Table: Customer
    ID (autonumber, PK)

    Table: Items
    Item # (data type?, PK?)

    etc.

    It would also be helpful if you opened each query in design view, and then
    click on View > SQL View. Copy the SQL statements and paste them into a
    reply. That way, we can see exactly what your query looks like. Indicate the
    names of your form and subform, and the recordsource for each (should be an
    existing table or query).

    Have you checked out the sample Northwind database (Northwind.mdb)? You
    likely have a copy installed on your hard drive already. The error message:

    "You cannot add a record because a related record is required
    in the Purchase Order Table."

    indicates a referential integrity violation. In other words, you are
    attempting to add a record to a child table, without first having a matching
    record in the parent table.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Ronnie" wrote:

    I am trying to set it up so that I can apply a purchase order to a customer.
    The purchase order should show each item that applies to that purchase order.
    There are 4 tables:
    Customer ID Table - ID, Name, Address, etc.
    Items Table - Item #, Product Name, Unit Price
    Purchase Order Table - Purchase Order No, Customer ID
    Purchase order Details Table - PO Details ID, PO No., Item #, Quantity
    I set up 2 queries:
    Order with Customer Info - Purchase Order No, Customer ID
    Order with Product Info - Item No., Product Name, Unit Price, Quantity

    I set up a form with a subform:
    Form - Customer Info with Purchase Order No.
    Subform - PO Details ID, Item #, Product Name, Unit Price, Quantity

    When I try to add a record to the form, this is the response: "You cannot
    add a record because a related record is required in the Purchase Order
    Table. I'm not sure what record to add.
    --
    Ronnie
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    PS. I forgot to mention that your work with Access will be much easier in
    the future if you avoid using special characters (spaces, # sign, etc.) for
    anything that you assign a name to. You should also avoid using any reserved
    words, such as Name. Here are three KB articles that are worth printing out
    and keeping handy for future reference:

    Special characters that you must avoid when you work with Access databases
    http://support.microsoft.com/?id=826763

    Reserved Words in Microsoft Access
    http://support.microsoft.com/?id=286335

    List of reserved words in Jet 4.0
    http://support.microsoft.com/?id=321266

    I also recommend adopting a naming convention. Here's a few links on that
    topic:

    Commonly used naming conventions
    http://www.mvps.org/access/general/gen0012.htm
    http://www.xoc.net/standards/default.asp


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Tom Wickerath" wrote:

    Hi Ronnie,

    Is this the same issue that you reported in your initial post? You wrote:

    "For some reason, each time I set the relationship from the Contact
    table to the Purchase Order table it appears correct , but when I close
    the relationship screen then go back, the relationship is deleted."

    It would be helpful if you identify the primary keys and the data types that
    you used for these key fields. For example:

    Table: Customer
    ID (autonumber, PK)

    Table: Items
    Item # (data type?, PK?)

    etc.

    It would also be helpful if you opened each query in design view, and then
    click on View > SQL View. Copy the SQL statements and paste them into a
    reply. That way, we can see exactly what your query looks like. Indicate the
    names of your form and subform, and the recordsource for each (should be an
    existing table or query).

    Have you checked out the sample Northwind database (Northwind.mdb)? You
    likely have a copy installed on your hard drive already. The error message:

    "You cannot add a record because a related record is required
    in the Purchase Order Table."

    indicates a referential integrity violation. In other words, you are
    attempting to add a record to a child table, without first having a matching
    record in the parent table.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Ronnie" wrote:

    I am trying to set it up so that I can apply a purchase order to a customer.
    The purchase order should show each item that applies to that purchase order.
    There are 4 tables:
    Customer ID Table - ID, Name, Address, etc.
    Items Table - Item #, Product Name, Unit Price
    Purchase Order Table - Purchase Order No, Customer ID
    Purchase order Details Table - PO Details ID, PO No., Item #, Quantity
    I set up 2 queries:
    Order with Customer Info - Purchase Order No, Customer ID
    Order with Product Info - Item No., Product Name, Unit Price, Quantity

    I set up a form with a subform:
    Form - Customer Info with Purchase Order No.
    Subform - PO Details ID, Item #, Product Name, Unit Price, Quantity

    When I try to add a record to the form, this is the response: "You cannot
    add a record because a related record is required in the Purchase Order
    Table. I'm not sure what record to add.
    --
    Ronnie
     

Share This Page