Show all records on a joined query with criteria

[Angel]

I have a table that contains Order information and another table that
contains Inventory information. I created one query that has 2 tables joined
showing order records from the orders table (outerjoin) and matching
inventory items to the order (showing available quantities). I would like
the query to do just that but only show items in the inventory table that
have inventory on Bin 300.
When I put 300 under the criteria not all records show for the order but
only the records joined to the Inventory table that are in bin 300. I went
around that by creatuing a separate query that gives all inventory in bin
300. Then, I joined that query to my original query (outer Join) and it did
work. Is there a way to acomplish this in my original query without having
to create a second query?
Thank you in advance to those trying to help.

 

[Tom Ellison]

Dear Angel:

I'm not getting how it is the case that some items "have inventory on Bin
300" differs from "in bin 300."

Nonetheless, if you will post the two queries I will propose how they can be
made into one, if that is within my knowledge.

I'm not sure what advantage there would be to having it all in one query,
although I certainly tend to write my queries in one rather large sweep,
unless the query can be broken into portions that may be re-usable in
building other queries. There's a principle of modularity in that. That is
largely a matter of judgement borne of experience.

Tom Ellison

 

[Chris2]

I have a table that contains Order information and another table that
contains Inventory information. I created one query that has 2 tables joined
showing order records from the orders table (outerjoin) and matching
inventory items to the order (showing available quantities).

Angel,

You mentioned: " created one query that has 2 tables joined showing
order records from the orders table (outerjoin)"

And:

You also mentioned: "and matching inventory items to the order
(showing available quantities)."

I apologize, but I am not able to resovle the references here. The
first part says you have two tables that are joined by an OUTER
JOIN. The second part says that you have "matching inteventory
items". An OUTER JOIN does not display matching items. It displays
matches, and non-matches from the other table.

Can you provide your SQL? (Open the Query in Design View, switch to
SQL View from the menus, View > SQL View, and copy and past the SQL
Code here.)

I would like
the query to do just that but only show items in the inventory table that
have inventory on Bin 300.

The above description does not mention any criteria that would
restrict the rows returned by location/bin number. Therefore, this
statement seems to imply that your test data has only items in Bin
300.

If you post your sample data along with the SQL, it would provide
more clarity.

When I put 300 under the criteria

This implies (to me, anyway) that you are adding a location/bin
criteria after you have tried the query without it, as described
above. Since above, without the criteria, you were only receiving
items in Bin 300, how would adding criteria to restrict to Bin 300
change the situation?

not all records show for the order but
only the records joined to the Inventory table that are in bin 300. I went
around that by creatuing a separate query that gives all inventory in bin
300. Then, I joined that query to my original query (outer Join) and it did
work. Is there a way to acomplish this in my original query without having
to create a second query?
Thank you in advance to those trying to help.

May I ask what is important about Bin 300? From the initial
description, I though you were trying to show inventory available
for existing orders. What does it matter if the items are in Bin
300 or Bin 500? Why are we trying to restrict the rows that are
returned to just Bin 300?



More information on how you can improve your chances of getting your
question answered:

-------------------------------

Formatting:

Please use a monospace font (Courier New, etc.) when writing out
your examples (all descriptions, charts, SQL, etc.).

-------------------------------

Process Description:

Please only include the shortest possible narrative of what is going
on with the query. (Include all that is necessary, and nothing
more.)

When parts of your query make calculations, show the exact code or
nearest readable plain-text math formula you can create.

When you are done with this section, re-read it several times before
posting to assure yourself that you are accurately describing the
situation in way you believe others will understand.

-------------------------------

Table Structures/Description:

Post a description of your table structures.

Although it can be a source of information, please do not copy and
paste information directly from MS Access' Documenter. It is
virtually unreadable. Please distill down and legibly format only
the relevant table information.

