Solved

Sales and Inventory Control problem.

Posted on 2004-08-29
24
571 Views
Last Modified: 2012-05-05
I am setting up a sales and inventory management database, but being a novice at using Access I've encountered the following problem:

I have three tables, Categories, Items, and ItemSales.
  Categories is comprised of CategoryID and CategoryName
  Items has a number of fields relevant to the item to be sold, including CategoryID and the number of items originally in stock
  ItemSales basically stores items that have been sold, price, number of items sold, customer, etc.

I also have a queries that sum the number of items sold in each and determine how many items are left in stock.

My problem is this: I want to display a form that allows me to selet the category of merchandise I want to sell, then shows what items are in that category that have not been sold out, and then, after selecting which item(s) I want to sell, allows me to fill in the selling price and number of items sold. Ideally, the form would allow multiple rows for purchase of multiple items, would total each row and total the sale. Upon hitting a submit button, the results would be added to the ItemSales table and the number of items in stock in the Items table would be updated. At the same time, the submit button would open an iinvoice for printing.

If this could happen all on one form, that would be great. If not I'm open to suggestions as I've been able to put together pieces of the puzzle, but not the whole thing.

Thanks.
RWW
0
Comment
Question by:PstWood
  • 12
  • 11
24 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11925568
That's a lot you're asking for in one question.

First of all, how do you want users to select which items they want to sell? What you have above is a list of all items which the users then go down filling the number of sales for each item. Would you be open to using a combo box instead, to select the item? That is, the form is initially blank, and each line has a combo box that only shows the items available in the category you have selected.
0
 

Author Comment

