I created a product catalog/sales recorder database that isn’t quite working the way I want it. I decided migrating to a new structure will be easier in the long run than trying to fix the old.
Here’s the scenario. I sell greeting cards. Most times, my greeting cards are unique, one time only. Sometimes I create more than one. Sometimes I create several of the same and sell them as a package. The cards are sold at two different local stores, plus online, plus in person to individuals, plus at craft shows.
So, what I for the new design thus far is:
Product Table
ProductName ProductType Image
Birthday1 Single Card png file
Fireplaces Card Pack png file
Disposition Table
Name Location LocationDetail ListPrice QtySold SoldPrice SoldDate
Birthday1 Store1 $5.00 1 $5.00 10/1/2021
Birthday1 Other John Smith $5.00 1 $5.00 10/2/2021
Fireplaces Store1 $15.00 2 $27.00 10/2/2021
Fireplaces Store1 $15.00 1 $15.00 10/2/2021
Birthday1 Store2 $5.00 1 $5.00 10/4/2021
Fireplaces Other Donated $15.00 1 $15.00 10/7/2021
Birthday1 Online $5.00 1 $5.00 10/18/2021
Birthday1 Stock $5.00
What I would like out of this is a combined table where I can show views by product and/or by store. I’ve tried linking these a couple of ways and I must be doing it backwards as the combined table never seems to have data. I always get confused which way to do the links first.
Any help would be appreciated.
