X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> How Do I Automate My Table?, Access 2016    
post Dec 27 2017, 04:26 PM

Posts: 67
Joined: 22-August 17
From: Los Angeles

I'll start this by mentioning that I work for a vitamin company and I'm trying to take their recipes from Word and automate them in Access. The recipe is known as an MBR. The MBR I'm working on now is for gummies and I am brainstorming how to organize a section that requires batches. What that means is the same list of ingredients goes into the mixture x number of times.

My current idea is to create 1 table to make all the redundant entries on, separating them by an indicator "batch 1, batch 2, etc..."

The reason these need to be entered on a table is so that I can query out each stage and print it on a report. It's printed because folks on the operating floor write in the actual weight of the ingredient being dispensed for each batch.

Are there any better ideas? If not, is there a way to automate the addition of records to the table?

Theoretical example: I write in 4 records (4 ingredients) on a temp table. I press a button and those 4 records are appended to my main table with all entries to be made. Automatically, I would like "Batch 1" in the indicator field. I press the button again and the process repeats, but with "Batch 2" in the indicator field.
Go to the top of the page
post Dec 27 2017, 09:48 PM

Posts: 2,426
Joined: 27-February 09

Sounds like a really good use for a Tally, or numbers table.

How do you determine how many batches you need for different recipes? If it's in a table somewhere, you can join to the tally table...

First, the Tally table... a table of numbers that runs from 1 to some number.


Then if you have a recipe that needs X number of batches, you could have a table of MBR_ID and the number of batches necessary...

CREATE TABLE RecipeBatches( MBR_ID INT, NumBatches TINYINT);

Then you could do something like

SELECT rb.MBR_ID t.N, RecipeIngredient...
FROM RecipeBatches rb INNER JOIN Tally t ON t.N <= rb.NumBatches

the inequality in the join will cause the list to "unpack"... you'll get all values from 1 to NumBatches for each MBR record.
Go to the top of the page
post Dec 27 2017, 10:06 PM

UtterAccess VIP
Posts: 9,831
Joined: 10-February 04
From: South Charleston, WV

For your recipes you'll need a recipes table, an ingredients table, and an ingredients-recipes table.

Robert Crouser

My company's website
Go to the top of the page
post Dec 28 2017, 11:03 AM

Posts: 67
Joined: 22-August 17
From: Los Angeles

If I am reading the literature right on this, I do not think it will work to fit my needs. Maybe I just need more explaining. I understand that the tally table is created via VBA and that the numbers are sequentially added, but unless the records are removed the number will just keep going up. This is something that we do not want. Also, the reading describes the procedure in a way that numbers are sequentially added to records and not multiple records (i.e. I have a batch of 4 ingredients that need to be copied 15 times. It should be numbered 1,1,1,1 - 2,2,2,2 - etc. - 15,15,15,15) The number of times a group of records (a recipe mixture) is added is determined by a number from the summary document.

I'm already working that approach. A junction table, right? I still need the indicator there so that I can query out the individual batches and print them on the report. I'm just looking for a way to automate the addition of records.

Go to the top of the page
post Dec 28 2017, 11:26 AM

Posts: 2,426
Joined: 27-February 09

SELECT i.IngredientName, t.Number
FROM Ingredient i, Tally t
WHERE t<=i.Quantity;

The 1.1.1 is all for show anyway... do that with a formatting function
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 06:16 PM