If reading the information in MS Access' Documenter is too
intimidating (I know what its output says, myself, and I still
dislike going over its output listings), open your table in Design
View, view the column names and data types in it, and then type out
the column names and data types *that are necessary* (do not include
columns that are not absolutely necessary for the query). Use the
Index dialog box (you can get at it by clicking on the "key" icon on
the toolbar) to locate information on primary and foreign keys and
other indexes and type out that information, as well.

Note: For table descriptions (or DDL) lining up the column names,
data type names, and key/index information in neat columns is quite
helpful.

Note: If you know how to write DDL SQL (CREATE TABLE), please post
that (including constraints) instead of text descriptions. (Please
post only the portion of the DDL that is relevant.)


Example (text description):

MyTableOne
MyTableOneID AUTOINCREMENT PK
ColTwo INTEGER NOT NULL
ColThree TEXT(10)

MyTableTwo
MyTableTwoID AUTOINCREMENT PK\
MyTableOneID INTEGER PK/-- Composite Primary Key
ColThree INTEGER FK -- MyTableOne ColOne
ColFour DATETIME
ColFive CURRENCY
ColSix BIT
ColSeven TEXT(1)
ColEight TEXT(1)

etc., etc., etc.


Example (DDL SQL/CREATE TABLE):

CREATE TABLE MyTableOne
(MyTableOneID AUTOINCREMENT
,ColTwo INTEGER NOT NULL
,ColThree TEXT(10)
,CONSTRAINT pk_MyTableOne
PRIMARY KEY (MyTableOneID)
)

CREATE TABLE MyTableTwo
(MyTableTwoID AUTOINCREMENT
,MyTableOneID INTEGER
,ColThree INTEGER
,ColFour DATETIME
,ColFive CURRENCY
,ColSix BIT
,ColSeven TEXT(1)
,ColEight TEXT(1)
,CONSTRAINT pk_MyTableTwo
PRIMARY KEY (MyTableTwoID)
,CONSTRAINT fk_MyTableTwo_MyTableOne_MyTableOneID
FOREIGN KEY (MyTableOneID)
REFERENCES MyTableOne (MyTableOneID)
)

The Primary Key and Foreign Key notes (or constraints in the DDL
SQL) are *critical*.

-------------------------------

Sample Data (using comma delimited lists):

Note: If your sample data is "wide" across the screen, and you can't
trim out any columns because they are needed, make *two* (or more)
charts, and then clearly note that the second chart is the
continuation of the first chart for the same table. It is far
easier to convert a comma delimited chart into a table in MS Word or
import it directly into MS Excel (where the data can be copied and
pasted into a new table in MS Access) or even MS Access than it is
to manually undo the line-break on *every* row of a line-wrapped
chart (in fact, manually undoing the line-breaks caused by newsgroup
posting is a huge pain in the neck).

Note: In a comma delimited list, it is not absolutely necessary
(although it is nice) to have the data in the columns lined straight
up and down, like I have in my examples below. When the data is
finally imported into MS Access, a quick glance at the table in
datasheet view will show things lined up straight. It is not
necessary to expend extra effort on your chart here. (The right
data does have to be in the right position of each row of the chart,
of course.)

Note: Use the real table and column names whenever possible. Use
invented table names and column names (like I use below in my
example) only when you absolutely have to.

Note: When naming the columns on this chart, use the same column
names as is the table structures above. Using shortened names (or
completely different names, for whatever reason) may save space and
prevent line-wraps, but it can be severely confusing. If the chart
gets too "wide", make two (or more) charts if you have to, as noted
above in Table Structures.

Note: Please include just enough rows of sample data so that
sufficient tests of the various possibilities ("test cases") can be
made.

Note: Please do not attempt to post endless rows of data. 3-5 rows
are probably the minimum, and 10-20 row are probably the normal
maximum. (Post only what is necessary, and no more.)

