Creating a meal planning calendar/weekly grocery list

[Urgent]

I would like to create a meal calendar (supper only), the idea is to plan it
for Sunday through Saturday for 52 weeks. I guess I would have to create a
table for each week?? Every Sunday I would want to print out a grocery list
for all the receipes for that up-coming week. I would have to enter the main
ingredients in another location (table, query??) Does this make sense?? I'm
kind of green to Access. Is there anyone out there that can help me? Is
this do-able through Access??

 

[strive4peace]

"table for each week?"

absolutely not! As a general rule, you should not have multiple tables
with the same structure.

here are some tables I see:

*Ingredients*
IngredID, autonumber
Ingredient, text

*Recipes*
RecipeID, autonumber
Recipe, text

*RecipeIngredients*
RecIngID, autonumber
RecipeID, long integer
IngredID, long integer
Amount, single

*Meals*
MealID, autonumber
MealDate, date
MealNum, integer (1=breakfast, 2=lunch, 3=supper)

even though you are just planning supper right now, you may as well make
your structure flexible enough to accomodate other meals down the road

*MealRecipes*
MealRecID, autonumber
MealID, long integer
RecipeID, long integer

to help you understand a bit better, send me an email and request my
30-page Word document on Access Basics (for Programming) -- it doesn't
cover VBA, but prepares you for it because it covers essentials in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Urgent]

So how would I set up the relationship for these tables -- Match the ID
numbers - primary keys??

When I have entered in the ingredients in the "ingredients" table, and then
filled in the names of the recipes under "Recipes" table do I have to enter
each ID number for the ingredients under the "Recipe Ingredients" table or is
there an easier way of doing this??

 

[Larry Linson]

You would decide how the information is related in the real world and use
the fields that represent those relationships. Typically, in a
"one-to-many" relationship, the tables on the "many" side include as a
"foreign key," the primary key of the table on the "one-side". They will
also contain a separate key field, to identify the specific records in the
"many-side" table.

Larry Linson
Microsoft Access MVP

 

[strive4peace]

you should set up forms to enter data, do not enter directly into tables.

'~~~~~~~~~~~
set up a continuous based on the Ingredients table

'~~~~~~~~~~~
set up a main form based on the Recipes table and make sure that
RecipeID is on the main form

set up a continuous form based on the RecipeIngredients table (this will
become a subform for the Recipes form). Make sure you put RecipeID on
the form and it can be invisible.

to collect the recipe to use, make a combobox on this form:

Name --> RecipeID
ControlSource --> RecipeID

RowSource -->
SELECT RecipeID, Recipe
FROM Recipes
ORDER BY Recipe

ColumnCount --> 2
ColumnWidths --> 0;3
ListWidth --> 3

then add control for the other fields

then save the form,
form name --> fsub_RecipeIngredients

once you have created fsub_RecipeIngredients, close it and open the
design view of Recipes. Add a subform/subreport control and make it
whatever height and width is good

SourceObject --> fsub_RecipeIngredients
LinkMasterFields --> RecipeID
LinkChildFields --> RecipeID

'~~~~~~~~~~~

set up a main form based on the Meals table

set up a form based on the MealRecipes table (this will become a subform
for the meals form). Make sure you put MealID on the form and it can be
invisible.

If you decide you want to plan 3 means a day, you can use the
MealRecipes subform 3 times...for now, though, we won't go into how
because there are other steps you need to take first

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

oops, what was I thinking!

since the subform for Recipes will be based on RecipeIngredients ... I
meant IngredID for what I said to do for RecipeID combobox on that
subform (you will still need RecipeID on the subform and it can be
invisible)

The RecipeID combobox should be on the subform for Meals...the
MealRecipes subform

well, I hope you get the idea anyway!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[haber]

You would decide how the information is related in the real world and use
the fields that represent those relationships. Typically, in a
"one-to-many" relationship, the tables on the "many" side include as a
"foreign key," the primary key of the table on the "one-side". They will
also contain a separate key field, to identify the specific records in the
"many-side" table.

Larry Linson
Microsoft Access MVP

 

[strive4peace]

Hi Urgent (what is your name?)

"I can get the Ingredient Names and Recipe Names, as apposed to their ID
number, to show up in the Recipe Ingredients Table "

I suggest that you NOT use lookups in your table design.

Instead, make forms for entering the data and use comboboxes there...

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

"It hard to do data entry when all you see is ID numbers"

here is a Combobox Example that you can draw analogies to...

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.

~~~~

to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Carrie,

in order to help you with your query, we need to know your data
structure. Here is something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)
Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal to add counter and data type string
Debug.Print fld.OrdinalPosition & " " & fld.Name _
, fld.Type & " " & GetDataType(fld.Type) _
, fld.Size
Next
End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~`

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Carrie,

I made a little modification to Duane's ShowFields sub...used literal
commas between each item on the line instead of a tab --- makes it just
a bit easier to read...

'~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)
Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next
End Sub
'~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*