I'm sitting now so will try to explain my db intent. I don't know anyone who I can discuss db strategies with but I have talked with my staff about what would help through the shop. This made sense at the time ?!?
In general I'm trying to create a 1 stop resource to be able to look-up past items Ive either bought or have pricing for so when the time comes to order / re-order an item I don't have to start going through catalogs or websites (or worse, my last year's worth or paper invoices) to find who I purchased something from , what the cat. number is and how much I paid last time.
There are quite a few specialty cutting tools and materials I need to buy over time that aren't common everyday items. Someone may remember who we bought something from but no catalog info, or may have in hand the box from the old part with a number but no idea who supplied it. The other use would be if I'm quoting to my customer on a project which would need special tools or materials etc., I'd record the quotes from my suppliers but wouldn't necessarily be placing an order. Perhaps months later, that quote request comes back as an order from my customer and I'd need to look up and refresh my vendor quotes to see if any price adjustments needed to be made and who I got the best price quotes from back then.
Since I'd be gathering price quotes from several vendors, the quote form with details on the sub-form would be used. In most cases, the part or product is not available from only 1 vendor so I may get prices for the same item from 3 or 4 vendors. It seemed normalization rules would have me not enter the same product description twice so the vendor_ID on the main quotes form in tblQuotes and the product_ID (or ID's ) and quote price(s) would be in the sub-form in the tblQuote_Details. ( 1 vendor : many products and 1 product : many vendors )
When I am placing an order, I'd use the Orders form and select which vendor I was purchasing from. A list of items they had supplied price quotes for would be in the Order Details subform combobox.
When it comes to entering a new quote from a Vendor for a certain product, the entire list of all products likely needs to be viewable and that makes for a huge list to scroll through. I had thought of filtering the list by somehow tagging each vendor with all the categories they may be suppliers of, but that one got away on me. In most cases, raw material suppliers don't supply cutting tools or office supplies, and tooling suppliers don't sell office supplies or raw material but then you get the McMaster-Carr's of the world who stock everything and would be in almost all categories.
The text based search and filtering of items in the search form gets me to the product quickly, but then I have to go back to the quotes sub-form and try to remember the full description or part number to select it in the combobox.
It seems to me that there are other fields in the search form that are coming from additional tables that you might also need to send to the Quotes form.
I also realize now that the 2 search forms I have are named in reverse. I'd copied one and removed fields I didn't need but renamed the wrong form. The search form that will display from the quotes sub-form (frmSearch_Orders) is only displaying Description, Part Number and Category. The Description & Part Number are all that get brought into a new record in the quotes sub-form and both are available from Product_ID. I'd then enter the price I get from the vendor (acquired by phone, fax or email) and any catalog number they use for that item.
Thanks for sitting with me. Any suggestions or comments would be appreciated. I may have suffered tunnel vision trying to get something in place with this db and missed the turn-off that would have been a short-cut to a better way.