Note: Please try and use real data when possible. However, real
people's personal information, or private information (banking,
proprietary, etc.), should never be posted. When you have
information that cannot be posted, you will have to invent test data
that can produce results similar to what the real data would
produce.


MyTableOne
MyTableOneID, ColTwo, ColThree
1, 2, a
3, 4, b
5, 6, c

MyTableTwo (Part One)
MyTableTwoID, MyTableOneID, ColThree, ColFour, ColFive
1, 5, 1, 01/01/06, 1.01


MyTableTwo (Part Two)
ColSix, ColSeven, ColEight
-1 g, h

-------------------------------

Desired Results

.. . . <whatever it is you want your query to produce; "the right
stuff", if you will forgive the pun>

(Same chart style as found in the Sample Data section.)

-------------------------------

Query:

Your SQL query code attempts to date. (If "SQL code" throws you for
a loop, open your Query in Design View, and then use the menus, View

SQL View, to switch to a window that will show the SQL code. Copy

and paste that into your new post to the newsgroup.)

Note: There is a huge temptation to merely copy and past the SQL
code. Usually, this is completely unreadable, and whoever reads it
must re-align the code in order to make heads or tails of it (yes,
there are a few out there who can read endless unbroken streams of
code packed together, but I am not one of them). If you know how,
spend some time straightening out and aligning the SQL before
posting it.

Note: In some situations, of course, you will have no query or SQL
code at all.

-------------------------------

Current Results:

.. . . <the incorrect results the current query(s) is producing>

(Same chart style as found in the Sample Data section.)

-------------------------------

Lots Of Work:

Does all this sound like a lot of work?

Remember, whatever work you haven't done will have to be done by
whoever tries to answer your question.

Any information that is not included may have to be asked for,
necessitating additional posts (sometimes many) before someone can
begin answering your question.

Time spent doing these things is time spent not answering your
question.

-------------------------------

I hope that the above can be of assistance in helping you receive an
answer to your various MS Access questions.


Sincerely,

Chris O.

 

[Angel]

Sorry for the confusion. Here is my query
SELECT dbo_AMC_CO_DETAIL_Report.*, ShipInv.STKBIN, ShipInv.QTY_BY_LOC,
ShipInv.LOT, ShipInv.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN ShipInv ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = ShipInv.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));

This query depends on this other one.
ShipInv
SELECT dbo_AMC_ITEM_INV.ITEM, dbo_AMC_ITEM_INV.ITEM_DESC,
dbo_AMC_ITEM_INV.INV_CATGRY, dbo_AMC_ITEM_INV.QTY_BY_LOC,
dbo_AMC_ITEM_INV.LOT, dbo_AMC_ITEM_INV.MFG_DATE, Trim([STK_ROOM]) & "-" &
Trim([BIN]) AS STKBIN
FROM dbo_AMC_ITEM_INV
WHERE (((Trim([STK_ROOM]) & "-" & Trim([BIN])) Like "2-300"));

This is the Query I would like to end up with but be able to put criteria
under STKBIN to show all Items in inventory for 2-300, but continue to
display all Order line Items.
SELECT dbo_AMC_CO_DETAIL_Report.*, [STK_ROOM] & "-" & [BIN] AS STKBIN,
dbo_ITEM_INVENTORY.QTY_BY_LOC, dbo_ITEM_INVENTORY.LOT,
dbo_ITEM_INVENTORY.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN dbo_ITEM_INVENTORY ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = dbo_ITEM_INVENTORY.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));
Thank you so much.

 

[Tom Ellison]

Dear Angel:

To be able to study your queries, I'm going to reformat them and add
aliasing. This is primarily for my own use, and future reference:

SELECT A.*, SI.STKBIN, SI.QTY_BY_LOC,
SI.LOT, SI.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report A
LEFT JOIN ShipInv SI
ON A.ItemNumber = SI.ITEM
WHERE A.CONumber = [Forms]![OrderPrint_frm]![OrderNo]

See? Isn't that a bit nicer?

