Query result?

[anden7606]

I have some problems with quering two tables, orders and warehouse.
The problem is that I need to find out ONE shelf containing "car001", not all shelfs containing "car001".
The tables looks like this,

Orders,
Orderno____Stockno_____Units
100________car001______3
100________car003______2
100________oil007_______10


Warehouse,
Stockno_____Units_______Shelf
car001_______100_______A-2-10
car001_______10________A-5-1
car001_______100_______A-5-2
oil003________50________B-1-1
oil007________50________B-1-2
car003_______50________C-3-2
car003_______25________C-3-1

I need this result,
car001________3________A-5-1
car003________2________C-3-1
oil007________10________B-1-1

But this is the result I get,

car001________3________A-2-10
car001________3________A-5-1
car001________3________A-5-2
car003________2________C-3-2
car003________2________C-3-1
oil007________10________B-1-2

Sorry for my poor english and knowledge i Access.
Anden

 

[Louie]

What does your Query look like?

 

[flixx]

To anden7606:
To resolve your query issue just do the following:
1. Open query in design mode
2. Click on View from Menu and select Totals
3. Under Total in the Query Design Grid
a. Leave field [stockno] - Group By
b. Change field [shelf] - Max
c. Change field [Units] - Max
4. Save and then run your query in table view.
Hope this helps, Flixx.
Here is the sample database zipped:
See my sample below:

 

[anden7606]

Thanks!
/Anden