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!!
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!!