Show selected records in subform

[Papilion]

Hi..



So, i have a tbl_Customer and tbl_Product which are related many to many..let's say that Customers always order same products but different quantities..i want to make an Order form where, when i choose Customer from combobox it will automatically list products in subform which that customer orders so i don't have to select individually every product, but just enter quantities..i suppose i would have to define which products each costumer orders..help..please..

Thanks...

[orange]

You have identified the Many to Many. You should design a Junction table, "CustomerProduct" to reduce the M:M to 1:M.
Customer --->CustomerProduct<-----Product

A Customer can buy Many Products.
A Product can be ought by Many Customers.

Do some research on Junction Tables.

http://www.foresightsoftware.com/Acc...tionTables.htm
http://www.eggheadcafe.com/microsoft...on-tables.aspx

[Papilion]

that i know how to do, but the main problem is that, for example, when i choose Customer1 from combo box in Master form(and, as i wrote, all customers order same products always but different quantities) i want to avoid having to select from combo box in Detail form all the products(Product1, Product 2,...etc.) which customers orders over and over again and just get the list od products where i must only type in quantities..does it make any sense..

[orange]

It makes some sense generally, but I'd like to see your tables and relationships.
A M:M between Customer and Product is not typical.
Do you understand normalization? Have you normalized your tables?

Here's some info on Normalization and Entity relationship Diagramming -- first few topics
http://www.rogersaccesslibrary.com/forum/topic238.html

If you had a form with a combo of Customers,
you could select a Customer, and a second combo of products and quantity could appear automatically based on your selection of Customer. If the Customer changed the Product or Quantity or Both, you could adjust.
Then click a button to Submit an Order etc.

Just a few thoughts.

[Papilion]

Oh sorry..i skipped that part, i thought the part with junction table is obvious..
here..i'll send you database so you can take a look..
so..look at the Order form..i chose Cost_1 which ordered Products:1, 2 and 3..
and Cost_2 which ordered Products:2 and 4..
this is what i want..let's say Cost_1 orders..since all Costumers always order same Products..i want that when Cost_1 orders that automatically lists Products 1, 2 and 3 and then i just enter quantities that i sold to them..so i don't want to enter every time Products by selecting them from combo box..makes sense??is it possible?
Attachment 3568

[orange]

I ahve acc2003 so can not open or use accdb. Can you save a copy as mdb?

[Papilion]

I ahve acc2003 so can not open or use accdb. Can you save a copy as mdb?

Attachment 3571

try this..

[orange]

I'm just looking at your database.

I don't see any info for CustomerId 2

Cost_2 which ordered Products:2 and 4..

A few thoughts about your tables and fields.

Do not use Lookups at the table level. (ProductId in OrderDetails).
Do not use a field named Date - it is a reserved word in Access and will come back to bite you.

What does SOLD represent in OrderDetails?

As far as the Customer's standard order, you could create something based on Last Order, or most frequent Products and quantities or something. But you'll have to determine what to do if this common order changes. How many customers do you have (anticipate to have) and will their order (products and quantity and price) always be the same???

I think you'd have a ProductName, ProductDescription, and maybe other fields in Product.

You may actually store the Price on the OrderDetail. Thius way you know what the price was when the Order was made. This would allow you to change Price without affecting existing Orders. There may be more involved but you'll have to determine it.

I think you need some way to adjust price from time to time. You may offer discounts? You may increase the price, so you'll have to figure a method for that -- unless your price for an product NEVER CHANGES????

I would create a table called CommonOrders:

table_name field_name ordinal_position data_type
CommonOrders CostumerId 0 Long
CommonOrders CommonOrderId 1 Long PK/automunber
CommonOrders ProductId 2 Long
CommonOrders Quantity 3 Long


UniqueIndex on CostumerId + ProductId + Quantity

and populate it with a query like this

INSERT INTO CommonOrders ( CostumerId, ProductId, Quantity )
SELECT Costumer.CostumerID, OrderDetail.ProductID, OrderDetail.Sold
FROM (Costumer INNER JOIN [Order] ON Costumer.CostumerID = Order.CostumerID) INNER JOIN OrderDetail ON Order.OrderID = OrderDetail.OrderID
GROUP BY Costumer.CostumerID, Order.OrderID, OrderDetail.ProductID, OrderDetail.Sold;

You could find the Customer's common order using

SELECT Costumer.Costumer, CommonOrders.ProductId, CommonOrders.Quantity
FROM CommonOrders INNER JOIN Costumer ON CommonOrders.CostumerId = Costumer.CostumerID;

When you place an Order for that Customer, you could use the CommonOrders to identify the products and quantity. But what if the commonOrder changes --- well you'll need a process to modify CommonOrders.

Anyway hope that is useful as a start.

[Papilion]

now this makes sense..
i'm new at this so i don' always get to see the wider picture of the problem but just wanting to do it the shortest way..
i'll give it a try your way and if i run into some problems i know who to ask for help..

thank you very much..