SELECT A.ITEM, A.ITEM_DESC,
A.INV_CATGRY, A.QTY_BY_LOC,
A.LOT, A.MFG_DATE,
Trim(STK_ROOM) & "-" & Trim(BIN) AS STKBIN
FROM dbo_AMC_ITEM_INV A
WHERE Trim(STK_ROOM) & "-" & Trim(BIN) Like "2-300"

OK! Now I can begin to see what you're doing!

By, "This query depends on this other one." I assume you mean that the first
query is stored and named "dbo_AMC_ITEM_INV" and is the sole source for the
second query. So far, so good?

A comment, if you'll indulge me. I don't see a need for the concatenation
nor for the LIKE comparison in the last line of the second query. How
about:

WHERE Trim(STK_ROOM) = "2" AND Trim(BIN) = "300"

Perhaps this would do the same thing, but with less processing. Just a
suggestion. I'll use this in my attempt below:

SELECT A.ITEM, A.ITEM_DESC,
A.INV_CATGRY, SI.QTY_BY_LOC,
SI.LOT, SI.MFG_DATE,
Trim(A.STK_ROOM) & "-" & Trim(A.BIN) AS STKBIN
FROM dbo_AMC_CO_DETAIL_Report A
LEFT JOIN ShipInv SI
ON A.ItemNumber = SI.ITEM
WHERE A.CONumber = [Forms]![OrderPrint_frm]![OrderNo]
AND Trim(A.STK_ROOM) = "2" AND Trim(A.BIN) = "300"

I hope I've done this carefully enough to not screw it up!

Tom Ellison

Sorry for the confusion. Here is my query
SELECT dbo_AMC_CO_DETAIL_Report.*, ShipInv.STKBIN, ShipInv.QTY_BY_LOC,
ShipInv.LOT, ShipInv.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN ShipInv ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = ShipInv.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));

This query depends on this other one.
ShipInv
SELECT dbo_AMC_ITEM_INV.ITEM, dbo_AMC_ITEM_INV.ITEM_DESC,
dbo_AMC_ITEM_INV.INV_CATGRY, dbo_AMC_ITEM_INV.QTY_BY_LOC,
dbo_AMC_ITEM_INV.LOT, dbo_AMC_ITEM_INV.MFG_DATE, Trim([STK_ROOM]) & "-" &
Trim([BIN]) AS STKBIN
FROM dbo_AMC_ITEM_INV
WHERE (((Trim([STK_ROOM]) & "-" & Trim([BIN])) Like "2-300"));

This is the Query I would like to end up with but be able to put criteria
under STKBIN to show all Items in inventory for 2-300, but continue to
display all Order line Items.
SELECT dbo_AMC_CO_DETAIL_Report.*, [STK_ROOM] & "-" & [BIN] AS STKBIN,
dbo_ITEM_INVENTORY.QTY_BY_LOC, dbo_ITEM_INVENTORY.LOT,
dbo_ITEM_INVENTORY.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN dbo_ITEM_INVENTORY ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = dbo_ITEM_INVENTORY.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));
Thank you so much.

Dear Angel:

I'm not getting how it is the case that some items "have inventory on Bin
300" differs from "in bin 300."

Nonetheless, if you will post the two queries I will propose how they can
be made into one, if that is within my knowledge.

I'm not sure what advantage there would be to having it all in one query,
although I certainly tend to write my queries in one rather large sweep,
unless the query can be broken into portions that may be re-usable in
building other queries. There's a principle of modularity in that. That
is largely a matter of judgement borne of experience.

Tom Ellison

 

[Angel]

