Any other ideas for tracking a stock list?

[Gingertrees]

I track our business customers with all the usual contact info (name of business, address, phone, etc). Additionally, I want to keep track of the product stock levels for these various customers. Currently, I'm tracking it in a subform like this:
------------------------------------------------------------------
In Stock.....SKU.....Category....Description.......Notes
------------------------------------------------------------------
[ ]..............1234...Widgets.......Red Widget.....ideal: 3+ units
[x]..............0333...Whosits.......Helper............both colors
[ ].........
[ ].........
[ ]........
x30 products; [] represents a check box.

This feeds a very long table with a list of all all customers, SKUs, and whether or not in stock:
Customer#...SKU...stocked?
2................1234..[x]
2................0342..[ ]
56...............1234..[ ]
56...............0342..[x]
72...............1234..[x]
72...............0342..[x]
xnumber of customers xnumber of SKUs.

I didn't design this. Can anybody think of a better way to track this info? Please let me know.

 

[guerillaunit]

Hi ginger, what kind of negative drawbacks are you seeing with the current setup?

 

[Gingertrees]

I've no specific gripes - I've just never seen this size of a checklist in an Access form before. I'm also naturally leery of many-to-many relationships...though that many be unavoidable. I'm redoing this particular database, and I didn't want to take the time to rewrite this section if I were to thereafter find a more streamlined way to do it.

 

[jackd]

Where exactly do you record product stock levels ?

Could you post your table and relationship structures?

 

[Gingertrees]

Umm, currently we do not track product stock levels. We're the manufacturer, we sell to distributors, who sell to our customers (who sell to consumers). So we don't have specific numbers of what the customers bought to stock their stores. It is however important from a sales standpoint that we know they do in fact have our product on their shelves.

This part of the database is filled in based on the reports of our salespeople, when they visit the customers' locations. They'll come by at regular intervals and report, "yes, they have displays X, Y, and Z, and sufficient stock of the following SKUs..."

The table structure is somewhat in flux; as I said, I'm redoing it [to hopefully improve it]. Customers are indexed throughout the database by a Customer#.

 

[guerillaunit]

I don't see a Customer ID field in your subform. How do you know which customers the records are referring to in that table?

 

[jackd]

Umm, currently we do not track product stock levels. We're the manufacturer, we sell to distributors, who sell to our customers (who sell to consumers). So we don't have specific numbers of what the customers bought to stock their stores. It is however important from a sales standpoint that we know they do in fact have our product on their shelves.

This part of the database is filled in based on the reports of our salespeople, when they visit the customers' locations. They'll come by at regular intervals and report, "yes, they have displays X, Y, and Z, and sufficient stock of the following SKUs..."

The table structure is somewhat in flux; as I said, I'm redoing it [to hopefully improve it]. Customers are indexed throughout the database by a Customer#.

I was just going by this

Additionally, I want to keep track of the product stock levels for these various customers.

If you have a current db structure and you have new requirements, I would look at the structures and provide comments if you wish. It is best if you get your tables and relationships structured before getting too much "done" in Access that becomes problematic because of table design.

There are some data models at www.databaseanswers.org that may be helpful to you.

Good luck with your project.

 

[Gingertrees]

oh - sorry. As I said, it's a subform (form within a form) - the customer # is in the main form. The form is structured like this:

==============================
Customer#: 202 =
Address: 123 Any St =
Phone: 324-234-2222 =
(etc) =
_______________________________ =
|SUBFORM....................................| =
|In Stock.......SKU....Category.........| =
|...[x]...........2342...Widgets...........| =
|...[ ]............0233...Helpers...........| =