New to Databases, Designing and building my first.

[Tomtom56]

Hey!

I am currently trying to design a database to keep all my cocktail recipes in and also keep information on each of the products I use so that I can recall them later.

I started the layout. This is what I have so far:

Cocktail Recipe Table
-ID
-Name
-Garnish
-Method
-Description
-Author
|
|-Alcoholic Ingredients (Many to Many)
||-ID
||-RecipeID
||-Alcohol
||-Amount
||-Measurement Type (ml, L, g, Pinch...etc...etc)
|
|-Non-Alcoholic Ingredients (Many to Many)
|-ID
|-RecipeID
|-Non-Alcoholic Ingredient (Everything from Fruit slices to Coffee)
|-Amount
|-Measurement Type (ml, L, g, Pinch...etc...etc)

There is one Measurements table linked to the above many to many through ID. But I want it to be able to add other measurement types and not allow duplication.

Measurements
|-Id
|-Measurement Type

In the Alcoholic Ingredients table it has

ID
Name
Primary Cat
Secondary Cat
Origin
Alcohol Content (In Percentage)


In the Non Alcoholic Ingredients table it has

ID
Name
Primary Cat

The categories each have their own table with ID and #Cat Name as columns.


The forms I want to build are this.

Input Form ONLY:

Name: (No duplication allowed but cannot overwrite data)
Garnish:(No duplication allowed)
Ingredients: (Fields must be Ingredient, Amount, Measurement Type. IF alcoholic then show Alcohol Percentage Automatically.)-If the ingredient field is filled in then
the rest must be
(A few boxes or a table that uses both the Non-Alcoholic Ingredients and Alcholic Ingredients tables data)
(I want this also to allow for a new ingredient to be added and it will ask if it is an alcohol or not. If it is then it will default to 100% Alchol Content unless
changed, before adding it to the database. A primary category must be put in, regardless if it is Alcoholic or Non-Alcoholic. If it is a New alcoholic
Ingredient then it will pop up a box with all the info able to be put in.)
Method:(Text 500 Characters)
Description:(Text 500 Characters)
Author:(First Name, Last Name) (To be stored in a separate table with an ID, No duplication allowed in this table but in the recipes table yes)

I have my tables set up and relationships linked correctly. That I am ok with...I think unless anyone has a suggestion.

The tricky bit.

I set up my Mainform with a Subform linking to Alcoholic Ingredients (Many to Many) set it up so that the fields input and there is a dopdown list of ingredients, I can't add anything, the measurement amounts show up and the type shows up after learning some SQL. When I go to make Another subform to link it to the Non-Alcoholic (Many to Many) it overtakes the Alcoholic subforms data. It wont allow input of ingredients in either form but will allow measurement amount and type, but it duplicates across both subforms.

How do I achieve what I am trying to do??? It feels so simple but yet i don't understand enough about how this works. I haven't used macros as I don't understand them and I also have not reach the Queries stage. Anyone able to point me to some tutorials on this type of database build or to a template tutorial for this type of thing?

I did download MyRecipes.odb and a few others but honestly...I'm lost haha

Thanks in advance!

Tom

[Tomtom56]

ok so figured most of it out. hadto change column names and also primary keys to make the auto instead of user input which i found was a problem.

Now. I can use the LList Box in the table columns and it will display things nicely from my database.
BUT
I can't add anything to it. So i changed it to combo box, added in the SQL code:

SELECT "NAProductName", "Non-AlcoholicID" FROM "Non-Alcoholic_Ingredients" ORDER BY "NAProductName" ASC

Which is what was used in List Box and it can't add anything, I can start typing and say, Vodka will come up but will default to a 0 when i go to the next line.
When I go to add an item while making the recipe, it reverts to a 0 aswell.