General Access Queries - stock etc.

[DanielCrook]

Hi,

I'm in the middle of constructing a database for a sole trader and am having some difficulties with certain bits. My relationships are based on 5 main tables: Customers, Products, Categories (of products), Orders and Sales.

Customers, Products and Categories go straight through to create forms for these areas. The Order form is constructed of fields based on a query (Order Information) linking Orders with Customers, and an Order Sub form based on a query (Order Details information) linking Order Details with Products.
The Sales forms work in the same way as for the Orders.

The database is nearly finished but I am having the following problems, which may need the use of Visual Basic of which I have little knowledge:

1) No warning for stock level attaining a given minimum stock limit. (eg. 5)
- In the Product form, the only basis for stock control is a field called Amount in Stock.
- When scrolling through records in Products, how do set up a pop-up message box / dialogue box to stop at those products with stock less than 5, and it saying: "Order level is less than 5. Please re-order", and then simply clicking on OK for it to continue going through records? It's not the Validation rule and text for any field, because that's when entering data only!

2) No control of product selection when taking orders
- In my Order form, when selecting the products to be ordered in the Order sub-form, it allows me to select a product/s and type in any amount of stock regardless of the fact that there may not be enough in stock of that product in the Products form (Amount in Stock field). There is no control. How do I do this?

3) On the decision that stock can be updated, say, at the end of every week, (i.e. Order ID from 1-8, and Order date from ____ to _______), the query will be run to update stock for those records in Orders and then send them via an Append Query to another table (Orders Archive and Order Details Archive), and finally run a Delete query to delete them from the Orders form. THIS IS THE ONLY LOGICAL WAY I FOUND.

Problems: 1) Stock can be updated more than once from a record. How do I prevent that from happening?
2) What if the customer has not paid for an order? Do I have to set a block for the field Date Paid : Yes/No, or otherwise stock will be updated for those records where the customer has not paid, and hence stock may not have to be updated after all.

4) Based on the database template Northwind, I tried to set up "Sales by Year", but from orders made. I based it on the Order Details query, where Order ID and Sub Total will be the only necessary fields. I set up a Sales by Year dialogue box as a form, as in Northwind, which would be appear to set the beginning and end date for the year.
I had to add the criteria: Is Not Null And Between [Forms]![Sales by Year Dialog]![Beginning Date] And [Forms]![Sales by Year Dialog]![Ending Date]
For the Sales by Year Report, I created the report, copied the Event Procedures for OnOpen and OnClose. I had to then set up the Utility functions module as the other procedures were not recognising certain parts of the other procedures.
I also copied the event procedures for OnClick for the OK and Cancel buttons on the Sales by Year dialogue form.
It does run in some way but it doesn't do what the Norwthwind one does.. the report does not open either…all a bit of a mess. I checked the procedures, but cannot find any errors. Can you help me with this?

Sorry for the long message… but I would appreciate help from anyone for these problems.

Thankyou.

Daniel Crook.

 

[Bastien]

Hi Daniel,

Let's look at this one at a time.
1. Check stock minimum livels:
Create a table called MinStockLevels and link it to the products table with the productID. Then create a field to hold the min stock level for each product. To check if the level of stock warrants reordering - use an autoexec macro to run a query to see if the stock level in the Products table is less than the MinStockLevel amount. Then generate a report that lists the results of the query. (note that the macro should call the report which automatically calls the query if you choose to go this route)
This is simpler than having a pop up box and provides a definte list as to what needs to be reordered.

2. Control when taking orders:
To check to see if there is enough QOH (quantity on hand) of stock there are a couple of ways to do this:
1. In the field where the order is placed, create the dropdown to show a set of numbers (the amount ordered) with the max amount being the QOH. This can be done using VBA code in the background to create an array based on 1 to QOH;
2. Using a text box, use the BeforeUpdateEvent to run query to compare what the requested order amount is (the value in the text box) to the QOH. Then popup a message box to tell the user that the ordered amount can't be filled as there are only Q items in stock. (The question then becomes do you allow ordered to be backlogged and how will you track that, another table, another query and another report)
Obviously the first solution is neater, but it depends on you like to run things.

The commom element here is that the QOH field needs to be dymanic. You must update the QOH every time the order is filled , otherwise the stock problems become way to confusing. This is point three.
3. Update the stock amounts (2 parts)
1. Design form to update the stock based on new quantity received by you to add to inventory. Then you need to write a function that will add the new inventory received to the existing QOH to get a new total and update the QOH field.
2. Every time an order is confirmed, whether paid or not, delivered or not, you need to remove that orders products desired (the number a particular product that the buyer wants) from the QOH. This function is essentially the reverse of the adding stock to inventory in part 1.

I would suggest learning the VBA code to do this, it will be cleaner and quicker to manipulate the amounts through SQL than a query, but this will be up to you.

One thing I would recommend is to separate the QOH from the products table. Place it in another table, so there will be less data to work with in the queries (cleaner to work with)

4. Sales per year
I would set up a table with the OrderID, CustomerId, SubTotal, Taxes, Delivery Fees (etc) and for every order that is tagged as Paid, update this table with the above info. Keeping it as a separate table will act as a double check and allow for other reports as well, like customers with repeat orders and other sales anaylsis features. It will also allow for reports that can tabulate sales by month, or other time periods if you run the report off of a query and use the query to provide the input boxes for the reports.

There are other changes that may need to be made when you start with this, like the backorder issue, feel free to contact me and we'll see what we can come up with.

hth

Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[Bastien]

Hey Daniel

Here is my email

<a href=&quot;mailto:Bntkoert@aol.com&quot;>Bntkoert@aol.com</a>

Bastien Bastien

There are many ways to skin this cat,
but it still tastes like chicken