Inventory Assembly/Sub-component problem (query)

B

Bob Anderson

Earlier I posted Inventory Assembly/Sub-component question:
I'm going to use a McDonald's analogy of my problem because it's familiar. I
can easily inventory the comings and goings of my inventory items. I make a
Big Mac and it is added to the inventory total. I sell a Big Mac and it is
deleted from the inventory total. I can easily enough inventory the comings
and goings of Big Mac meals too (exactly the same way as anything else).
What I can't see how to do is correctly handle the sale of a Big Mac Meal.
How do I automatically deduct one Big Mac, one fries and one drink (and the
Meal) every time I sell a Big Mac Meal? By the way, I am working in Access
97 with a database created by the Database Wizard for Inventory Control.

I'm still using Access 97. I still have a problem of getting the correct
inventory count, although I have a better understanding of the problem. I
have a query that will show me two records. The numbers, if I could only add
them together, would give the desired result. Here is that query:
SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Sum((nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage]))) AS [Units in
Stock], Sum((nz([UnitsOrdered])-nz([UnitsReceived]))) AS [Units on Order],
Products.ProductID
FROM Products INNER JOIN ((ItemsForSale INNER JOIN Assemblies ON
ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID) INNER JOIN [Inventory
Transactions] ON ItemsForSale.ItemForSaleID = [Inventory
Transactions].ItemForSaleID) ON Products.ProductID = Assemblies.ProductID
GROUP BY ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Products.ProductID
HAVING (((Products.ProductID)=[Enter ItemForSaleID]))
ORDER BY Products.ProductID;

I thought I could write a sub or function that would allow me to "get" the
two numbers and add them. But, when I use the SQL text from my working query
(strSQL= my pasted text) VB is rejecting it saying; "Compile error: Expected
end of statement". Is this enough information? Can anyone help with an idea
out of this mess?
 
N

Nikos Yannacopoulos

Bob,

On your first question: I haven't really done something similar, so this is
not experience speaking, but I do have an idea you might want to evaluate.
I would logically differenciate Commercial Products from Stock Keeping
Units, and join the two by means of a table with the SKU's that make up a
CP. So, to refer to your example, assuming:

tblCommProducts
CP_ID CP_Name
1 Big Mac
2 Fries
3 Coke
4 Big Mac Meal
....

tblSKUs
SKU_ID SKU_Name
1 Big Mac
2 Fries
3 Coke
....

tblBillOfMaterials
CP_ID SKU_ID SKU_Qty
1 1 1
2 2 1
3 3 1
4 1 1
4 2 1
4 3 1
....

Following that, I would "sell" commercial products, and do all the stock
manipulation at SKU level; that is I would order, receive, write-off etc.
SKU's, while for a sale I would subtract from the stock the quantity of
SKU's associated to the commercial product sold (from tblBillOfMaterials).
You will notice that my tblBillOfMaterials I have a field SKU_Qty, which is
necessary if you were to sell, say, a McCouple Meal comprising two Big Macs
and two Cokes ect.

On your SQL syntax issue: it is not very clear to me what you are trying to
do with it: the expression is clearly a select query, which cannot be run as
is through VB code (through VB code you can only run action SQL queries,
e.g. make table, append, delete, update). A select query expression can only
be used to define, say, a form or report recordset, or open a recordset to
perform some action on. Which is the case?
Also, the part [Enter ItemForSaleID] works fine for getting a user parameter
in query design, but not in SQL. To do this in VB code you would have to use
an inputbox (or a control on a form) first, to get the user's input and
assign it to a variable, and then use that variable in your SQL statement
(leaving it out of the quotes).
At any rate, if you post your code you stand a far better chance of getting
help on this.

HTH,
Nikos

Bob Anderson said:
Earlier I posted Inventory Assembly/Sub-component question:
I'm going to use a McDonald's analogy of my problem because it's familiar. I
can easily inventory the comings and goings of my inventory items. I make a
Big Mac and it is added to the inventory total. I sell a Big Mac and it is
deleted from the inventory total. I can easily enough inventory the comings
and goings of Big Mac meals too (exactly the same way as anything else).
What I can't see how to do is correctly handle the sale of a Big Mac Meal.
How do I automatically deduct one Big Mac, one fries and one drink (and the
Meal) every time I sell a Big Mac Meal? By the way, I am working in Access
97 with a database created by the Database Wizard for Inventory Control.

