Question one-to-all relationship

[pavlos]

Hi all,

I know the title of this thread is not correct but it accurately describes my problem: how can I create a relationship between two tables (A and B) where each line from table A links(relates) with ALL lines of B. I tried different ways using one-to-many relationships without success.

Regards,
pavlos

 

[HiTechCoach]

Would you please explain in more detail what you are trying to represent with your table design.

 

[pavlos]

Table A is a list with more than 500 customers and has 20 columns, table B is a list with 10 products and has 4 columns. I would like to related all products from table B with each record (customer) from table A. When a new customer added then automatically must be related with all products from table B.

--------------------------- 1st record of A
|
----------------
| Table B |
----------------

--------------------------- 2st record of A
|
----------------
| Table B |
----------------


---------------------------------- n record of A
.
.
.

 

[engineer]

why ??????
you can make combo box for any field and add all products on it .
why you want all table appear in the another table??

you should appear the products which each customer is purchased and that's good and is one to many relationship

 

[Rabbie]

I think you need to to read up on data normalisation. Data should not be stored in more than one place in a relational database like Access

 

[pavlos]

why ??????
you can make combo box for any field and add all products on it .
why you want all table appear in the another table??

you should appear the products which each customer is purchased and that's good and is one to many relationship

I developed my date base using the ms access template "contact management database".

When I launch the data base immediately opens a tabbed form with 5 pages. First page displays details of the chosen customer (name, address, tel, etc), the second page displays ALL products from table B plus other information (which is calculations) that are based on the profile of the chosen customer.

Therefore I need all records (all columns and lines from table B) to be related to each customer in table A. To make it clearer, customers do not choose which product but since they put their name in the list automatically all products are given to them but with different characteristics based on each customer profile.

Many thanks for assisting to solve the problem!

Best regards,
pavlos

 

[pavlos]

I think you need to to read up on data normalisation. Data should not be stored in more than one place in a relational database like Access

Dear Rabbie, i must have stored all products in a different table.

 

[engineer]

1- add new field in customers table and make it primary key
if you have previous primary key , you will make two fields primary key
2- add the same field in products table and don't make it primary
3- make relation one to many between them and you will display all products table in the customers

I wait your reply

 

[pavlos]

Dear Engineer,

I followed your directions, it does not work. Actually it produces an indeterminate relation.... Did you try it and worked?

 

[HiTechCoach]

Table A is a list with more than 500 customers and has 20 columns, table B is a list with 10 products and has 4 columns. I would like to related all products from table B with each record (customer) from table A. When a new customer added then automatically must be related with all products from table B.

--------------------------- 1st record of A
|
----------------
| Table B |
----------------

--------------------------- 2st record of A
|
----------------
| Table B |
----------------


---------------------------------- n record of A
.
.
.

Since no one else's suggestions are meeting your needs, I think what you want is to define a relationship between the customers and the products.

I would do this by creating a third "junction" table. This will ally you to create the Many-to-many relationship you desire.

The new table will look something like:

Table: CustomerProducts
Customer_ID - foreign key to the customer table
Product_Id - foreign key to the product table
- here you would include fields that define the what is unique for this customer and product combination.

 

[Banana]

Dear Engineer,

I followed your directions, it does not work. Actually it produces an indeterminate relation.... Did you try it and worked?

If you get 'indeterminate relation' then the setup is not correct. Usually we may relate table A's *autonumber* primary key to table B's *Number* column (not a primary key) to create a one-many relationship.

I'm not sure why you believe you need a "one-all relationship"; normally, we create records when we need it and not ahead of time, so if one customer orders a product, we make the appropriate record in the order table at that time of order and not earlier.

I'd concur with Rabbie's advice- read up on normalization first. Designing is going to get more difficult if it's not normalized to start with.

 

[pavlos]

Since no one else's suggestions are meeting your needs, I think what you want is to define a relationship between the customers and the products.

I would do this by creating a third "junction" table. This will ally you to create the Many-to-many relationship you desire.

The new table will look something like:

Table: CustomerProducts
Customer_ID - foreign key to the customer table
Product_Id - foreign key to the product table
- here you would include fields that define the what is unique for this customer and product combination.

Hi Boyd, tried what you suggest and it does not work... I am getting really concerned with this problem!

 

[pavlos]

If you get 'indeterminate relation' then the setup is not correct. Usually we may relate table A's *autonumber* primary key to table B's *Number* column (not a primary key) to create a one-many relationship.

I'm not sure why you believe you need a "one-all relationship"; normally, we create records when we need it and not ahead of time, so if one customer orders a product, we make the appropriate record in the order table at that time of order and not earlier.

I'd concur with Rabbie's advice- read up on normalization first. Designing is going to get more difficult if it's not normalized to start with.

Relating table B (products) with each customer is what we must have. Customers do not choose the products but they get all of them once they get registered! I wonder if someone else came acroos to the same problem...

 

[Banana]

Let me ask this to make sure...

Will your customers *always* get one of everything in your products table?
Will your customers whose has already registered get new products that get added?
Will your products *ever* change?

 

[CraigDolphin]

It sounds to me like your design needs normalizing, and that what you need to have is a junction table, and you may need to use a little vba/sql to automatically populate the junction table when you add a new person, if you want to construct a database that functions in the way you envisage.

However, I strongly urge you to first: read up on normalization. There are many tutorials on the topic and, unless you take this step to help yourself, you'll find most of the people who have the expertise to help you will be unwilling to do so.

Secondly: rather than arrive with a predetermined idea as to how your database should work, try explaining what the database is supposed to do (generally) and explain as much as you can of the real world data structure you're wrestling with. This approach allows us to better help you come up with a design that works best in your situation.

For example: are you trying to show product quantities shipped to a person in this other tab? If so, you should be storing information about those shipping events in a transactions table, and using a query to show the total shipped for all products based on an outer join between the products table and the transaction table.

 

[Rabbie]

Relating table B (products) with each customer is what we must have. Customers do not choose the products but they get all of them once they get registered! I wonder if someone else came acroos to the same problem...

Just to clarify what you are saying is that as soon as you register a client they then get all the products. Can you confirm this is the case and why you want this to happen.

 

[pavlos]

Let me ask this to make sure...

Will your customers *always* get one of everything in your products table?
Will your customers whose has already registered get new products that get added?
Will your products *ever* change?

YES, actually always get all the products.
YES, when a product is added or removed from table B that chenge reflects to all customers.
YES, they do change.

Many thanks for trying to help!

 

[Banana]

If that is the case...

Then you don't need a relationship at all.

Just store your customers in customers table, your products in product tables and when you need to report, just do a report listing all customers with a subreports listing all products.

 

[HiTechCoach]

Relating table B (products) with each customer is what we must have. Customers do not choose the products but they get all of them once they get registered! I wonder if someone else came acroos to the same problem...

Once you load a record for each combination of customer/product, what is the issue? Have you loaded all the record into the table?

In what was does it not work for you?

I am starting to agree with the others. I think you may be making this overly difficult. You probably do not need to define a relationship.

You may what to look at the template: Order entry

 

[pavlos]

Just to clarify what you are saying is that as soon as you register a client they then get all the products. Can you confirm this is the case and why you want this to happen.

Dear Rabbie,

Correct, this is the case as soon as a client gets register it gets all the products. Based on this relation we can personalize products for each client. And as I have written all these info are displayed in a tabbed form, 1st page shows the clients profile and 2nd page shows the products personalized on clients specifications. Show by choosing the client’s name one can go from page 1 to page 2 and have a full picture.

Many thanks!