473,379 Members | 2,890 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

Linking Allen Browne's code to Northwind


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 ***
Nov 13 '05 #1
15 3025
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
Nov 13 '05 #2
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

Nov 13 '05 #3

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 ***
Nov 13 '05 #4
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

Nov 13 '05 #5

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

Thanks again,
jlig
*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #6
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

Nov 13 '05 #7
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 ***
Nov 13 '05 #8
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?

Nov 13 '05 #9
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 ***
Nov 13 '05 #10
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 ***

Nov 13 '05 #11
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 ***
Nov 13 '05 #12

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 ***
Nov 13 '05 #13
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 ***
Nov 13 '05 #14
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!

Nov 13 '05 #15

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 ***
Nov 13 '05 #16

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rob Davis | last post by:
I am familiar with VBA and the manual method of attaching/linking external data tables (File, Get External Data etc). I am also familiar with opening tables which exist in the current Access file,...
6
by: amywolfie | last post by:
Hi all: I am using Access 2002, and of course, when I copied the solution from the Solutions.mdb to link tables at startup, I got an area using the code supplied. I have since tried a variety...
1
by: Steven Britton via AccessMonster.com | last post by:
Follow Up question to the below posted by Allen Browne on 02/06/2005 - Could something like this work for a form that has a subform and the subform is a datasheet? ...
7
by: Swinky | last post by:
Mr. Browne's copy code on his web site has saved me. I have been struggling to copy a record with several related sub-form tables. I found code on his web site that copies a sub-form table,...
7
by: ApexData | last post by:
I am using the following code in my TabControl to manage subform loads. The code assigns the subForms SourceObject. - Do I also need code to DeAssign the SourceObject when leaving the Tab, I'm...
3
by: Photobug | last post by:
I have downloaded Allen Browne's function TableInfo() and am getting a ByRef Type Mismatch error when I try to execute it. I don't know if it is a reference problem or not, but my references for...
2
by: sara | last post by:
I use Allen Browne's Audit Trail code in everything I do and I love it. Recently, I've run into a problem I can't figure out. I have a database with about 35 lookup tables. I am creating an...
5
by: Patrick | last post by:
I have set the Navigation Pane so that it is not displayed on startup of my application. However, if I programmatically link to an external Access table, the navigation pane automatically...
6
by: babamc4 | last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne)...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.