comboBox mulitiple fields

[Dick7Access]

I am on a weight lost program at the VA. I have sheets I have to fill out by hand. I have to track date and day. Type of food, which meal (breakfast, lunch etc.), time of day, serving size, calories, fat, carbs, and protein. A sheet for each day.
I put together a quick program that does, but lots of my food is the same thing every day, or every third or fourth day. I have to go back and look up all the above and enter the text in by hand. I was able to use a combo box to enter food type of food, date picker for date, and combo for day, but then had to again look up the rest of the fields.
Typical entry:
Date : 12/1/2017,
Day: Fri
Type: coffee with half&half,
time : 7:00 am,
ser. Size: 2 cups,
Cal.: 10g ,
Fat: 1g
Carbs: 1g
Protein: 3g

 

[Dick7Access]

Where is the Look up wizard in access 2016. I have googled everything I can think of for over an hour and it just keeps bringing up Classics menu info.

 

[isladogs]

I had to check the Access help file as I've not used that wizard in a very long time!

Open the table in datasheet view & move to the right hand column marked 'Click to Add'.
Right click the column & you will see 'Lookup & Relationship' in the context menu

Click to open the Lookup Wizard

 

[Pat Hartman]

Do Not add lookups to tables. They will cause nothing but problems if you ever need to create queries or VBA.

Combos (lookups) are used on forms. There is no need for them to be defined on tables. All they do is mask the actual value being stored which will simply confuse you. They add no value. You should be doing the data entry via a form where combos can easily be used.

 

[isladogs]

Just to say I agree totally with Pat and had intended to add a similar comment to my previous answer.

The fact that the wizard is less than obvious to get to may be a good thing.

 

[Dick7Access]

Just to say I agree totally with Pat and had intended to add a similar comment to my previous answer.

The fact that the wizard is less than obvious to get to may be a good thing.

Thanks, I just happen to come across it as I am googling everything I could find on multiple field combos, and wanted to see what is all about.

 

[Dick7Access]

I am on a weight lost program at the VA. I have sheets I have to fill out by hand. I have to track date and day. Type of food, which meal (breakfast, lunch etc.), time of day, serving size, calories, fat, carbs, and protein. A sheet for each day.
I put together a quick program that does, but lots of my food is the same thing every day, or every third or fourth day. I have to go back and look up all the above and enter the text in by hand. I was able to use a combo box to enter food type of food, date picker for date, and combo for day, but then had to again look up the rest of the fields.
Typical entry:
Date : 12/1/2017,
Day: Fri
Type: coffee with half&half,
time : 7:00 am,
ser. Size: 2 cups,
Cal.: 10g ,
Fat: 1g
Carbs: 1g
Protein: 3g

OK my friends, now that you have had a chance to evaluate the end results I need is a multiple field look up table the avenue I should be pursuing? Your opinion greatly valued.

 

[Pat Hartman]

You don't need to copy all the data from the lookup table to the log table. All you need to do is pick the item and that stores the foreign key.

The foods table contains foods, serving size, calories, fat, carbs, and protein per unit.

The log table picks the food and if the unit size is other than 1 (1 should be the default), then you need to add the number of servings. So using your example, if you have two cups of coffee with Half&Half, then you would enter 2 as the number of servings and the form/report would multiply the calories, fat, carbs, and protein by 2.

The forms and reports will join the two tables on the foodID and that makes all columns from both tables available at the same time so the calculations can easily be done.

The food list itself will be customized to you (unless multiple people have to use this app) so Coffee is defined as Coffee with Half & Half since that is how you drink it. If multiple people are using the app, you would need to define two food items. One for Coffee, black and one for Half & Half and choose both of them when doing your log. Having a customized food list will make your data entry easier.

If you are currently keeping your log with Excel, we can offer some help with importing it into Access. So far, the databases you've posted look like practice projects rather than what you actually think you need. Do you have a database that you are currently working with or a spreadsheet that can be your starting point?

If are using the sample built by Mark rather than the sample I sent, did you fix the errors I pointed out?

I've attached a database with a schema I think might work for you.

 

[Dick7Access]

The food list itself will be customized to you (unless multiple people have to use this app) so Coffee is defined as Coffee with Half & Half since that is how you drink it. If multiple people are using the app, you would need to define two food items. One for Coffee, black and one for Half & Half and choose both of them when doing your log. Having a customized food list will make your data entry easier.
ONLY ME AT THIS TIME

 