Thanks Tom.
Basically I just want the following query to give me all order line items
and show me if they have inventory on hand. I applied some of the technics
you mentioned but I am still unabale to come up with what I need. Here is
the query in question.
SELECT dbo_AMC_CO_DETAIL_Report.*, dbo_ITEM_INVENTORY.QTY_BY_LOC,
dbo_ITEM_INVENTORY.LOT, dbo_ITEM_INVENTORY.MFG_DATE, Trim([STK_ROOM]) & "_"
& Trim([BIN]) AS STKBIN
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN dbo_ITEM_INVENTORY ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = dbo_ITEM_INVENTORY.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo])
AND ((Trim([dbo_ITEM_INVENTORY].[STK_ROOM]))="2") AND
((Trim([dbo_ITEM_INVENTORY].[BIN]))="300"));
The query is giving to me the order line items that have inventory on hand
but is leaving out the ones that do not have inventory.
Thanks a million and sorry for the mess!

Dear Angel:

To be able to study your queries, I'm going to reformat them and add
aliasing. This is primarily for my own use, and future reference:

SELECT A.*, SI.STKBIN, SI.QTY_BY_LOC,
SI.LOT, SI.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report A
LEFT JOIN ShipInv SI
ON A.ItemNumber = SI.ITEM
WHERE A.CONumber = [Forms]![OrderPrint_frm]![OrderNo]

See? Isn't that a bit nicer?

SELECT A.ITEM, A.ITEM_DESC,
A.INV_CATGRY, A.QTY_BY_LOC,
A.LOT, A.MFG_DATE,
Trim(STK_ROOM) & "-" & Trim(BIN) AS STKBIN
FROM dbo_AMC_ITEM_INV A
WHERE Trim(STK_ROOM) & "-" & Trim(BIN) Like "2-300"

OK! Now I can begin to see what you're doing!

By, "This query depends on this other one." I assume you mean that the
first query is stored and named "dbo_AMC_ITEM_INV" and is the sole source
for the second query. So far, so good?

A comment, if you'll indulge me. I don't see a need for the concatenation
nor for the LIKE comparison in the last line of the second query. How
about:

WHERE Trim(STK_ROOM) = "2" AND Trim(BIN) = "300"

Perhaps this would do the same thing, but with less processing. Just a
suggestion. I'll use this in my attempt below:

SELECT A.ITEM, A.ITEM_DESC,
A.INV_CATGRY, SI.QTY_BY_LOC,
SI.LOT, SI.MFG_DATE,
Trim(A.STK_ROOM) & "-" & Trim(A.BIN) AS STKBIN
FROM dbo_AMC_CO_DETAIL_Report A
LEFT JOIN ShipInv SI
ON A.ItemNumber = SI.ITEM
WHERE A.CONumber = [Forms]![OrderPrint_frm]![OrderNo]
AND Trim(A.STK_ROOM) = "2" AND Trim(A.BIN) = "300"

I hope I've done this carefully enough to not screw it up!

Tom Ellison

Sorry for the confusion. Here is my query
SELECT dbo_AMC_CO_DETAIL_Report.*, ShipInv.STKBIN, ShipInv.QTY_BY_LOC,
ShipInv.LOT, ShipInv.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN ShipInv ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = ShipInv.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));

This query depends on this other one.
ShipInv
SELECT dbo_AMC_ITEM_INV.ITEM, dbo_AMC_ITEM_INV.ITEM_DESC,
dbo_AMC_ITEM_INV.INV_CATGRY, dbo_AMC_ITEM_INV.QTY_BY_LOC,
dbo_AMC_ITEM_INV.LOT, dbo_AMC_ITEM_INV.MFG_DATE, Trim([STK_ROOM]) & "-" &
Trim([BIN]) AS STKBIN
FROM dbo_AMC_ITEM_INV
WHERE (((Trim([STK_ROOM]) & "-" & Trim([BIN])) Like "2-300"));

