Table design flaw? Query won't search

I have started a Thread in queries, but it has been mentioned that the query looks good and it may be a design flaw. I have link the other thread (best I can) below... I can't I haven't posted enough uhm - add website to front? NOPE. here is the title then.. maybe now?

Like "*" & [What Item] & "*" - doesn't work?

Table set up
Company : TableItem : TablePrice : TableThe PRICE:TABLE pulls the COMPANY:FIELD from COMPANY:TABLES-FIELD:ID and FIELD:COMPANY

The PRICE:TABLE pulls the ITEM:FIELD from ITEM:TABLE-FIELD:ID and FIELDESCRIPTION

The purpose of the query is to search in the PRICE:TABLE and have the related information displayed.

For example:

EX1
Want to know where to get a "blue towel" but can not remember the color? Search under ITEM for "towel" the query will then return all entries with "towel" and all related information - which company has it, what price it is and what is the part number.

EX2
Need to know what company offers what? Search for "Bob's cloth" under COMPANY and the query will return all items,price and part numbers that match "Bob's Product's"

I hope that made sense. I am almost sure no that I did something wrong in defining the relationship's. When I do search for a number I get results. I think the query is searching the ID fields in the related tables (COMPANY:TABLE and ITEM:TABLE) but I am unable to get it to point elsewhere and match all the fields up to give a whole picture of the product.

Company ---->CompanyItemsPartnumberProduct<----Product ( I think is what I have done)

Can you post some sample data from all your tables? Be sure to include field names. Use this format:

TableName
field1name, field2name, field3name
1/18/2013, 18, "John"
2/12/2013, 72, "Jane"

Yes, yes I can
Company:Table
Id (KEY),Company,Phone,Contact
1,Gregg's Lugs,895.965.1258,Lisa
2,Bobs Wheel,895.965.1856,Sam

Item:Table
Id (KEY),Description
3,5/8" Chrome Plated Lug
4,1/2" No Plate Lug
5,5/8" No Plate Lug

Price:Table
Id (KEY),Company,Item,Part Number,Price
6,Gregg's lugs,5/8" Chrome Plated Lug,5/8crmpl,$.50
7,Gregg's lugs,1/2" No Plate Lug,1/2plain,$.20
8,Gregg's lugs,5/8" No Plate Lug,5/8plain,$.35
9,Bobs Wheel,5/8" Chrome Plated Lug,5/8crmpl,$1.25
10,Bobs Wheel,1/2" No Plate Lug,1/2plain,$.89
11,Bobs Wheel,5/8" No Plate Lug,5/8plain,$1.01

I just realized as I typed this out that under the fields Company,Item (IN PRICE:TABLE) I told access to also link the ID fields, is that correct?

This is defiantly a design flaw, I thought I understood, but do not and thus now must correct.

Here is the link to the previous thread

http://www.access-programmers.co.uk/forums/showthread.php?t=241779

Brian

As per my previous comments in the related post/thread, I think in your "business", you have a logical model that is similar to/derived from the attached jpg. This is very generic and you may have to tweak it to match your requirement.

You provide a Purchase Order to a Supplier to purchase Items/Products.
A PurchaseOrder is for many Items.
From a different view, a Customer creates an Order to "order" Items/Products. Several Items are "ordered" at the same time, which means an Order often contains many Items.

If in a data base you record Price/Cost with the Product only, you can run into difficulty when reporting/querying past Orders. You tend to get only Prices/Costs that reflect the Current Price/Cost. (This would be perfect if the Price/Cost of a Product never changed - but isn't very realistic.) So in the OrderLineItem/PurchaseOrderLineItem table you record the Quantity and the Agreed to Price, and resolve the difficulty in reporting/querying past Orders/Purchases.

Good luck with your project.