Tracking Daily Various Mixtures of Product

[Cosmos75]

I have a table (tblProduct) with two fields (for purposes of simplifying this).

tblProduct
- Product
- Weight

In one day, I need to track how much was used in one day BUT it’s a little more complicated.

Say we have Product A, Product B, Product C and Product D.

The we the products are used is that they are mixed. So one day the usage may look like this

1) 5 Product A mixed with 6 Product B
2) 6 Product C mixed with 1 Product D
3) 3 Product B mixed with 4 Product D
4) 2 Product A mixed with 2 Product C

I need to be able to compute the average density for each mixed product. I can’t figure out how to accomplish this.

I like the user to be able to just enter a date on a main form, and the subform would be a continuous form and allow the user to choose what product was used and how much, then the next row would be the next product used. Then click next, and add another mixture for the same day.

Any ideas?

 

[Cosmos75]

Initial Ideas

I've considered creating a table the stores a autonumber primary key and a date field (tblDate). Then use a junction table between tblProducts and tblDate (tblUsage). So the junction table has this structure

-Lookup to DateID
-Lookup to Product
-Product Usage

e.g.

tblDate
[DateID], [Date]
1, 01/01/2003
2, 01/01/2003
3, 01/01/2003

tblUsage
[DateID], [Date], [Product],[Usage]
1, 01/01/2003, Product A, 5
1, 01/01/2003, Product B, 6
2, 01/01/2003, Product C, 6
2, 01/01/2003, Product D, 1
3, 01/01/2003, Product B, 3
3, 01/01/2003, Product D, 4
4, 01/01/2003, Product A, 2
4, 01/01/2003, Product C, 2

I'd have to have a form where the user entered a date in a textbox, and for every new record in tblDate, use that as the default value.

But it just doesn't seem like a very elegant solution to the problem having to store the same date more than once in a table. I just can't see another way to deal with these mixtures.

My brain is tired from this! Argh!!

 

[Cosmos75]

I just wanted to mention that I will not be naming the field Date and point out to someone who might not know this. Don't name a field Date as it is a reserved word for VBA.

 

[Cosmos75]

Here's a table design that seems to work.

tblDates
-DateID (PK)
-UsageDate

tblMix
-MixID(PK)
-DateID(PK)

tblUsage
-UsageID (PK)
-MixID(FK)
-ProductID (FK)
-Usage

But now my problem is getting the forms to work as I would like it to...

I like the user to be able to just enter a date on a main form, and the subform would be a continuous form and allow the user to choose what product was used and how much, then the next row would be the next product used. Then click next, and add another mixture for the same day.

Any ideas?

 

[neileg]

The continuous flow of consciousness doesn't help anyone to jump in and advise!

What exactly is the situation, what do you need to do?

Do you have just one mix a day? How many products do you have in each mix? How many products do you have altogether?

 

[pbolger]

HELP PLEASE!

could anyone have a look at this DB.

basically what i need is a form for entering details about suppliers, product categories and products. i have set up 3 tables, supplier, category and product.

i have on the main form, supplier details and on the sub form category and product details.

i would like to have both category and product as combo boxes. i need these set up so that the user can select from a list or enter a new one if its not on the list.

i would like for the user to select prodcut based on the category selection..

does this make sense to anyone!!

any help greatly appreciated
cheers
paddy

 

[Cosmos75]

neileg,

Sorry for the "continuous flow of consciousness ". Just that I didn't get any replies and thought that I'd update anybody following this thread as to what I've figured out (or think I have) so far.

Yes, there is more than one mixture per day. Each mixture can be made up of multiple products and a product can be found in more than one mixture.

On top of that, certain prodcuts (Special) used by themselves (and also may be found in any mixture), must have the weight added up for the day and distributed proportional to the usage for each mixture total weight. Then this new total weight must be used to calculate the density for each mixture.

My last post on my table design seems to work and I think I've gotten the forms to work (by having a button to set the date on the usage subform [FK] equal to the date in the main form so that the PK for the MixtureID is generated).

Will update when I get it to work.

 

[Cosmos75]

I think I might have to be more detailed.

I would like to set up Mixtures in a table but I want to track the usages of each Product that makes up the product on a daily basis.


Product: A, B, C, D

MixID: 1, 2, 3, 4, 5

Mixture
1 = A+B
2 = B+C
3 = C+D
4 = B+D
5 = A+D

UsageDate: 01/01/2003, 02/01/2002, 03/01/2003

01/01/2003
[MixID], [ProductID], [Usage]
1, A, 10
1, B, 5
3, C, 20
3, D, 2

My table structure works in that I can track each products usage and tie that back to a MixID (PK), but if I has Mixture AB more than one I have to keep choosing Product A and B and entering seperate usages. Which is great for doing the daily tracking but than I have no way of just tracking an annual total of Mixture AB since each occurance of Mixture AB is not tied to a unique PK.

I think the problem is that I would like to have a mixture exist as a unique record but am trying to track it usage by its parts.

I am sorry if I am just so thick that I can't see it!
HELP!

 

[Cosmos75]

Table structure 1

One table structure that I've thought of but can't get it to work with the form structure I would like.

 

[Cosmos75]

More table structure

Another one...

 

[neileg]

OK, this is what I assume to be the case:
You have a standard range of ingredients from which you create a range of mixtures according to some standard formulas.

You want to be able to track
1) the mixtures you have made on any day
2) the products you have used on any day
3) the density of the mixtures

I suggest you have the following tables:
tblProducts - holds name of product (ingredient) and density
tblFormula - holds a formula name and an ID
tblIngredients - holds against the FormulaID a range of products and quantities (to produce a unit of the resulting mixture)
tblBatches - holds details of the batches mixed showing quantity produced of each formula and the date

This structure will enable you to query out the information you need. I attach a relationship diagram. I don't understand what role the 'specials' fill so I've ignored these for now!

 

[neileg]

Ooops, image attached

 

[RichMorrison]

Here is my best advice:

Start from the start and describe the business process in (drumroll + cymbal crash) well-organized *E*n*g*l*i*s*h*. Don't start with a list of tables, fields, and keys.

Define any special terms you use. After several posts, you have not explained what "density" means. Putting something in bold doesn't add meaning.

RichM