|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Here is my SQL query:
SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
AS TransDate, tblInventory.ID,
IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
(tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
tblInventory.CustomerID
WHERE (((tblCustomer.ShortName) Like "NESTLE"))
ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
tblProduct.ProductCode, tblInventory.MoveOrder;
I appreciate your assistance.
samiam
"KARL DEWEY" wrote:
> Post the SQL of your query.
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "samiam" wrote:
>
> > Karl:
> >
> > My query includes a column where quantity * weight is placed. This always
> > has a number in it. What I'm trying to do is if the summation of this column
> > for a product is zero, exclude that product from the report. I've tried <>0,
> > but I still get zero result products sent to the report.
> >
> > samiam
> >
> > "KARL DEWEY" wrote:
> >
> > > Use criteria in your totals query <> 0 and it not pull them.
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "samiam" wrote:
> > >
> > > > I have a report based on a query. I would like to exclude the items in the
> > > > report that sum to zero in order to shorten my report and remove those items
> > > > that have been "used." How do I exclude the detail records that will sum to
> > > > zero in my report? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
I would create a totals query like:
SELECT tblInventory.ID
FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
tblInventory.CustomerID
WHERE tblCustomer.ShortName Like "NESTLE"
GROUP BY tblInventory.ID
HAVING Sum(Quantity) <> 0;
Then add this query to your report's query and join the ID fields. This
should limit the report to IDs where the quantity is not 0.
Apparently you work for one of our suppliers (I work for Nestle ;-)
--
Duane Hookom
Microsoft Access MVP
"samiam" wrote:
> Here is my SQL query:
>
> SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
> IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
> AS TransDate, tblInventory.ID,
> IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
> AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
> tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
> tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
> ([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
> FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
> (tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
> tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
> tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
> tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
> tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
> tblInventory.CustomerID
> WHERE (((tblCustomer.ShortName) Like "NESTLE"))
> ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
> tblProduct.ProductCode, tblInventory.MoveOrder;
>
> I appreciate your assistance.
> samiam
>
> "KARL DEWEY" wrote:
>
> > Post the SQL of your query.
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "samiam" wrote:
> >
> > > Karl:
> > >
> > > My query includes a column where quantity * weight is placed. This always
> > > has a number in it. What I'm trying to do is if the summation of this column
> > > for a product is zero, exclude that product from the report. I've tried <>0,
> > > but I still get zero result products sent to the report.
> > >
> > > samiam
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > Use criteria in your totals query <> 0 and it not pull them.
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "samiam" wrote:
> > > >
> > > > > I have a report based on a query. I would like to exclude the items in the
> > > > > report that sum to zero in order to shorten my report and remove those items
> > > > > that have been "used." How do I exclude the detail records that will sum to
> > > > > zero in my report? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I work at Payson Fruit Growers in Payson, UT. We store products for you.
Thanks a bunch.
samiam
"Duane Hookom" wrote:
> I would create a totals query like:
>
> SELECT tblInventory.ID
> FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
> tblInventory.CustomerID
> WHERE tblCustomer.ShortName Like "NESTLE"
> GROUP BY tblInventory.ID
> HAVING Sum(Quantity) <> 0;
>
> Then add this query to your report's query and join the ID fields. This
> should limit the report to IDs where the quantity is not 0.
>
> Apparently you work for one of our suppliers (I work for Nestle ;-)
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "samiam" wrote:
>
> > Here is my SQL query:
> >
> > SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
> > IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
> > AS TransDate, tblInventory.ID,
> > IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
> > AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
> > tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
> > tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
> > ([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
> > FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
> > (tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
> > tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
> > tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
> > tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
> > tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
> > tblInventory.CustomerID
> > WHERE (((tblCustomer.ShortName) Like "NESTLE"))
> > ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
> > tblProduct.ProductCode, tblInventory.MoveOrder;
> >
> > I appreciate your assistance.
> > samiam
> >
> > "KARL DEWEY" wrote:
> >
> > > Post the SQL of your query.
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "samiam" wrote:
> > >
> > > > Karl:
> > > >
> > > > My query includes a column where quantity * weight is placed. This always
> > > > has a number in it. What I'm trying to do is if the summation of this column
> > > > for a product is zero, exclude that product from the report. I've tried <>0,
> > > > but I still get zero result products sent to the report.
> > > >
> > > > samiam
> > > >
> > > > "KARL DEWEY" wrote:
> > > >
> > > > > Use criteria in your totals query <> 0 and it not pull them.
> > > > > --
> > > > > KARL DEWEY
> > > > > Build a little - Test a little
> > > > >
> > > > >
> > > > > "samiam" wrote:
> > > > >
> > > > > > I have a report based on a query. I would like to exclude the items in the
> > > > > > report that sum to zero in order to shorten my report and remove those items
> > > > > > that have been "used." How do I exclude the detail records that will sum to
> > > > > > zero in my report? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Duane:
Should the HAVING clause include quantity times new weight, or just quantity?
samiam
"Duane Hookom" wrote:
> I would create a totals query like:
>
> SELECT tblInventory.ID
> FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
> tblInventory.CustomerID
> WHERE tblCustomer.ShortName Like "NESTLE"
> GROUP BY tblInventory.ID
> HAVING Sum(Quantity) <> 0;
>
> Then add this query to your report's query and join the ID fields. This
> should limit the report to IDs where the quantity is not 0.
>
> Apparently you work for one of our suppliers (I work for Nestle ;-)
>
> --
> Duane Hookom
> Microsoft Access MVP
>
>
> "samiam" wrote:
>
> > Here is my SQL query:
> >
> > SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
> > IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
> > AS TransDate, tblInventory.ID,
> > IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
> > AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
> > tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
> > tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
> > ([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
> > FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
> > (tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
> > tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
> > tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
> > tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
> > tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
> > tblInventory.CustomerID
> > WHERE (((tblCustomer.ShortName) Like "NESTLE"))
> > ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
> > tblProduct.ProductCode, tblInventory.MoveOrder;
> >
> > I appreciate your assistance.
> > samiam
> >
> > "KARL DEWEY" wrote:
> >
> > > Post the SQL of your query.
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "samiam" wrote:
> > >
> > > > Karl:
> > > >
> > > > My query includes a column where quantity * weight is placed. This always
> > > > has a number in it. What I'm trying to do is if the summation of this column
> > > > for a product is zero, exclude that product from the report. I've tried <>0,
> > > > but I still get zero result products sent to the report.
> > > >
> > > > samiam
> > > >
> > > > "KARL DEWEY" wrote:
> > > >
> > > > > Use criteria in your totals query <> 0 and it not pull them.
> > > > > --
> > > > > KARL DEWEY
> > > > > Build a little - Test a little
> > > > >
> > > > >
> > > > > "samiam" wrote:
> > > > >
> > > > > > I have a report based on a query. I would like to exclude the items in the
> > > > > > report that sum to zero in order to shorten my report and remove those items
> > > > > > that have been "used." How do I exclude the detail records that will sum to
> > > > > > zero in my report? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I don't know your data so you need to decide if the weight is significant. I
assumed the quantity was really the only required value.
--
Duane Hookom
Microsoft Access MVP
"samiam" wrote:
> Duane:
>
> Should the HAVING clause include quantity times new weight, or just quantity?
>
> samiam
>
> "Duane Hookom" wrote:
>
> > I would create a totals query like:
> >
> > SELECT tblInventory.ID
> > FROM tblCustomer INNER JOIN tblInventory ON tblCustomer.uID =
> > tblInventory.CustomerID
> > WHERE tblCustomer.ShortName Like "NESTLE"
> > GROUP BY tblInventory.ID
> > HAVING Sum(Quantity) <> 0;
> >
> > Then add this query to your report's query and join the ID fields. This
> > should limit the report to IDs where the quantity is not 0.
> >
> > Apparently you work for one of our suppliers (I work for Nestle ;-)
> >
> > --
> > Duane Hookom
> > Microsoft Access MVP
> >
> >
> > "samiam" wrote:
> >
> > > Here is my SQL query:
> > >
> > > SELECT tblInventory.CustomerID, tblCustomer.ShortName, tblCustomer.FullName,
> > > IIf([TransTypeID]=1,[RecDate],IIf([TransTypeID]=2,[BOLDate],IIf([TransTypeID]=3,[MoveDate],[ADJDate])))
> > > AS TransDate, tblInventory.ID,
> > > IIf([TransTypeID]=1,"Receiving",IIf([TransTypeID]=2,"BOL",IIf([TransTypeID]=3,"Move","Adjustment")))
> > > AS TransTypeName, tblAisle.Name AS Aisle, tblInventory.CustPO,
> > > tblInventory.Quantity, tblProduct.uID, tblProduct.ProductCode,
> > > tblProduct.Description, tblProduct.NWeight, tblInventory.DayCode,
> > > ([QUANTITY]*[NWEIGHT]) AS NETWEIGHT
> > > FROM tblCustomer INNER JOIN (tblAisle RIGHT JOIN ((tblMoveInv RIGHT JOIN
> > > (tblInvAdj RIGHT JOIN (tblReceiving RIGHT JOIN (tblBOL RIGHT JOIN
> > > tblInventory ON tblBOL.BOLNo = tblInventory.ID) ON tblReceiving.uID =
> > > tblInventory.ID) ON tblInvAdj.uID = tblInventory.ID) ON tblMoveInv.uID =
> > > tblInventory.ID) INNER JOIN tblProduct ON tblInventory.ProductID =
> > > tblProduct.uID) ON tblAisle.uID = tblInventory.AisleID) ON tblCustomer.uID =
> > > tblInventory.CustomerID
> > > WHERE (((tblCustomer.ShortName) Like "NESTLE"))
> > > ORDER BY tblInventory.CustomerID, tblInventory.CustPO,
> > > tblProduct.ProductCode, tblInventory.MoveOrder;
> > >
> > > I appreciate your assistance.
> > > samiam
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > Post the SQL of your query.
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "samiam" wrote:
> > > >
> > > > > Karl:
> > > > >
> > > > > My query includes a column where quantity * weight is placed. This always
> > > > > has a number in it. What I'm trying to do is if the summation of this column
> > > > > for a product is zero, exclude that product from the report. I've tried <>0,
> > > > > but I still get zero result products sent to the report.
> > > > >
> > > > > samiam
> > > > >
> > > > > "KARL DEWEY" wrote:
> > > > >
> > > > > > Use criteria in your totals query <> 0 and it not pull them.
> > > > > > --
> > > > > > KARL DEWEY
> > > > > > Build a little - Test a little
> > > > > >
> > > > > >
> > > > > > "samiam" wrote:
> > > > > >
> > > > > > > I have a report based on a query. I would like to exclude the items in the
> > > > > > > report that sum to zero in order to shorten my report and remove those items
> > > > > > > that have been "used." How do I exclude the detail records that will sum to
> > > > > > > zero in my report? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
Question
Notify me of replies