Question one-to-all relationship

[Banana]

Hmm, okay I'm confused.

Earlier, you indicated that all customers get all products, but now you say products are 'personalized' for each customers. What is meant by 'personalize'? If we're talking about customer selecting a subtype, color or accessories or whatever, then that's just a normal one-many relationship...

 

[pavlos]

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

If i understood your question well, a shown record that relates a customer (from table A) with all products (table B) will display extra calculated information next to each product.

many thanks!

 

[pavlos]

Hmm, okay I'm confused.

Earlier, you indicated that all customers get all products, but now you say products are 'personalized' for each customers. What is meant by 'personalize'? If we're talking about customer selecting a subtype, color or accessories or whatever, then that's just a normal one-many relationship...

why confused? it's simple, each customer (old or new) gets all products in table B. Based on this relation other details are calculated...

 

[pavlos]

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.

Before posting to this forum I spent more than two days looking at the problem. I thought to create a relationship one-to-all it would be easy and just could not see it...
The database is already working and this is an extra adding I was asked to make. A tabbed form with two pages, page 1 shows clients details (name, address, etc) and page 2 shows all products (which are same for all clients) plus other details that are calculated based on each client's profile.

many thanks for trying to help!

 

[CraigDolphin]

...page 2 shows all products (which are same for all clients) plus other details that are calculated based on each client's profile.

many thanks for trying to help!

Then page 2 should be based solely on a query of the products table, with additional calculated fields for the other 'details', using parameters taken from the current client details on page 1 as inputs for your calculation.

 

[Niroth]

I think CraigDophin is right, it's how I would do it too. Pavlos, unless there's something complicated about the "calculation based on each client's profile" you alluded to, you shouldn't need a relationship because there's no relationship between them that I can see. If there's a field in your customer table that tells the product table what it should show, i.e. the products are "personalised" for each customer, then you can include that field in the product table and form a relationship base on that or use it as a query criteria (which I imagine would be more appropriate after reading what you have described).

 

[engineer]

I am so sorry for my wrong answer
when I wrote it , I was n't try it.
but I understand your idea very good
but I can't know the solution
sorry for interruption & confusing

 

[pavlos]

Then page 2 should be based solely on a query of the products table, with additional calculated fields for the other 'details', using parameters taken from the current client details on page 1 as inputs for your calculation.

Many thanks CraigDolphin! well I managed to make the one-to-all relationship by creating a query as follows: from clients table I added only the primary_key field and from products table I added * field (all fields). No need to join the tables in the query.

Then in the relationships I related the table clients and the query by joining the two primary_key fields.

Now opening the clients table i can see all products related to every customer!

HOWEVER, the next problem i come across is that, how can i insert the product table fields in my 2nd page of the form when all fields in the 1st page link to another query?

 

[pavlos]

I am so sorry for my wrong answer
when I wrote it , I was n't try it.
but I understand your idea very good
but I can't know the solution
sorry for interruption & confusing

No problem! every opinion counts!!!

 

[engineer]

I managed to make the one-to-all relationship by creating a query as follows: from clients table I added only the primary_key field and from products table I added * field (all fields). No need to join the tables in the query.

Then in the relationships I related the table clients and the query by joining the two primary_key fields.

Now opening the clients table i can see all products related to every customer!

I tried but not success
when I make a relation between table and query , not appear the type of relationship
it was not active for choosing