Linking Allen Browne's code to Northwind

[Jerry Alexander]

The Northwind Order Entry Application database is great!
-----------------------------------------
But one thing is lacking: Real-time Stock Qty calculation!
-----------------------------------------
I know that this topic has been discussed over & over in forums, but I
have yet to find anyone that has shown the following:
-----------------
1) Linking Allen Browne's code to Northwind? then an
2) Inventory report to show Products & Qty-On-Hand, etc.
-----------------

The Allen Browne link shows a sample table layout & code but I'm having
trouble seeing how to connect this code to a Northwind or Order Entry
database sample.

- His example has a tblInvoiceDetail table with a field called Quantity.
- How does this relate to my existing tblOrderDetails that also has
Quantity?
- Where or how do I create an Inventory Report?

Thanks in advance for your help!
jlig

*** Sent via Developersdex http://www.developersdex.com ***

[Lyle Fairfield]

Jerry Alexander wrote:

The Northwind Order Entry Application database is great!

At least one developer thinks that the Northwind and Solutions databases
are among the world's ugliest and most inefficient creations.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Allen Browne]

Northwind has the quantity of the product in the OrderDetails table, but it
lacks the tables to handle the acquisition of products when you receive them
in response to a purchase order.

You will need to add the tables for purchase orders and goods received
(acquisition) so that your database has both the in and out sides of the
equation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Alexander" <je***@gospeland.com> wrote in message
news:V5*************@news.uswest.net...

The Northwind Order Entry Application database is great!
-----------------------------------------
But one thing is lacking: Real-time Stock Qty calculation!
-----------------------------------------
I know that this topic has been discussed over & over in forums, but I
have yet to find anyone that has shown the following:
-----------------
1) Linking Allen Browne's code to Northwind? then an
2) Inventory report to show Products & Qty-On-Hand, etc.
-----------------

The Allen Browne link shows a sample table layout & code but I'm having
trouble seeing how to connect this code to a Northwind or Order Entry
database sample.

- His example has a tblInvoiceDetail table with a field called Quantity.
- How does this relate to my existing tblOrderDetails that also has
Quantity?
- Where or how do I create an Inventory Report?

Thanks in advance for your help!
jlig

[Jerry Alexander]

Allen, I have built business applications in the past but never had to
deal with Stock Quantity and Inventory On-Hand reports. So I admit to
being very weak when I look at your code and how to relate it to my
database.

I have taken your table & fields samples, created the tables & linked
the fields.

I also took out your tblProduct table & linked your other tables
directly to my existing tblProducts ProductID field. Everything looks
fine to me.
-------------------------------------
Now for my ignorance, how do I incorporate your code into my database?
-------------------------------------
- Do I create a form with a command buton?
- Do I create a new Module1 ?
-------------------------------------

Thanks for the help!
ps: Have you ever provided this piece of code as a sample mdb?
Thanks again,
jlig

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

The code is designed to work out the quantity of the product at the time you
need it.

For example, if you have a form with a ProductId text box, you could add a
text box and set its Control Source to:
=OnHand([ProductId])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Alexander" <je***@gospeland.com> wrote in message
news:kG***************@news.uswest.net...

Allen, I have built business applications in the past but never had to
deal with Stock Quantity and Inventory On-Hand reports. So I admit to
being very weak when I look at your code and how to relate it to my
database.

I have taken your table & fields samples, created the tables & linked
the fields.

I also took out your tblProduct table & linked your other tables
directly to my existing tblProducts ProductID field. Everything looks
fine to me.
-------------------------------------
Now for my ignorance, how do I incorporate your code into my database?
-------------------------------------
- Do I create a form with a command buton?
- Do I create a new Module1 ?
-------------------------------------

Thanks for the help!
ps: Have you ever provided this piece of code as a sample mdb?
Thanks again,
jlig

[Jerry Alexander]

Thanks for the reply. But where do I actually paste the code?

Thanks again,
jlig
*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Select the Modules tab of the Database window.
Click New.
Access opens a new module.
Paste the code in there.
Save the module with a name such as Module1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Alexander" <je***@gospeland.com> wrote in message
news:U9***************@news.uswest.net...