This is the Query I would like to end up with but be able to put criteria
under STKBIN to show all Items in inventory for 2-300, but continue to
display all Order line Items.
SELECT dbo_AMC_CO_DETAIL_Report.*, [STK_ROOM] & "-" & [BIN] AS STKBIN,
dbo_ITEM_INVENTORY.QTY_BY_LOC, dbo_ITEM_INVENTORY.LOT,
dbo_ITEM_INVENTORY.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN dbo_ITEM_INVENTORY ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = dbo_ITEM_INVENTORY.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));
Thank you so much.

Dear Angel:

I'm not getting how it is the case that some items "have inventory on
Bin 300" differs from "in bin 300."

Nonetheless, if you will post the two queries I will propose how they
can be made into one, if that is within my knowledge.

I'm not sure what advantage there would be to having it all in one
query, although I certainly tend to write my queries in one rather large
sweep, unless the query can be broken into portions that may be
re-usable in building other queries. There's a principle of modularity
in that. That is largely a matter of judgement borne of experience.

Tom Ellison


I have a table that contains Order information and another table that
contains Inventory information. I created one query that has 2 tables
joined showing order records from the orders table (outerjoin) and
matching inventory items to the order (showing available quantities). I
would like the query to do just that but only show items in the
inventory table that have inventory on Bin 300.
When I put 300 under the criteria not all records show for the order
but only the records joined to the Inventory table that are in bin 300.
I went around that by creatuing a separate query that gives all
inventory in bin 300. Then, I joined that query to my original query
(outer Join) and it did work. Is there a way to acomplish this in my
original query without having to create a second query?
Thank you in advance to those trying to help.

 

[Tom Ellison]

Dear Angel:

Your query uses a LEFT JOIN. It then says to use all the rows from
dbo_AMC_CO_DETAIL_Report and any related rows from dbo_ITEM_INVENTORY.

If the missing information is in dbo_ITEM_INVENTORY, then it is possible you
have the JOIN backward. Try a RIGHT JOIN instead if this is the case.

Otherwise, I recommend you verify that the missing information actually
exists in dbo_AMD_CO_DETAIL.

Tom Ellison

Thanks Tom.
Basically I just want the following query to give me all order line items
and show me if they have inventory on hand. I applied some of the technics
you mentioned but I am still unabale to come up with what I need. Here is
the query in question.
SELECT dbo_AMC_CO_DETAIL_Report.*, dbo_ITEM_INVENTORY.QTY_BY_LOC,
dbo_ITEM_INVENTORY.LOT, dbo_ITEM_INVENTORY.MFG_DATE, Trim([STK_ROOM]) &
"_" & Trim([BIN]) AS STKBIN
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN dbo_ITEM_INVENTORY ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = dbo_ITEM_INVENTORY.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo])
AND ((Trim([dbo_ITEM_INVENTORY].[STK_ROOM]))="2") AND
((Trim([dbo_ITEM_INVENTORY].[BIN]))="300"));
The query is giving to me the order line items that have inventory on hand
but is leaving out the ones that do not have inventory.
Thanks a million and sorry for the mess!

Dear Angel:

To be able to study your queries, I'm going to reformat them and add
aliasing. This is primarily for my own use, and future reference:

SELECT A.*, SI.STKBIN, SI.QTY_BY_LOC,
SI.LOT, SI.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report A
LEFT JOIN ShipInv SI
ON A.ItemNumber = SI.ITEM
WHERE A.CONumber = [Forms]![OrderPrint_frm]![OrderNo]

See? Isn't that a bit nicer?

SELECT A.ITEM, A.ITEM_DESC,
A.INV_CATGRY, A.QTY_BY_LOC,
A.LOT, A.MFG_DATE,
Trim(STK_ROOM) & "-" & Trim(BIN) AS STKBIN
FROM dbo_AMC_ITEM_INV A
WHERE Trim(STK_ROOM) & "-" & Trim(BIN) Like "2-300"

OK! Now I can begin to see what you're doing!

By, "This query depends on this other one." I assume you mean that the
first query is stored and named "dbo_AMC_ITEM_INV" and is the sole source
for the second query. So far, so good?

