Share via

I am new to access. I am setting up a new database and associated queries. I would like suggestions on the correct way to set up my tables.

Sean M 20 Reputation points
2025-07-28T16:53:06.2833333+00:00

I 've used Excel for many years and am relatively proficient with it. However, I am working on a project that requires far more than Excel offers. I believe access is the best prograqm for this. I am completely new to Acess so I am on a big learning curve.

I have a small product formulation excel sheet with aproximately 150 product item codes. Each item has roughly 5 variations of core ingredients that comprise the base blend. Then a list of approximately 600 ingredient items with associated percentages required after the base blend is complete.

If I understand correctly the best way to do this is to create a few different tables. Mainly:

  1. The first, item code along with item description.
  2. The second, listing different variations of core ingedients that reference the main item code in the 1st table
  3. The third, a larger data pool with the associated ingredients referring back to the main item code.

The reason for the different sections is so that when we change an ingredient percentage or item cost we only do it in one place and it carries through for all items. This also allows for strict control over the levels of each.

The idea is to allow for queries of diffierent items with differnt ingredients as the recipes change daily according to ingredient cost. I attached a rudimentary pdf (three Sheets) of a potential setup. Any input is greatly appreciated.

Microsoft 365 and Office | Access | Other | Windows
Accepted answer
  1. Ken Sheridan 3,026 Reputation points
    2025-07-29T11:30:37.1266667+00:00

    When designing tables an important concept is the Functional Determination of columns in each table.  It is this which is the basis for the definitions of Normal Forms.  The process of Normalization involves a number of Normal Forms, and you'll find as simple as possible (but no more so) descriptions of these in Normalization.zip in my Dropbox public databases folder at:

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    Concentrate on the first three normal forms to start with.  Normalization to Third Normal Form (3NF) requires all non-key columns to be functionally determined solely by the whole of the table's primary key.

    To illustrate this let's take a look at the physical model for my Inventory demo in the same Dropbox folder:

    InventoryModel Firstly, consider the related tables Customers, Cities CountiesOrRegions, and Countries.  You'll see that Customers contains a foreign key CityID numeric column (not the city name, as city names can legitimately be duplicated, so cannot be used as keys), which references the primary key of the Cities table.  The CityID column in Customers fits the definition of Third Normal Form as it is determined by the primary key CustomerID, i.e. once we know the CustomerID value we know which city the customer is in.

     Let's consider what would be the case if we were to include a CountyOrRegionID column in the Customers table.  The table would not now be normalized to Third Normal Form because, once we know which city a customer is in we automatically know which County or Region they are in, i.e. CountyOrRegionID is functionally determined by CityID.  The table is therefore not normalized to Third Normal Form because CountyOrRegionID is not solely determined by the table's key, CustomerID.  In the jargon it is transitively dependant on the key.  This could result in an update anomaly if the customer moved to another city, but we leave the CountyOrRegionID value in the row unchanged.  An example would be my move from Cannock in Staffordshire to Newport in Shropshire a couple of years ago.  If the table were not normalized it would be possible for it to incorrectly show me living in Newport in Staffordshire.

    Let's now look at the OrderDetails and Products tables in the model.  This table models the many-to-many relationship type between Orders and Products by resolving it into two one-to-many relationship types.  The Products table contains a UnitPrice column which is functionally determined solely by the ProductID primary key column, so the table is normalized to Third Normal Form.  However, ProductDetails also has a UnitPrice column.  At first sight it looks like the table is not normalized to Third Normal Form because UnitPrice is dependant on only one half of the primary key (a composite of two columns in this case).  Surely we can dispose of the UnitPrice column in OrderDetails therefore?  But what would then happen if the UnitPrice value in Products is changed?  All earlier invoices would now show the new price, which means they would be incorrect.  By having a UnitPrice column in OrderDetails, on the other hand, whenever we insert a new row into this table we can assign the current unit price value from Products to the column, and the value will remain the same whatever changes are subsequently made to the value in Products.  The auditors will now be happy!  Both tables are now in Third Normal Form, because in products UnitPrice is solely determined by ProductID, and in OrderDetails it is determined by the combination of OrderID and ProductID, i.e. by the whole of the primary key.

    I've concentrated on Third Normal Form in this example because experience has shown that if a table is normalized to Third Normal Form, it will in the vast majority of cases also be normalized to the higher normal forms.  This is not to say that the higher normal forms are unimportant, and once you have a good grasp of normalization to Third Normal Form (3NF), you should familiarise yourself with the higher normal forms described in my demo.

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. George Hepworth 21,805 Reputation points Volunteer Moderator
    2025-07-28T17:37:49.08+00:00

    What you are asking about is the process of Database Normalization, or the proper design for relational tables. There are a great many resources on Database Normalization available, including blog posts and articles and YouTube videos.

    I recommend you start there. When you get a good idea of what relational database design is all about, you can take a run at creating the tables you need for your application.

    Product Recipes is not an uncommon business process and there are, no doubt, many databases built to support various businesses of that sort. Search using variations of that term. I'm sure several discussions will turn up on various forums.

    You need a table for Ingredients, which will have fields for IngredientName, IngredientDescription and IngredientType. This last field indicates whether the ingredient is one of those core ingredients which are used in the five base formulations you mentioned, or whether it is an additional ingredient added to the base formulations.

    You need a table which identifies various formulations, along with the ingredients (of both types) in that formulation and a field that indicates the percentage of that ingredient in that formulation. Given that your ingredient percentages can change, I would imagine you would also need to have StartDate and EndDate fields for the time period in which an ingredient was used in a specific formulation at a specific percentage. Otherwise, you can carry that information in a Batches table.

    The Batches table identifies each Batch of each Formulation, with the foreign keys for the formulation and each ingredient in that specific batch, along with the specific percentage of each ingredient in that specific batch. Although this is duplicative, I believe it would be useful because any given batch could potentially vary from the standard percentage. It totally depends on your business rules though.

    No doubt there will be additional tables which you will identify as you progress, perhaps one that identifies employees responsible for creating different batches, and maybe a table that identifies machines used to produce a batch of a formula. It all depends on what factors you intend to track.

  2. TiNo-T 4,215 Reputation points Microsoft External Staff Moderator
    2025-07-29T00:55:31.92+00:00

    Dear @Sean M,

    Thank you so much for contacting Microsoft Q&A Forum. 

    Based on your description, you're absolutely on the right track thinking about transitioning from Excel to Access for this kind of project. Access is well-suited for managing relational data, especially when you want to maintain consistency, reduce redundancy, and enable dynamic querying. 

    Also, you mentioned that you attached PDF for your setup. However, I do not see any of them. Therefore, I have several suggestions based on what you described your ideas: 

    1.Products table: 

    • Fields: ItemCode, ItemDescription 
    • Purpose: Master list of products. 

    2.Base blends table: 

    • Fields: BlendID, ItemCode (FK), CoreIngredient1, CoreIngredient2, ..., CoreIngredient5 
    • Purpose: Variations of core ingredients per product. 

    3.Ingredients table: 

    • Fields: IngredientID, ItemCode (FK), IngredientName, Percentage, Cost 
    • Purpose: Detailed list of ingredients and their percentages/costs. 

    This structure can help you: 

    • Normalization: you're separating data into logical tables to avoid duplication. 
    • Referential Integrity: Using foreign keys (FK) ensures that ingredient and blend data always link to valid products. 
    • Scalability: You can easily update ingredient costs or percentages in one place. 
    • Query Power: You can run queries like:  “Show all products using Ingredient X”; “Calculate total cost of Product Y based on current ingredient prices”; “List all blend variations for Product Z”. 

    You can consult in this article for the query: Video: Get started with queries - Microsoft Support 

    In this situation, you can try my suggested setup to see if it can help you with your data. Also, you can check while using it: 

    • For example, if you want to see all products that use a specific ingredient, you can run a query that pulls that instantly, something that’s very manual in Excel. 
    • If the cost of an ingredient changes, you update it in the ingredients table, and all cost calculations for products using that ingredient will reflect the new value. 

    Besides that, I highly recommend that you can consult other sections in the link above regarding Access and you should find other online videos to learn more if you want to be more proficient in this feature. This will be a good start for new learner. 

    I hope this information can help you in this situation and please kindly correct me if I misunderstand your concern. 

    Wish you a pleasant day!

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    User's image

  3. Sean M 20 Reputation points
    2025-07-30T10:03:40.0666667+00:00

    Sample layout for Access file.pdf

    I apologize. I did not realize that the file did not attach to the original post. I did a very basic concept of the layout in excel. I believe the pdf will make it easier to conceptualize what I am trying to get to.

    This is my first time posting to the forum. I am still learning how to use the site.

    0 comments
  4. Ken Sheridan 3,026 Reputation points
    2025-08-01T14:01:32.5566667+00:00

    Apologies for the delay in responding.

    When designing a relational database it's necessary to get out of a spreadsheet mindset. Despite their superficial similarities a spreadsheet and a database table in datasheet view are fundamentally different.

    The thing which immediately stands out from your schematic representations of the mooted three tables is the use of duplicate columns, lng 1 and lng 2, along with the two qty columns. Such a table is not normalized to First Normal Form (1NF) which requires each row in a table to have only one value of each attribute.

    In a correct model you should have a referencing table to model item variations:

    ItemVariations

    ….VariationNumber (PK)

    ….ItemCode (PK)

    ….IngredientID (PK)

    ….Quantity

    The primary key of this table is a composite of the three foreign key columns. This table references an Items table, your table 1, of which ItemCode is the primary key, a Variations table,

    Variations

    …VariationNumber (PK)

    The table can include any further columns necessary to represent non-key attributes of the variations entity type.

    and an Ingredients table.

    You refer to core variations. What distinguishes these is unclear, but rather than having separate tables, table 2 and table 3, you could perhaps add a Boolean (Yes/No) column, IsCore, to ItemVariations. However, without a fuller understanding of what makes a variation core or not (in database terms, on what is it functionally dependant), I say this only provisionally.

    I note that in the qty column of your table 3 some column positions have cardinal numbers, while another has a percentage. You should not mix attribute types in a column like this. Each value should be legitimate member of type qty, which might be a cardinal number or a percentage in every case, but not a mixture of the two.

    While on the subject of recipes you might find the following query of mine of interest. It was published many years ago in Personal Computer World Magazine's databases column by my old friend Mark Whitehorn. In relational database terms it is an example of Relational Division, which was one of Codd's original algebraic operation when he first introduced the database relational model back in 1970.

    SELECT  R1.Recipe

    FROM Recipes AS R1

        INNER JOIN Larder AS L1 ON R1.Ingredient = L1.Ingredient

    GROUP BY  R1.Recipe

    HAVING COUNT(R1.Ingredient) =

            (SELECT COUNT(*)

            FROM Recipes AS R2

            WHERE R2.Recipe = R1.Recipe);

    An alternative method, which references a multi-select list box in a form can be found in SearchDemo.zip in my Dropbox public databases folder at:

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.