query over my head (order vs in stock inventory)

[Anika]

I have 2 tables. OrderInfo and Inventory. I created a one to many relationship with Order ID being the linking field. In OrderInfo, I can have up to 6 distinct items being ordered but some fields will always remain the same. Example:

20 | Cotton | White (will be the same) and then T-shits | Camisoles | Tank Tops will be sample order details.

In the inventory table I have several listings of each T-shirts, Camisoles and Tank tops with their own Item#.

I am attempting to see when I get an order, what I have on hand and what I need to make. When I try to create my query, I must be doing something wrong with the join or something else because I get too many results. I know I only have 19 tank tops on hand (I can filter them from the Inventory table) but the query returns 589.

I do not hav sample of the code because I got so frustrated that I scrapped it (I have been staring at this for a few days now). Could someone please offer a link to a query tutorial r any advice here pleasse. I am not sure where to even go from here anymore.

 

[evanscamman]

Perhaps you just need to set the join property to "Show all records from Table1 and only matching records from table2".
Or maybe try setting propertios to Distict Records or Distinct Values.

These are shots in the dark, but I don't have much info to go by.
Evan

 

[Anika]

The oddest thing is that I actually chose to show only the records whre both fields match. I think my problem may be is that I am trying to select based of too many criteria.

How would you write a query where you want to display results from table Inventory only where fields amount, color and fabric are the same? COuld someone please help with that bit and that could maybe shed some light on my ignorance. By results I mean the number of items. Maybe I should just try a Dcount?

 

[raskew]

Hi -

Would you post the db?

Bob

Added: Spreading your issues over multiple threads http://www.access-programmers.co.uk/forums/showthread.php?p=834663&posted=1#post834663 will not prove beneficial to resolving your problems. Please post your db, then stick with one thread.

 

[Anika]

Thank you for your reply Bob. I will provide my database most likely Monday as I am away from home for the weekend.

I apologize for multiple posts. I posted my other question in General as it was not Query related, thinking that was the best place for it. Thank you for a lesson in posting ettiquete. I will keep all my questions in the same post, even if they span multiple categories.