by:PstWood
ID: 11925698
Ideally, there would be one combobox for Category selection and another for Item selection on each line, then text boxes for fill in of price(it's not a fixed number) and number of items with a total that automagically updates when the price and number of items were filled in. Barring that, I could go with a combobox of only the Items table. Initially at least it won't be so extensive as to make filling in the form a chore.

Thanks.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11925756
Hmm, tricky to filter for Category on each line of a continuous form. Not impossible though.

First, you need to create a continuous form based on your ItemSales form. The ItemID should be bound to a combo box that selects all of the relevant information from the Items table. The main difficulty here is - does your ItemSales table include the Category field? If it doesn't you can't filter on each table (a control on a continuous form must be bound to a field, otherwise it doesn't work properly).

A better solution might be to create a temporary table identical to ItemSales, and that includes the Category field. You can base your continuous form on this table instead. This also means that users aren't entering items into the "real" table until they are ready to finalise the invoice. The Category field should be bound into a combo box that selects all the unique categories. This is important - the ItemID combo box must have its "LimitToList" property set to No. When you've done that, paste the SQL of the ItemID combo's RowSource, and we'll take it from there.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11926186
I think your DB needs another table at least. Do you sell items or Categories? Can one item have different categories - and so on. I think you would be better served to have a good look at the Northwind sample DB that came with Access. It has good samples of what you are trying to do.
0
 

Author Comment

by:PstWood
ID: 11926356
To: shanesuebsahakarn

Thanks for responding, however I'm not sure I'm following what you're saying. I understand in your first paragraph creating the continuous form and binding the ItemId to the combo box. In answer to your question, yes, the SalesItems table contains the CategoryID field.

Secondly, what are you referring to by "temporary table", and how would I do that? Via a query that had all the tables fields?

Thanks.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11926370
No, basically, you'd create a new table that is identical to the SalesItems table. You don't have to do this but in some circumstances it can make the system more robust. It would work something like this:

User inputs into temporary table.
User clicks "Save invoice" button.
You transfer records from the temporary table into the real table and clear the temp table.

OK, now that you have your continuous form, the CategoryID needs to be bound into a combo box that selects all of the available categories. Can you paste the SQL of the ItemID combo box? We need to change it so that it only shows the Items associated to the CategoryID of the current record.
0
 

Author Comment

by:PstWood
ID: 11926411
To: GRayL

Thanks for responding. I have taken a look at the Northwind sample and have gotten a lot of info from there, however, I don't think it has samples of what I have in mind. On the Orders form in the sample database, they are listing all of the order items in a dropdown, which I could do, however I'd like to be able to first select the category, which would then limit the item dropdown to only those items within that category, rather than including everything in the inventory.

Secondly, and probably more important to me is how to change the number of items in the inventory field of the Items table based on what is entered on the sales form, for example, if there are 50 items in inventory and I sell 7 of them on one order form, I want to be able to update the inventory field to say 43. That number would be one that I would show on the sales form so that I don't oversell an item. Eventually, I would set a filter to remove the items showing 0 inventory from the list that displays on the sales form.

The Northwinds sample is acceptable other than for those two considerations.

I hope that's a bit clearer than it was.

Thanks.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11926415
Under these circumstances, I'd definitely use a temporary table. The bit of code that appends from there to the real table can also update the inventory count at the same time.
0
 

Author Comment

by:PstWood
ID: 11926488
To: shanesuebsahakarn

Here is the sql from the SalesTemp table that is given in the row source for the Category combo box
   SELECT DISTINCTROW [Categories].[CategoryID], [Categories].[CategoryName] FROM [Categories];

Thanks.
RWW
0
 

Author Comment

by:PstWood
ID: 11926504
To: shanesuebsahakarn

I should probably mention that the item description also shows up as a combo box list.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11926515
Sorry, I needed the SQL of the Item combo box rather than the Category combo. Basically, if it says:

SELECT * FROM Items

you need to change it to:
SELECT * FROM Items WHERE [CategoryID]=Form!cboCategoryID

where cboCategoryID is the combo box that displays the category. Now, you need to put this code in the AfterUpdate event of the Category combo box:
Me!cboItems.Requery
(cboItems is the name of the Item combo box)

You also need to put some code in the BeforeUpdate event of Item combo box:
If IsNull(DLookUp("[CategoryID]","Categories","[CategoryID]=" & Me!cboCategoryID)) Then
   MsgBox "Invalid category!"
   Cancel=True
End If

This is so that the user can't enter an invalid category. We need this because the NotInList property is set to No, and we have to do that in order to be able to filter on a row-by-row basis. We also need some code in the OnCurrent event of the form:
Me!cboItems.Requery

in order to make sure the Item combo box always shows the right data.

Phew, that'll do to start with! Let me know when you have this working, or if any of it is unclear.
0
 

Author Comment

by:PstWood
ID: 11927261
To: shanesuebsahakarn

OK that works like a charm. I had a problem with an error message when I tried to open the event editor for the Item BeforeUpdate, but repairing the database seemed to eliminate it.

I'm doing this in the using a form created with the temporary table you suggested, BTW, so there are only two pieces missing: how do I get the current Inventory count from the Items table, and how do I update it for the number of items sold on the order form, and secondly, how do I write what is in the temp form to the permanent table and then blank the form to start over?

Thanks.
RWW
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11927313
OK, let's assume that your inventory count field is always correct and shows the current values of the items in stock (we'll look at this in a moment). You can include that value in your combo box so that the user always gets the correct inventory count, and you can filter it to show only available items by changing the combo's recordsource to something like:
SELECT * FROM Items WHERE [CategoryID]=Form!cboCategoryID AND [Inventory]<>0

Now, you create a "Save invoice" button on the form. The save invoice button has this code:
CurrentDb.Execute "UPDATE Items INNER JOIN tmpItemSales SET [Items].[Inventory]=[Items].[Inventory]-[tmpItemSales].[QuantitySold]"
CurrentDb.Execute "INSERT INTO ItemSales (Field1,Field2...) SELECT Field1,Field2... FROM tmpItemSales"
CurrentDb.Execute "DELETE * FROM tmpItemSales"

The first line updates the inventory value in the Items table to deduct the sales you've just input. Change the names to match your own tables.
The second one copies records from the temp table into the real one - change Field1,Field2 etc. to all of the fields in your tmpItemSales table excluding any autonumber fields you may have. The second set of field1,field2 should be in the same order as the first. Finally, the third line clears the temporary table. You may also want to do:
Me.Requery

after the above lines to refresh your form if you aren't closing it immediate. Let me know if you have any problems.
0
 

Author Comment

by:PstWood
ID: 11927576
Before I go forward, I realized that the Limit to List property for the Item box is set to yes, so per your instructions I tried to change it and got a message about the first visible column, which is determined by the Column Widths property isn't equal to the bound column, and to adjust the column widths property first. I have for the Item box ItemID and ItemDescription for the two columns and the first of those has it's width set to 0 so that it doesn't show in the list. The bound column is 1.

How do I fix this before proceeding?

Thanks. You've got the points when we finish this and your previous post looks like it should do it.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11927596
Ah ok, the first column has to be visible under these circumstances I'm afraid. However, what you could do is fake it slightly. Set the first column width to the absolute minimum (say 0.1). Size the combo box so that only the drop down arrow shows. Now, in front of the combo box (i.e. to the left), create a new textbox. Set its ControlSource to:
=Form!cboCategory.Column(1)
Access will put some bracketing in for you. It will look slightly messy, but with a bit of shifting around, you can achieve more or less the effect you need.
0
 

Author Comment

by:PstWood
ID: 11928219
Hmmm. This is strange. I made the form using the Form wizard, then made the changes you suggested originally, but missed the one about the ListProperty limit, and it worked as it should, ie. changing what was in the Item list after the category was set to only those items in that category. Then I discovered the Limit to List property, changed the 1st column width to .001, changed the Limit to List value to no, and now, even though I can't see the ItemID when I click on the drop down arrow and the list shows the items, when I select one, the only thing that shows in the list is the ItemID, not the ItemDescription as it did prior to "fixing" it. If I try to type something in the item box, however, it only tells me that it's an invalid category, and I can't go back to choose a category without getting an invalid category message, even if I erase what was typed in the box.

So is this something I'm doing wrong, or what?
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 250 total points
ID: 11928233
You haven't quite followed all of the steps. You need to also resize the combo box itself so that it's just wide enough to show the drop down arrow.

Create the text box to the left of the combo as I described. This will show the Item description, which used to show in the combo box (there's no way to prevent the combo box from showing the ItemID, but we "hide" it in this way).

To get out of the invalid category message, press CTRL+Z (Undo) rather than deleting what you typed in.
0
 

Author Comment

by:PstWood
ID: 11928375
Ok, Now I follow. Long day, too many irons in the fire, etc.

I'm going to have to go back and sort out some other issues, too, because there's one value that's calculated in a query that I'd like to have posted on the form that's relevant to each item. Part of this is that the Items table contains information concerning prices paid for incoming stock that has to be converted from dollars to a foreign currency in order to calculate the "recommended sales price" so for example, I have an item that sells for $10 US and the current exchange rate is 3 pesos to the dollar, the query multiplys the price paid by 3 to come up with the price to sell for. Then there are items that come in lots, so the price is divided by the number per lot to get per item selling price. Since that's a calculated figure, I haven't figured out how to get it on the form.

You earned the points though, so I'll figure that if I have any other questions afterward, I'll know where to come.

Thanks. I've learned a bunch.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11928402
No problem, glad I could help!
0
 

Author Comment

by:PstWood
ID: 11939116
You said above with respect to the problem with the Item combo box displaying only the item ID to resize the combo box to just the button, then put a text box to the left of the button with the following control source:

=Form!cboCategory.Column(1)

I thought you made a mistake with giving it the Category combo box name rather than for the Item, so I changed it to:

=[Form]![ItemID].[Column](2)

thinking that it was the second column that is displayed in the Item combo box, not the first column in the Category combo box. Is my thinking correct here?

In any case, I can't get anything other than the Item ID number to display there.
Thanks.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11941490
Since we've resized the category combo box to "hide" the Category name, what we need to do is make this text box show that information instead, by setting it to show the second column of the Category combo box. It basically replaces the part of the combo box that you've hidden.
0
 

Author Comment

by:PstWood
ID: 11941724
I think you lost me. The combo box that is the problem is the Item combo box, not the Category combo box. The combo box shows the Category name and sets the list of related items in the Item combo box, and it's there that the only thing that shows up is the ItemID, rather than the ItemDescription.

Further, going back to the defiinition of using the tmpSales form to collect user input, then having it inseert that info into the Sales table on a button onclick event, something is awry. I used the same approach to insert items into the Item table and it works flawlessly, but it's not inserting anything into the Sales table. I get no error message and the DELETE * FROM tmpSales is working, so I don't know where to look. I've checked and double checked my table names and field names, and can't find the problem. Here is the INSERT line:

CurrentDb.Execute "INSERT INTO Sales (SalesDate,CategoryID,AuctionID,Price,NumberSold) SELECT SalesDate,CategoryID,AuctionID,Price,NumberSold FROM SalesTemp"

Thanks again.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11941765
Oops. Sorry, I'm not quite with it, you're absolutely right.

Does:
=[Form]![ItemID].[Column](1)

do it? Column(1) actually shows the data in the second column of the combo box, since Access counts columns starting from 0.
0
 

Author Comment

by:PstWood
ID: 11942075
Yep, that fixed it. I thought I had tried it that way before but apparently not. I also figured out the INSERT problem. It was an extra required field in the Sales table that I wasn't inserting anything too.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now