Table structure for price tracking - boxes of widgets galore (1 Viewer)

Hey everyone,

I've been asked to develop a database to track prices for our clients, but I haven't built a serious database in years, and am struggling a bit with the structure. This is what happens:

We assist our clients with purchasing boxes of widgets, at sales that happen at specific times during the year. Each client may pay a slightly different price for a box of widgets, depending on what widgets are inside: let's say triangles, circles, or squares.

Box of widgets at Sale 1
Contents of client 1's box: 65% triangles, 35% circles, price per box $100 per kilo.
Contents of client 2's box: 50% circles, 25% squares, 25% triangles, price per box $110 per kilo.

At Sale 1, other boxes are also for sale: boxes of whirligigs, boxes of thingamibobs, etc, and each client will ALSO pay different prices for each of these boxes, depending on the break-down of whirligigs and thingamibobs inside. The price of individual whirligigs, thingamibobs, and widgets is not available and cannot be calculated.

At Sale 2, boxes of widgets, whirligigs, and thingamibobs are all still on sale, but their overall box prices have changed. Let's say that Client 1's box of widgets is now 60% triangles, 30% circles, and 10% stars, and the price is $101 per kilo. Client 2 has a similar box of widgets to last time, but they're now paying $95 per kilo.

As you can imagine, price tracking is getting a bit tricky. Also, rather than just being 3 types of boxes, there are around 150, all with different components inside. Also also, different clients purchase different types of boxes, and we don't have enough data to guess what component prices might be from sale to sale.

Does anybody have ANY IDEA how I would structure this? Having eg a 400 or 500-field table with a field for each box and component, then lines for each client at each sale, seems a bit crazy. Perhaps there's some completely simple and much prettier solution that is blindingly obvious to those who use Access on a more regular basis?

Thank you so much!!

How do you price these by hand? (Not a frivolous question.)

There is an "Old Programmer's Rule" that says - If you can't do it by hand, you can't do it in Access. Meaning: If you don't already have the manual pricing rules figured out, there is no hope of doing this in Access. The reason is that Access is in many ways just a bit dumber than a box of rocks. You have to tell it how to do EVERYTHING. If you can do that by hand, then draw out the rules.

The price of individual whirligigs, thingamibobs, and widgets is not available and cannot be calculated.

Click to expand...

This leads to the question, what do you sell? Widgets or the boxes of assortments that may include widgets? If you sell the boxes of assortments as-is, you have over-complicated your life because the contents are a matter of interest for description but not for pricing.

On the other hand, if you take the boxes and sort them out into (sub-) boxes of widgets, boxes of thing-um-a-bobs, etc. and sell those items retail or wholesale, then the statement "price cannot be calculated" is a dagger in the heart of this process. Again, if you cannot determine a price by hand, you are already dead in the water.

First decide what you are selling. Then you know what you are tracking. Then you can decide what you want to input to your putative database.

IF all you are selling is boxes, then you just assign each box a number and a price based on how much you paid for it at Sale 1 or Sale 2 or Sale 543151 or whatever plus your markup rate. The contents are then immaterial for pricing, only for descriptions - and that can be as simple as a text field in a box table. (It could get more complex than that if you have tens of thousands of boxes and only a hundred possible box-content types.)

When you can tell us more about what you wanted to track because you know what you are selling (specifically), we might be able to help.

Read up on normalization: https://en.wikipedia.org/wiki/Database_normalization, work through a few tutorials and then start trying to apply it to your data.

My advice is to open a spreadsheet and add sample data. Put Field names in Row A and then start plugging in possible records. Make that one big 500 field table you are talking about. Then, once you have enough data to cover all casses and enough columns to accomodate all your data, you start normalizing.

Open Access and start modeling your tables. Set up a Client table to handle all the Client data. Set up an ItemType table to handle all those specific items that go into boxes. Keep doing this until all your data is accomodated in Access. Then set up your relationships, take a screen shot and post it back here for us to look at.

The_Doc_Man, you are a wonderful human being. Thanks for the comprehensive reply!

We act as brokers for our clients, so we are neither selling the boxes nor buying them, but rather tracking sales data so that our clients know how much they've purchased, and at what price. At the moment, there is an Excel spreadsheet which is formatted along the lines of

Sale 1 Sale 2
Box of widgets $100 $110
Circles 55% 60%
Squares 30% 20%
Stars 15% 20%
Box of thingamibobs $50 $60
Reds 10% ...
Blues 30% ...
Greens 60% ...
(etc).

(I've just realised that the formatting for my beautifully-spaced table doesn't come out, tho have put an image on imgur...and can't link! imgur.com/a/lfHnc)

So as you can see, we track the prices of the overall boxes, but just record the component % breakdowns. (I'm sorry if any of this sounds idiotic, or if i'm not explaining myself properly.)

ALSO as you can see, this is only tracking the prices and breakdowns for one client, whereas we need to track the prices and breakdowns for EACH client. At the moment, after each sale we send out a pricelist to each client, and the price is fairly arbitrarily selected--it could be the price and breakdown for Client 1, 2, 3 etc, and each client gets that data (which seems a little sketchy imho). So the new solution would see us sending out a pricelist which entailed weighted average prices, as an example...but that's getting off-track somewhat.

Given what we're currently doing, I imagine it would be totally fine to just include the % breakdown of boxes as a description--but each type of box has different components, so I feel like there should be some kind of linked table action in there somewhere? Am I making any sense?

Thank you!!

You can certainly do such a thing, though data entry might be a bit tedious.

Consider these tables

table Boxes: BoxID (prime key, either autonumber or whatever natural key might exist), cost (currency), seller, buyer, other box data (except for contents)

table BoxHolds: BoxID (foreign key, LONG if Boxes PK was autonumber otherwise whatever data type you used for the natural key), Object Type (code), percentage in box (single or double or, if fractions of a percent don't count, you could even use a BYTE integer).

table BoxObjs: Object Type (code, see BoxHolds), object name (string), anything specific to objects that can appear in the box.

Then you have a relationship such that a Boxes record has a one-to-many relationship with records in BoxHolds (Boxes on the "one" side, BoxHolds on the "many" side).

Because the "arrows" would point the wrong way, you might have to have a second layer of query to then establish the relationship between Object Type between BoxHolds and BoxObjs, because that will be many-to-one (Boxholds on the "many" side and BoxObjs on the "one" side).

When you write your queries you could then do any kind of lookup, report, or display using the queries to bring it all together.