Inventory control (modifying Allen Brown's procedure

[peskywinnets]

Inventory control (modifying Allen Browne's procedure

So I need to track inventory, a bit of googling reveals this...

http://allenbrowne.com/appinventory.html

which is fine (it works well) ....but it only works if there's been at least one sale for each product being queried (i.e. there must be an entry in InvoiceDetail table for the product)....that's no good, because when you first stock a new item, you've not made any sales for that product, so his 'Quantity On Hand' code won't work in that instance ....unless I'm missing something obvious?

So I think the code needs tweaking, but I need some guidance on how...I reckon it's failing because of the relationships.....



...I'm thinking that unless there's an entry for the productID in the 4 rightmost tables then the product won't show when passing to his function - but I could be wrong! Is there a way of spoofing (or working around) for the situation I mention (new product just in, no customer sales yet)

 

[Ranman256]

need to make some of these an OUTER join. To pick up the items the did not have a sale.

 

[peskywinnets]

Thanks...yes, that was the problem

Next issue is that the column on the right below, is problematic (if I remove the column the query runs fine)...




here's the first error (I get two more follow on parameter related prompts for the other two references in that right column)...

 

[sneuberg]

Try using the builder to create the expression. Choose the fields from the query so that they end up qualified. So let's say the name of this query is Query1 then the expression would end up something like

Expr: [Query1].[LocalStock] + [Query1]......

Save, close and reopen the query before you test it again. Sometimes that seems to help.

 

[peskywinnets]

Thanks Steve ...but that didn't resolve (some good came out of it though I hadn't used the expression builder in a query prior...didn't even know it was there as an option...as I've only ever used in Reports)

I think the problem is coming about because one of my query's columns calls a function (to calculate the stock quantity 'On Hand') ...but the query is also trying to do a calculation in another column based on the returned result of that function.

My 'workaround' was to use another follow query & do the calculation needed in that instead ....no error when I go that way (so I guess this is a 'sequence' type of problem - in other words, it takes a finite amount of time to carry out the function, but the other column in the query expected it to have the data instantly?)

 

[sneuberg]

Another way I would have tried would be to make a function for Available. It could reuse the OnHand function in it. If the function for Available only had raw fields as arguments I think it would have to work. One of the neat things about putting these in functions is that you have all the facilities of the debugger. You can put Debug.Prints in the code and see what the function is doing when the query executes.

But if you are happy with the workaround you might as well press on with it.

 

[CJ_London]

when you call a field something other than what it is called in the table it is an alias - so localstock is an alias.

With group by queries the structure of the sql is

SELECT
FROM
WHERE
GROUP BY
HAVING

SELECT can only reference the table names from the FROM
GROUP BY can only group by what is selected
So at the time you are trying to reference localstock, that alias has not been created.

there are two solutions.

either replace localstock with code which creates the localstock value and the other aliased values

or save the group by query without your available calculation and create a new query based on the group by query

By the same token - WHERE can only be applied to fields in the source tables, whilst HAVING refers to any aliases created. So when designing group by queries, if you want to find values relating to a particular SKU if you put the criteria in the SKU Group By columns, the query will load all the SKU's then filter for the one you want using HAVING.

A more efficient way is to have the SKU in the query twice - once as a GROUP BY and show (as you have at the moment) and a second time with WHERE (show will automatically untick) and the criteria. Then the query will only load records relating to the SKU

 

[peskywinnets]

or save the group by query without your available calculation and create a new query based on the group by query

Thanks CJ - that's essentially the solution I've done (it's less pain from my Noob's perspective!)

But if you are happy with the workaround you might as well press on with it.

A useful tip...at the moment I need to crack on (& since it's working blah blah).

I've just come up against another challenge....how to put all the different aspects of inventory control in date order.

Essentially I have 3 tables with ongoing 'stock action's being added to the table (stock acquired, stock sold, stock takes carried out)....the date is always captured for any changes made to each of these tables, what I want to do now is show the sequence in date order of the actions carried out across all 3 tables (then I can do a stock item history) ...but how do I sort on three different table date fields so that they are all in date sequence?

So I seek a way of combining the datestamps for all 3 tables into one (so I can then sort on the combined dates) ...but suffering a severe case of brain fuzz wrt how I'd achieve this!

 

[Lightwave]

Looks to me like you need to do a Union Query and at the end sort on date.

A union query combines the result sets of several similar select queries. Why not create a select query for each table and ensure that you have the same number of output fields in the same order and with the same or compatible data types and names and then do a union query.

A union query is SQL specific. SQL specific queries cannot be displayed in Design View and you have to go into the SQL window to write them.

Here's a simple instruction video for MS Access

Union Query Youtube

 

[peskywinnets]

that sounds exactly what I need - thanks!

 

[gemma-the-husky]

can you not just initialise every product by adding a "starting stock" quantity of zero?
then you won't have any with zero records.


I do inventory with a single stock movements table, with fields for qty, date, movementtype, and anything else necessary.

opening qty,
adjustment
movement in - positive qty
movement out - negative qty

you get the qty on hand by summing all the records up to a given date. (maybe between dates). no union queries, no multiple queries. Just a single sum.

 

[peskywinnets]

can you not just initialise every product by adding a "starting stock" quantity of zero?

then you won't have zero records.

Good spot! ....& indeed this solution occurred to me last night (when I revisited this issue), so that's exactly what I've just done & it works a treat

Many thanks.

I'll mark the thread as solved.