[Pat Hartman]

Your database is not normalized and will be impossible to use for any analysis. Stop now and switch to a normalized model similar to what I posted. The schema I posted will allow you to analyze calories, fat, etc over time, over meals, etc. You'll be able to tell if you are eating more or less and where during the day you are spending your calories, etc.

What you have done has all the relevant data locked into text fields where you will never be able to make use of it. Plus, you would have to type the entire string each time you enter a "food". If you don't want to switch to a normalized schema, you are better off just downloading one of the sample excel food log templates and using that.

Notice how in my sample, tblFoods contains a list of foods and in individual fields contains the calories, protein, etc. Then notice how the MealLogDetail only points to FoodID. It does NOT copy the details. The only other piece of information is how many servings you had of that item. Then the individual food items are grouped into a meal or snack, who ate it, and when. And at the top of the food chain (as we should be) are people. So the app can work for multiple people and when you make reports, you can choose which person to report on, which date period, etc.

You may need a couple more fields. I threw this together pretty quickly. But, I believe you will find that "as is", it does what you need it to do. You will have to fill tblFoods either ahead of time or as you go along. Then log your meals and the items in each meal.

 

[Dick7Access]

Your database is not normalized and will be impossible to use for any analysis. Stop now and switch to a normalized model similar to what I posted. The schema I posted will allow you to analyze calories, fat, etc over time, over meals, etc. You'll be able to tell if you are eating more or less and where during the day you are spending your calories, etc.

You are absolutely right. I admitted up front it was a mess. The easiest thing for me would be to just fill out the hand written sheet the VA gives me, but access is my hobby and I thought it would be a good time to study up on this type of DB.
I will study the last db you sent me.
Thanks.

 

[Dick7Access]

Pat,
Why is Calories, Protein, Carbs, and fat all have currency as data types

 

[Pat Hartman]

I had some time this afternoon so I put this together. It's a start. I hope you'll find it much simpler to build on than what you have at the moment.

The date time entry field requires that you type the full date because you should also enter the time. I might have done that differently to minimize the input. There is also no validation behind the forms although I did specify that most fields are required and provided defaults. Make sure you actually understand what is going on before jumping in to change things.

The switchboard is not standard. I have made modifications to it to allow it to open DS view forms and to close the app correctly but you should still be able to use the wizard to add entries although, you will have to manually add the switchboard icon to the ribbon since it is no longer there by default.

 

[Pat Hartman]

I used the currency data type because that will avoid the floating point errors that occur when you do arithmetic with single and double precision types. Currency is a scaled integer so it has a fixed 4 decimal digits. So, only if I need more than four decimal digits will I have to use single or double. Format is what gives you the dollar sign. You can use whatever format you want. I formatted the fields as standard with 1 decimal position.

 

[Dick7Access]

I had some time this afternoon so I put this together. It's a start. I hope you'll find it much simpler to build on than what you have at the moment.

The date time entry field requires that you type the full date because you should also enter the time. I might have done that differently to minimize the input. There is also no validation behind the forms although I did specify that most fields are required and provided defaults. Make sure you actually understand what is going on before jumping in to change things.

The switchboard is not standard. I have made modifications to it to allow it to open DS view forms and to close the app correctly but you should still be able to use the wizard to add entries although, you will have to manually add the switchboard icon to the ribbon since it is no longer there by default.

I don't see a switch board. In fact no forms at all. I assumed I was to make my own. Is that correct?

 

[Pat Hartman]

I uploaded a new version. I didn't change the name so you are probably looking at the original. If you have it open, I think the download won't replace it so make sure the db is closed first.

 

[Dick7Access]

I uploaded a new version. I didn't change the name so you are probably looking at the original. If you have it open, I think the download won't replace it so make sure the db is closed first.

Pat no attachment for me to download

 

[Pat Hartman]

Here it is again. This time I changed the name to avoid confusion.

 

[Dick7Access]

Pat, thank you so very much. There is enough for me to learn her for a long time. Right from the start. I usually open the splash with a autoexec marco. You are, evidently using something better, but I can't find it. Where do I look to find what loads the switchboard.
This to me is funny, but in my circles I am the Access expert. In this circle I am still on bottle milk, but I like learning.

 

[AccessBlaster]

Dick:
Is this what your look for?