Trouble with structuring Categories and Sub Categories (1 Viewer)

[AJR]

Hi

I am a Newb who has almost completed my first Database. I have just one problem to solve that seems to be far more complicated (and taken far more time) than it should. As it is very complicated to explain the problem in the actual database (I tried and no one answered) and because I am beginning to suspect that the real problem is that I have structured my tables wrong from the start; I thought it would be a good idea to go right to the start and use a simple, hypothetical question, that would replicate what I am trying to accomplish.

So, Suppose I receive boxes and store them in a room. Each box can contain items that are either shaped as cubes or spheres and I have to allow for the addition of more shapes in the future, but only one shape can be in each box. Each box contains a random number of shapes and two boxes may, or may not, contain the same number of shapes as another. Cubes can be either Red or Green, Spheres can be either Blue or Yellow.

I wish to set, and have the ability to change, how many cubes and how many spheres, on a percentage basis, I wish to have (e.g., 60% Cubes and 40% spheres).

I also wish to set, and have the ability to change, what percentage of cubes I want to be Red and Green and which percentage of Spheres I wish to be Blue and Yellow (e.g., Cubes -Red 70%, Yellow 30% )

As I receive each box I, Count how many shapes it contains, note if they are Cubes or Spheres and count how many of each color there are.

e.g. -- I have several boxes totaling:

Cubes
Red 30
Green 40

Spheres
Blue 10
Yellow 20

My goal is to produce three reports.

The first report would provide the Target and Actual number of Cubes and Spheres expressed as a Value and %. The #'s would simply be calculated from my desired % . So, if I wanted to have 60% cubes and I had 100 Shapes, my target # would be 60. If I had 200 Shapes it would be 120. (i.e., The actual number of shapes I have is simply a factor of how many I receive and I have no control over this.)

The First Report would look like this

Target % - Actual % - Target # - Actual #

Cubes 60% 70% 60 70
Spheres 40% 30% 40 30


The second and third reports would be the same format but would break down cubes into red and green and spheres into blue and yellow.

Any suggestions as to how to structure this?

I can accomplish the Report that breaks down the shapes but I am running into problems with the next level as I need the total number of shapes and the the total number of only Cubes or Spheres to insert into the expressions and I can't seem to figure out how to get both into the same expression.

Any help, even just a point in the right direction, would be greatly appreciated.

Thanks in advance

 

[Beetle]

You've left out the most important piece of information, which is your current table structure.

Using your example of boxes containing shapes, then quick spitball of a table structure might look something like;

tblBoxes
BoxID (Primary Key)
BoxDescripton

tblItems
ItemID (PK)
BoxID (Foreign Key to tblBoxes)
TypeID (FK to tblItemTypes)
ItemQty
ItemColor

tblItemTypes
TypeID (PK)
Description (Cube, Sphere, etc)

tblTargets
TargetID (PK)
TypeID (FK to tblItemTypes)
TargetPercent

 

[AJR]

Sorry my reply is a little late--had some catching up to do after the holidays.

Thanks for taking the time to respond. Turns out that my table structure was similar and the problem was at the query level. (As a Newb I didn't realize that one query could contain another. )

Viewing your table structure has still helped as I now realize that I should be using fields like TableID.

Thanks Again for taking the time to help.

AJR