Thanks for the reply. But where do I actually paste the code?

Thanks again,
jlig

[Jerry Alexander]

OK, Thanks Allen for your patience & expertise.

I have added the Module1, created Query's & Forms.
I also added the text box with the record source =OnHand([ProductId]).

I can see in your code Date Range variables such as vAsOfDate.

Can I use those as record sources & labels for my reports & forms?

How does the code Prompt for a date range?

Thanks again,
jlig

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

The date is optional.

If you omit it, the code calculates the quantity as of today.
If you want to include in, you can use a date text box in the 2nd argument,
e.g.:
=OnHand([ProductId], [OrderDate])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Alexander" <je***@gospeland.com> wrote in message
news:Pn***************@news.uswest.net...

OK, Thanks Allen for your patience & expertise.

I have added the Module1, created Query's & Forms.
I also added the text box with the record source =OnHand([ProductId]).

I can see in your code Date Range variables such as vAsOfDate.

Can I use those as record sources & labels for my reports & forms?

How does the code Prompt for a date range?

[Jerry Alexander]

Allen, Getting closer, but still stuck.

I'm doing something wrong with my query. Here is the SQL on my query
which is the record source for my Product Form :
-------------------------
SELECT Products.ProductID, Products.ProductName, Products.UnitPrice,
tblAcqDetail.*, tblAcq.*, tblBrands.*
FROM tblDesc INNER JOIN ((tblBrands RIGHT JOIN Products ON
tblBrands.BrandID = Products.BrandID) INNER JOIN (tblAcq INNER JOIN
tblAcqDetail ON tblAcq.AcqID = tblAcqDetail.AcqID) ON Products.ProductID
= tblAcqDetail.ProductID) ON tblDesc.DescID = Products.DescID;
-------------------------
Problem is that Access will not allow me change any of the fields? I
know it's a join issue, but I can't figure it out.

The only difference in my table design & yours is that I replaced your
tblProducts with my Products table which is already filled with 500
product details such as Name, Desc, Price etc.

It also includes the 2 extra Look-Up tables called tblBrands & tblDesc
which are linked to the Products table.

I have created a Product Form with a field =OnHand([Products.ProductID])
& I can see that the value in this field is being calculating per your
code.

1) Can you tell me what the SQL needs to be my Form? or
2) Based on your original table design, a sample query?

Thanks so much, Sorry for the bother,
jlig

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Okay, you now have a query that is read-only.

You can fix that by dropping tables from the query that are not essential,
or possibly by changing the joins to INNER JOINS, or perhaps using only
arrow heads that point away from the table you want to be able to edit.

The product form might need just the Products table as its recordsource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Alexander" <je***@gospeland.com> wrote in message
news:Wz*****************@news.uswest.net...

Allen, Getting closer, but still stuck.

I'm doing something wrong with my query. Here is the SQL on my query
which is the record source for my Product Form :
-------------------------
SELECT Products.ProductID, Products.ProductName, Products.UnitPrice,
tblAcqDetail.*, tblAcq.*, tblBrands.*
FROM tblDesc INNER JOIN ((tblBrands RIGHT JOIN Products ON
tblBrands.BrandID = Products.BrandID) INNER JOIN (tblAcq INNER JOIN
tblAcqDetail ON tblAcq.AcqID = tblAcqDetail.AcqID) ON Products.ProductID
= tblAcqDetail.ProductID) ON tblDesc.DescID = Products.DescID;
-------------------------
Problem is that Access will not allow me change any of the fields? I
know it's a join issue, but I can't figure it out.

The only difference in my table design & yours is that I replaced your
tblProducts with my Products table which is already filled with 500
product details such as Name, Desc, Price etc.

It also includes the 2 extra Look-Up tables called tblBrands & tblDesc
which are linked to the Products table.

I have created a Product Form with a field =OnHand([Products.ProductID])
& I can see that the value in this field is being calculating per your
code.

1) Can you tell me what the SQL needs to be my Form? or
2) Based on your original table design, a sample query?

Thanks so much, Sorry for the bother,
jlig