A comment, if you'll indulge me. I don't see a need for the
concatenation nor for the LIKE comparison in the last line of the second
query. How about:

WHERE Trim(STK_ROOM) = "2" AND Trim(BIN) = "300"

Perhaps this would do the same thing, but with less processing. Just a
suggestion. I'll use this in my attempt below:

SELECT A.ITEM, A.ITEM_DESC,
A.INV_CATGRY, SI.QTY_BY_LOC,
SI.LOT, SI.MFG_DATE,
Trim(A.STK_ROOM) & "-" & Trim(A.BIN) AS STKBIN
FROM dbo_AMC_CO_DETAIL_Report A
LEFT JOIN ShipInv SI
ON A.ItemNumber = SI.ITEM
WHERE A.CONumber = [Forms]![OrderPrint_frm]![OrderNo]
AND Trim(A.STK_ROOM) = "2" AND Trim(A.BIN) = "300"

I hope I've done this carefully enough to not screw it up!

Tom Ellison

Sorry for the confusion. Here is my query
SELECT dbo_AMC_CO_DETAIL_Report.*, ShipInv.STKBIN, ShipInv.QTY_BY_LOC,
ShipInv.LOT, ShipInv.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN ShipInv ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = ShipInv.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));

This query depends on this other one.
ShipInv
SELECT dbo_AMC_ITEM_INV.ITEM, dbo_AMC_ITEM_INV.ITEM_DESC,
dbo_AMC_ITEM_INV.INV_CATGRY, dbo_AMC_ITEM_INV.QTY_BY_LOC,
dbo_AMC_ITEM_INV.LOT, dbo_AMC_ITEM_INV.MFG_DATE, Trim([STK_ROOM]) & "-"
& Trim([BIN]) AS STKBIN
FROM dbo_AMC_ITEM_INV
WHERE (((Trim([STK_ROOM]) & "-" & Trim([BIN])) Like "2-300"));

This is the Query I would like to end up with but be able to put
criteria under STKBIN to show all Items in inventory for 2-300, but
continue to display all Order line Items.
SELECT dbo_AMC_CO_DETAIL_Report.*, [STK_ROOM] & "-" & [BIN] AS STKBIN,
dbo_ITEM_INVENTORY.QTY_BY_LOC, dbo_ITEM_INVENTORY.LOT,
dbo_ITEM_INVENTORY.MFG_DATE
FROM dbo_AMC_CO_DETAIL_Report LEFT JOIN dbo_ITEM_INVENTORY ON
dbo_AMC_CO_DETAIL_Report.ItemNumber = dbo_ITEM_INVENTORY.ITEM
WHERE
(((dbo_AMC_CO_DETAIL_Report.CONumber)=[Forms]![OrderPrint_frm]![OrderNo]));
Thank you so much.


Dear Angel:

I'm not getting how it is the case that some items "have inventory on
Bin 300" differs from "in bin 300."

Nonetheless, if you will post the two queries I will propose how they
can be made into one, if that is within my knowledge.

I'm not sure what advantage there would be to having it all in one
query, although I certainly tend to write my queries in one rather
large sweep, unless the query can be broken into portions that may be
re-usable in building other queries. There's a principle of modularity
in that. That is largely a matter of judgement borne of experience.

Tom Ellison


I have a table that contains Order information and another table that
contains Inventory information. I created one query that has 2 tables
joined showing order records from the orders table (outerjoin) and
matching inventory items to the order (showing available quantities). I
would like the query to do just that but only show items in the
inventory table that have inventory on Bin 300.
When I put 300 under the criteria not all records show for the order
but only the records joined to the Inventory table that are in bin
300. I went around that by creatuing a separate query that gives all
inventory in bin 300. Then, I joined that query to my original query
(outer Join) and it did work. Is there a way to acomplish this in my
original query without having to create a second query?
Thank you in advance to those trying to help.