Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 13th, 2007, 12:50 PM
Registered User
 
Join Date: Sep 2007
Location: Central City, Ky., USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Lookups

I used the lookup wizard to develop a drop-down list in one of my tables in an Access database. For example, when the list is opened in the table it shows something like: Part101, Part102, Part103, ..., if I wanted to make sure Part101 was always >=6, Part102 was always >=10, and Part103 was always >=12...; what would be the best way to achieve this. It would be helpful if when the items in the list were below the specified quantity the item would be listed in a file, etc. What would be the best way to develop this?

Best Regards,
  #2 (permalink)  
Old September 13th, 2007, 02:16 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How is data being entered in the table? Generally in SQL you would do this with a trigger, but in Access you can do it with events on data entry forms. Do you have a form for data entry in this table?

If you do have a form, you would add business rules to the Before Insert and Before Update events, either asking the user to select a different value, or writing the information to a file? Do you want a file generated when certain threshholds are not met, and to allow the user to make the entry without reaching the threshhold?

Anyway, it can be done with events, as any top Access expert will tell you. Can you provide more information?



mmcdonal
  #3 (permalink)  
Old September 18th, 2007, 04:38 PM
Registered User
 
Join Date: Sep 2007
Location: Central City, Ky., USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is more information about my question.

PartNumberID PartDescription Quantity (This is Table 1)

SalesID PartDescription Price (This is Table 2)

If you have two tables with the fields above: Table 1, PartDescription field is linked to Table 2 Part Description field with a one-to-many relationship. If the first entry in Table 1 was PartNumberID 1, PartDescription Hexnut1" and quantity >25, then Table 2 could have this PartDescription field with Hexnut1" listed in many records; how could you have the application alert you when the quantity is less than or equal to 25, since you always want to have more than 25 Hexnut1" in stock. How could you have this item automatically listed in an Order table, etc. when stock gets below the quantity specified in Table 1?

Thanks
  #4 (permalink)  
Old September 19th, 2007, 08:13 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh, you want in stock minimum qty.

You want to manage this using forms. You would have users do data entry in forms.

The tables should be like this, however:

Table1
PartNumberID - PK
PartDescription - text
Cost - Currency
Price - Currency
MinQty - Number - this is where you keep your minimum quantity value.

Table2
SalesID - PK
PartNumberID - FK - Cascade Updates = No
Quantity - Number
Received - Yes/No (only available to Shipping / Receiving, not Order Entry)
dtDate - DateTime - Default Value = Now()

Now I think you have enough data to do this. There may be other consierations.

From this you can create a query that shows when inventory comes in, and when sales are made, and the resulting QTY on hand at any given point. Which means you can also show what the value is as soon as an order is made.

This query will show you all your incoming and out going transactions in the order that they occur:

SELECT Table1.PartNumberID, Table1.PartDescription, Table2.SalesID, Table2.Quantity, Table2.Received, Table2.dtDate, Table1.MinQTY
FROM Table1 INNER JOIN Table2 ON Table1.PartNumberID = Table2.PartNumberID
ORDER BY Table2.dtDate

Here is a query to show you only items received:

qryPartsRecvd (Query3)
SELECT Table1.PartNumberID, Sum(Table2.Quantity) AS SumOfQuantity, Table2.Received
FROM Table1 INNER JOIN Table2 ON Table1.PartNumberID = Table2.PartNumberID
GROUP BY Table1.PartNumberID, Table2.Received
HAVING (((Table2.Received)=Yes));

And one to show only items sold:

qryPartsSold (Query4)
SELECT Table1.PartNumberID, Sum(Table2.Quantity) AS SumOfQuantity, Table2.Received
FROM Table1 INNER JOIN Table2 ON Table1.PartNumberID = Table2.PartNumberID
GROUP BY Table1.PartNumberID, Table2.Received
HAVING (((Table2.Received)=No));

Then this will give you the quantity on hand for each part number:

qryPartsOnHand (Query5)
SELECT Query3.PartNumberID, ([Query3].[SumOfQuantity]-[Query4].[SumOfQuantity]) AS OnHand
FROM Query4 RIGHT JOIN Query3 ON Query4.PartNumberID = Query3.PartNumberID;

And THIS, will give you the balance compared to your set minimum:

qryOrderOrNot (Query6)
SELECT Table1.PartNumberID, Table1.PartDescription, Table1.MinQTY, Query5.OnHand, (IIf([OnHand]<=[MinQTY],"Order","Okay")) AS Warning
FROM Table1 RIGHT JOIN Query5 ON Table1.PartNumberID = Query5.PartNumberID;

So with this initial data from Table1:

PartNumberID PartDescription Cost Price MinQTY
1 HexNut1 $0.10 $0.20 6
2 HexNut2 $0.11 $0.25 8

And this data from Table2:

SalesID PartNumberID Quantity Received dtDate
1 HexNut1 12 Yes 9/19/2007 8:31:17 AM
2 HexNut1 4 No 9/19/2007 8:31:32 AM
3 HexNut1 2 No 9/19/2007 8:31:38 AM
4 HexNut2 12 Yes 9/19/2007 8:31:44 AM
5 HexNut2 2 No 9/19/2007 8:31:51 AM

I get this data in my query6:

PartNumberID PartDescription MinQTY OnHand Warning
1 HexNut1 6 6 Order
2 HexNut2 8 10 Okay

Now, how do you want to package this info? What you could do is run Query6 for the part number you are on after every order is made (After Insert AND After Update) and then run a routine to email you the results if "Warning = Order".

Did any of this help?





mmcdonal
  #5 (permalink)  
Old September 19th, 2007, 02:30 PM
Registered User
 
Join Date: Sep 2007
Location: Central City, Ky., USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you mmcdonal for your time in answering my question. I believe this will help me achieve my goal for my application. Do you do any consulting for database designers?

Best regards
  #6 (permalink)  
Old September 20th, 2007, 07:26 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I do consulting on this forum. That is free. I am also working towards becoming a data architect, but am waiting till I finish my degree this December before I do that professionally. If you have any other issues, please use my contact information by clicking on my username and sending an email directly to me. Remember to put yout return email address in it. I can't remember if it sends your email address... take a look.

mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Lookups elsewhere in the XML ashcarrot XSLT 5 July 17th, 2006 11:09 AM
Multiple lookups in same table kev_79 Access 3 February 15th, 2004 05:38 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.