I'm still using Access 97. I still have a problem of getting the correct
inventory count, although I have a better understanding of the problem. I
have a query that will show me two records. The numbers, if I could only add
them together, would give the desired result. Here is that query:
SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Sum((nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage]))) AS [Units in
Stock], Sum((nz([UnitsOrdered])-nz([UnitsReceived]))) AS [Units on Order],
Products.ProductID
FROM Products INNER JOIN ((ItemsForSale INNER JOIN Assemblies ON
ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID) INNER JOIN [Inventory
Transactions] ON ItemsForSale.ItemForSaleID = [Inventory
Transactions].ItemForSaleID) ON Products.ProductID = Assemblies.ProductID
GROUP BY ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Products.ProductID
HAVING (((Products.ProductID)=[Enter ItemForSaleID]))
ORDER BY Products.ProductID;

I thought I could write a sub or function that would allow me to "get" the
two numbers and add them. But, when I use the SQL text from my working query
(strSQL= my pasted text) VB is rejecting it saying; "Compile error: Expected
end of statement". Is this enough information? Can anyone help with an idea
out of this mess?
Click to expand...
 
B

Bob Anderson

About the SQL syntax, I want to define a recordset. Yeah, I know the
parameter part is not good in VB. I copied the SQL from my query rather than
from VB to paste into my post. In my VB code I simply used 8 for the product
ID to see if I could get things rolling. Again, my intention was to use the
VB sub to sum two fields of my query results. That would give the answer I
want. Did you find my previous post with a post subject intentionally very
similar? It gives more information than I included in this post.

I will investigate your suggested idea. I think I have done something very
similar, because I set up a products table as well as an items-for-sale
table that are joined by an assemblies table that shows a quantity factor
for each product.

Bob Anderson

Nikos Yannacopoulos said:
Bob,

On your first question: I haven't really done something similar, so this is
not experience speaking, but I do have an idea you might want to evaluate.
I would logically differenciate Commercial Products from Stock Keeping
Units, and join the two by means of a table with the SKU's that make up a
CP. So, to refer to your example, assuming:

tblCommProducts
CP_ID CP_Name
1 Big Mac
2 Fries
3 Coke
4 Big Mac Meal
...

tblSKUs
SKU_ID SKU_Name
1 Big Mac
2 Fries
3 Coke
...

tblBillOfMaterials
CP_ID SKU_ID SKU_Qty
1 1 1
2 2 1
3 3 1
4 1 1
4 2 1
4 3 1
...

Following that, I would "sell" commercial products, and do all the stock
manipulation at SKU level; that is I would order, receive, write-off etc.
SKU's, while for a sale I would subtract from the stock the quantity of
SKU's associated to the commercial product sold (from tblBillOfMaterials).
You will notice that my tblBillOfMaterials I have a field SKU_Qty, which is
necessary if you were to sell, say, a McCouple Meal comprising two Big Macs
and two Cokes ect.

On your SQL syntax issue: it is not very clear to me what you are trying to
do with it: the expression is clearly a select query, which cannot be run as
is through VB code (through VB code you can only run action SQL queries,
e.g. make table, append, delete, update). A select query expression can only
be used to define, say, a form or report recordset, or open a recordset to
perform some action on. Which is the case?
Also, the part [Enter ItemForSaleID] works fine for getting a user parameter
in query design, but not in SQL. To do this in VB code you would have to use
an inputbox (or a control on a form) first, to get the user's input and
assign it to a variable, and then use that variable in your SQL statement
(leaving it out of the quotes).
At any rate, if you post your code you stand a far better chance of getting
help on this.

HTH,
Nikos

Bob Anderson said:
Earlier I posted Inventory Assembly/Sub-component question:
I'm going to use a McDonald's analogy of my problem because it's
Click to expand...
familiar.
I
can easily inventory the comings and goings of my inventory items. I
Click to expand...
make
a
Big Mac and it is added to the inventory total. I sell a Big Mac and it is
deleted from the inventory total. I can easily enough inventory the comings
and goings of Big Mac meals too (exactly the same way as anything else).
What I can't see how to do is correctly handle the sale of a Big Mac Meal.
How do I automatically deduct one Big Mac, one fries and one drink (and the
Meal) every time I sell a Big Mac Meal? By the way, I am working in Access
97 with a database created by the Database Wizard for Inventory Control.

