a couple inventory questions (1 Viewer)

[Joeman2020]

Hi Access World,
Hope all is well. I have a couple questions regarding my database inventory project for my class. I'm trying to set up a reorder alert and not sure how to go about that. I tried running a query called qreorder, but it didn't seem to work when I ran it. I put a criteria in for <= minimum stock level but all the records showed up. I only want it for under 15. Can we put some kind of conditional format in the quantity in stock field to let me know it's time to reorder? You can experiment with a new record if you wish. Also can you take a look at my over all project, and see it I sat everything up correctly? I would appreciate your any advice that you can give me. Thank you, Joe

 

[jdraw]

See this Inventory article by Allen Browne for concepts and approaches.

 

[Joeman2020]

Hi,
Thanks for the article, but I would still like someone to take a look at it.

 

[pbaldy]

Your criteria is comparing the Min Stock Level to itself, so all records qualify. When you fix that you'll run into the issue of the quantity in stock field being the wrong data type.

That said, as jdraw's link implies, most of us don't store the quantity on hand, we calculate it from transactions.

 

[plog]

When people post databases I look at their tables and if I see issues there I go no further. You have a major issues with your tables: You do not have a proper transaction database, you've got a transaction spreadsheet.

tblItems is doing the work of what should be 2 tables. tblVendor lists all vendors, you need a similar table for items. You need a table that simply lists the items available. That table would have nothing to do with transactions. You would have a seperate table for that.

You are accomplishing your current inventory levels with an UPDATE query and writing that data back to the table. Incorrect. When you want inventory levels you simply run a query on your transaction table (it adds up all credits and subtracts all debits from inventory) and you reference that query when you want inventory levels. You don't store calculated values in a database--you let the database calculate them for you via a query.

That's the big one. Then I see a few other issues:

1. Duplicated data. tblitems has every field tbllabsupplies has. tblallsupplies shouldn't exist. Insted it's data should go into tblitems.

2. Spaces in names. You should only use alphanumeric characters in naming tables/fields. That means [Item Code] should become [ItemCode]. Doing that just makes coding/querying easier later.

 

[Joeman2020]

Great, thanks for the advice plog and pbaldy, i will try to correct the issues.

 

[pbaldy]

No problem, and I certainly agree with plog's points.

 

[Joeman2020]

OK i will try to fix those issues. I'm just kind of starting out so, I don't have a great deal of knowledge in Access.