*** Sent via Developersdex http://www.developersdex.com ***

[Jerry Alexander]

Allen, I can now enter data once I based it on the Products table only.
Thanks!

Here's where I'm at:

I have created these forms
--------------------------
- frmProducts (To see Products info & OnHand qty) (Sample data for
ProductID=18)
- frmtblStockTake (for entering Starting Inventory) (Sample data for
Quantity = 100)
- frmtblAcq (for entering Received Inventory)
(Sample data for Quantity = 3 of ProductID 18)
- frmtblInvoice (for test entering some Product lines)
(Sample data for Quantity = 10 of ProductID = 18)
I based each Form on the corresponding table except frmtblAcq. This one
is based on this SQL
SELECT tblAcq.*, tblAcqDetail.*
FROM tblAcq INNER JOIN tblAcqDetail ON tblAcq.AcqID=tblAcqDetail.AcqID;

I have entered the sample data into StockTakes, Acquisitions & Invoice
detail.

But the calculations seem strange.
When I open up the tblProducts for ProductID=18, the OnHands show 93?

Any ideas? Thanks for getting me this far.
jlig
*** Sent via Developersdex http://www.developersdex.com ***

[Jerry Alexander]

Allen, I think it's fine now!

I guess I did not give the tblProducts OnHand field time enough to
re-calculate?

Do I need to add a requery/refresh to that form?

Anyway, I'm going to try the tables & forms out with many records and
see what is happening. Then I need to create a sample Inventory report
showing Products & On-hands.

It feels really good to have a beginners understanding of this Inventory
Stock thing. I guess I took Inventory databases for granted in the past.

Thanks again!
jlig
*** Sent via Developersdex http://www.developersdex.com ***

[Jerry Alexander]

Allen, Here is the link to my sample database.
---------------------------------------
http://www.utteraccess.com/forums/do...?Number=703859

Thanks to you, I have the On-Hand Inventory working great. The only
thing left that is a problem is this:

Click on Orders/Enter New Order/Choose Employees. Then choose a Product
& Order Quantity & try to move to PO#. That's where I get the MS Jet
error.

I know it's my SQL query for this subform, but I've tried every possible
join & can't get it to work.

Here's the SQL behind the subform:
-------------------------------
SELECT DISTINCTROW [Order Details].*,
CLng(([Quantity]*[UnitPrice])*(1-[Discount])*100)/100 AS [Line Total],
tblInvoice.InvoiceDate, tblInvoice.InvoiceNote
FROM tblInvoice INNER JOIN [Order Details] ON tblInvoice.InvoiceID =
[Order Details].InvoiceID;

Any help with this with be much appreciated!

Thanks in advance for your help!
Jerry

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Jerry, as you can probably appreciate, we are not able to download
everyone's databases and fix them all for free.

Sounds like you are making good progress though, so keep going and you will
solve it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry Alexander" <je***@gospeland.com> wrote in message
news:2P***************@news.uswest.net...

Allen, Here is the link to my sample database.
---------------------------------------
http://www.utteraccess.com/forums/do...?Number=703859

Thanks to you, I have the On-Hand Inventory working great. The only
thing left that is a problem is this:

Click on Orders/Enter New Order/Choose Employees. Then choose a Product
& Order Quantity & try to move to PO#. That's where I get the MS Jet
error.

I know it's my SQL query for this subform, but I've tried every possible
join & can't get it to work.

Here's the SQL behind the subform:
-------------------------------
SELECT DISTINCTROW [Order Details].*,
CLng(([Quantity]*[UnitPrice])*(1-[Discount])*100)/100 AS [Line Total],
tblInvoice.InvoiceDate, tblInvoice.InvoiceNote
FROM tblInvoice INNER JOIN [Order Details] ON tblInvoice.InvoiceID =
[Order Details].InvoiceID;

Any help with this with be much appreciated!

[Jerry Alexander]

No problem, I'll use your previous advice & remove a field or two to get
the last query working. My reason for posting a sample was to help
others out there who need to see specifics on Inventory control, Qty on
Hand etc.

Thanks again & again,
jerry


*** Sent via Developersdex http://www.developersdex.com ***