I'm still using Access 97. I still have a problem of getting the correct
inventory count, although I have a better understanding of the problem. I
have a query that will show me two records. The numbers, if I could only add
them together, would give the desired result. Here is that query:
SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Sum((nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage]))) AS
Click to expand...
[Units
in
Stock], Sum((nz([UnitsOrdered])-nz([UnitsReceived]))) AS [Units on Order],
Products.ProductID
FROM Products INNER JOIN ((ItemsForSale INNER JOIN Assemblies ON
ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID) INNER JOIN [Inventory
Transactions] ON ItemsForSale.ItemForSaleID = [Inventory
Transactions].ItemForSaleID) ON Products.ProductID = Assemblies.ProductID
GROUP BY ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Products.ProductID
HAVING (((Products.ProductID)=[Enter ItemForSaleID]))
ORDER BY Products.ProductID;

I thought I could write a sub or function that would allow me to "get" the
two numbers and add them. But, when I use the SQL text from my working query
(strSQL= my pasted text) VB is rejecting it saying; "Compile error: Expected
end of statement". Is this enough information? Can anyone help with an idea
out of this mess?
Click to expand...
Click to expand...
 
B

Bob Anderson

Oh how frustrating. My syntax works just fine when it is on a single line!
I wasn't even running this code. I was just typing it into the sub!
My code (will word-wrap):
strSQL = "SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Sum(nz([UnitsReceived]) - nz([UnitsSold]) - nz([UnitsShrinkage])) AS [Units
in Stock], (Sum((nz([UnitsOrdered])-nz([UnitsReceived])))) AS [Units on
Order], Products.ProductID FROM Products INNER JOIN ((ItemsForSale INNER
JOIN Assemblies ON ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID)
INNER JOIN [Inventory Transactions] ON ItemsForSale.ItemForSaleID =
[Inventory Transactions].ItemForSaleID) ON Products.ProductID =
Assemblies.ProductID GROUP BY ItemsForSale.ItemForSaleID,
ItemsForSale.ItemName, Products.ProductID HAVING (((Products.ProductID) =
8)) ORDER BY Products.ProductID;"

My code, from module:
strSQL = "SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName, _
Sum(nz([UnitsReceived]) - nz([UnitsSold]) - nz([UnitsShrinkage])) AS [U
nits in Stock], _
(Sum((nz([UnitsOrdered])-nz([UnitsReceived])))) AS [Units on Order],
Products.ProductID _
FROM Products INNER JOIN ((ItemsForSale INNER JOIN Assemblies _
ON ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID) INNER JOIN
[Inventory Transactions] _
ON ItemsForSale.ItemForSaleID = [Inventory Transactions].ItemForSaleID) _
ON Products.ProductID = Assemblies.ProductID GROUP BY
ItemsForSale.ItemForSaleID, _
ItemsForSale.ItemName, Products.ProductID HAVING (((Products.ProductID) =
8)) _
ORDER BY Products.ProductID;"

Good Night All!

Bob Anderson said:
About the SQL syntax, I want to define a recordset. Yeah, I know the
parameter part is not good in VB. I copied the SQL from my query rather than
from VB to paste into my post. In my VB code I simply used 8 for the product
ID to see if I could get things rolling. Again, my intention was to use the
VB sub to sum two fields of my query results. That would give the answer I
want. Did you find my previous post with a post subject intentionally very
similar? It gives more information than I included in this post.

I will investigate your suggested idea. I think I have done something very
similar, because I set up a products table as well as an items-for-sale
table that are joined by an assemblies table that shows a quantity factor
for each product.

Bob Anderson

Nikos Yannacopoulos said:
Bob,

On your first question: I haven't really done something similar, so this is
not experience speaking, but I do have an idea you might want to evaluate.
I would logically differenciate Commercial Products from Stock Keeping
Units, and join the two by means of a table with the SKU's that make up a
CP. So, to refer to your example, assuming:

tblCommProducts
CP_ID CP_Name
1 Big Mac
2 Fries
3 Coke
4 Big Mac Meal
...

tblSKUs
SKU_ID SKU_Name
1 Big Mac
2 Fries
3 Coke
...

tblBillOfMaterials
CP_ID SKU_ID SKU_Qty
1 1 1
2 2 1
3 3 1
4 1 1
4 2 1
4 3 1
...

