relationships

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

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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

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/ex...tributors.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

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/ex...tributors.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/ex...tributors.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