hi. i have a Client table and a Stock table, and I need to record prices for the stock items. Each item has a different price for eah client (and vice versa). I've tried implementing it with a many-to-many relationship, putting the Price field in the joining table. This seems to be the way to do it, except that when I try to make the form to display the prices it won't work. I only want it to display clients/stock items that exist in the joining table (coz some items don't have any prices recorded, and some clients don't buy any stock items so therefore no prices attached to them). I can get it do this using the StkID and ClientID from the joining table, but then it shows a record for each instance of the client/stock item, rather than being able to display one record for a client, then listing all the items/prices in the subform. If I use the StkID and ClientID from their respective tables, then it only displays one record per client/item, but it displays ALL of them, regardless of whether they have an entry in the joining table...in short, what's the best way to do this. I've never used many-to-many relationships before and it's got me pulling my hair out!
I don't think you need a many-to-many joining table. At least it doesn't need to be defined in the relationships. Create your Parent form to display Customer information from your customers table only. Add a subform to display Customer, Stock#, CustPrice from a Customer Price Table . The query for the subform should show all customers/stock/price items. Then Use the Link Child, Link Master Fields in the subform properties to relate the subform to the Parent form using the CustID Fields. This will filter the subform to display only the records matching the CustomerID. You'll need a Customer Stock Items table for this that includes CustID, StockID, CustPrice fileds like you discribed. A combined Key index of CustID and StockID will prevent identical Cust/Stock IDs from being entered.
Just think of it as creating 2 separate forms/queries, use the Link Master/Child field properties to manage the form syncronization. I think that will solve your problem.
thanks for your help bill. The problem Im having doing it this way tho is that it will display EVERY client (and the prices attached to that client) when scrolling thru, even tho many of the clients don't (and never will) have prices attached to them, and I can't find a way to get access to exclude these clients (ie ones with no prices attached). But if i do it the other way (by reading the clientID from the prices table, since that will then only get clients who have prices attached), for each client, it displays a record for each time that client appears in the price table. As in, just say a client has prices listed for 3 different items, then when scrolling thru the records using this form, it will show the client name and the 3 prices, but then for the next record it will show the same again...and again a third time, once for each time the client appears in the price table. I can't figure out how to fix either problem!