Following that, I would "sell" commercial products, and do all the stock
manipulation at SKU level; that is I would order, receive, write-off etc.
SKU's, while for a sale I would subtract from the stock the quantity of
SKU's associated to the commercial product sold (from tblBillOfMaterials).
You will notice that my tblBillOfMaterials I have a field SKU_Qty, which is
necessary if you were to sell, say, a McCouple Meal comprising two Big Macs
and two Cokes ect.

On your SQL syntax issue: it is not very clear to me what you are trying to
do with it: the expression is clearly a select query, which cannot be
Click to expand...
run
as
is through VB code (through VB code you can only run action SQL queries,
e.g. make table, append, delete, update). A select query expression can only
be used to define, say, a form or report recordset, or open a recordset to
perform some action on. Which is the case?
Also, the part [Enter ItemForSaleID] works fine for getting a user parameter
in query design, but not in SQL. To do this in VB code you would have to use
an inputbox (or a control on a form) first, to get the user's input and
assign it to a variable, and then use that variable in your SQL statement
(leaving it out of the quotes).
At any rate, if you post your code you stand a far better chance of getting
help on this.

HTH,
Nikos

Bob Anderson said:
Earlier I posted Inventory Assembly/Sub-component question:
I'm going to use a McDonald's analogy of my problem because it's
Click to expand...
familiar.
I
can easily inventory the comings and goings of my inventory items. I
Click to expand...
make
a
Big Mac and it is added to the inventory total. I sell a Big Mac and
Click to expand...
Click to expand...
it
is (and
the
Click to expand...
problem.
I
have a query that will show me two records. The numbers, if I could
Click to expand...
only
add
them together, would give the desired result. Here is that query:
SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Sum((nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage]))) AS
Click to expand...
[Units
in
Stock], Sum((nz([UnitsOrdered])-nz([UnitsReceived]))) AS [Units on Order],
Products.ProductID
FROM Products INNER JOIN ((ItemsForSale INNER JOIN Assemblies ON
ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID) INNER JOIN [Inventory
Transactions] ON ItemsForSale.ItemForSaleID = [Inventory
Transactions].ItemForSaleID) ON Products.ProductID = Assemblies.ProductID
GROUP BY ItemsForSale.ItemForSaleID, ItemsForSale.ItemName,
Products.ProductID
HAVING (((Products.ProductID)=[Enter ItemForSaleID]))
ORDER BY Products.ProductID;

I thought I could write a sub or function that would allow me to "get" the
two numbers and add them. But, when I use the SQL text from my working query
(strSQL= my pasted text) VB is rejecting it saying; "Compile error: Expected
end of statement". Is this enough information? Can anyone help with an idea
out of this mess?
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

My code, from module:
strSQL = "SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName, _
Sum(nz([UnitsReceived]) - nz([UnitsSold]) - nz([UnitsShrinkage])) AS [U
nits in Stock], _
(Sum((nz([UnitsOrdered])-nz([UnitsReceived])))) AS [Units on Order],
Products.ProductID _
FROM Products INNER JOIN ((ItemsForSale INNER JOIN Assemblies _
ON ItemsForSale.ItemForSaleID = Assemblies.ItemForSaleID) INNER JOIN
[Inventory Transactions] _
ON ItemsForSale.ItemForSaleID = [Inventory Transactions].ItemForSaleID) _
ON Products.ProductID = Assemblies.ProductID GROUP BY
ItemsForSale.ItemForSaleID, _
ItemsForSale.ItemName, Products.ProductID HAVING (((Products.ProductID) =
8)) _
ORDER BY Products.ProductID;"
Click to expand...

Get into Debug mode, step through the code, and type

?StrSQL

in the immediate window after this line. I bet it will be missing some
blanks. The _ continuation character must occur after a blank, and
that blank is NOT part of the string - so I suspect you have (e.g.)

INNER JOIN AssembliesON

and

[Inventory Transactions]ON

with the syntactically required blanks missing.

I habitually build up such strings by concatenating readably-wide
string constants, e.g.

strSQL = "SELECT ItemsForSale.ItemForSaleID, ItemsForSale.ItemName," _
& " Sum(nz([UnitsReceived]) - nz([UnitsSold]) - " _
& "nz([UnitsShrinkage])) AS [Units in Stock]," _
....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inventory Assembly/Sub-component question 5
Inventory Transaction Problem 1
Crystal Reports and parameterized query 2
What is wrong with my SQL Statement 3
Crystal Reports with parameters...again. 2
AT WITS END ON QUERY TOTAL 34
Inventory Control Database Calculations 1
calculating quantities 5

Top