How to pull data from one table and save it to another

[Lorraine]

hi,
I want to pull data from more then one field ( as it seems with a combo
box with multiple fields listed in a row ) and have it save these fields to a
diffrent table. What I'm trying to do is take a Whole record and save that
record to a new table.

Thanks
L

 

[Rick Brandt]

hi,
I want to pull data from more then one field ( as it seems with a
combo
box with multiple fields listed in a row ) and have it save these
fields to a diffrent table. What I'm trying to do is take a Whole
record and save that record to a new table.

Thanks
L

One "how?" is to use the AfterUpdate event of the ComboBox and use that to copy
the values from the other columns in the drop-down list to bound controls on the
form...

Me.SomeControl = Me.ComboBoxName.Column(1)
Me.SomeOtherControl = Me.ComboBoxName.Column(2)

....but the more relevent question is "why?". Copying data redundantly from one
table to another beyond the primary key to use as a foreign key is (almost
always) a bad idea and suggests a flaw in the design of your database.

 

[Lorraine]

ok - what I'm trying to create is a food database that allows me to keep
track of my nutritional amounts. So I thought would be workable is a table
with a form that allows me to enter in the foods with there nutritional facts
then have a form that allows me to enter in my daily food intake but in that
form I want to be able to select the food from the first table and the rest
of the nutritional facts are pulled from the first food table and then I
would be able to save it to another table called daily food table. If you
have any ideas I am certainly all ears

Thanks
L

 

[Rick Brandt]

ok - what I'm trying to create is a food database that allows me to
keep track of my nutritional amounts. So I thought would be workable
is a table with a form that allows me to enter in the foods with
there nutritional facts then have a form that allows me to enter in
my daily food intake but in that form I want to be able to select the
food from the first table and the rest of the nutritional facts are
pulled from the first food table and then I would be able to save it
to another table called daily food table. If you have any ideas I am
certainly all ears

Store ONLY the primary key from the first table into the second. All other
information about the food item selected can be pulled from the first table
using a variety of lookup or query mechanisms. That is how relational databases
work.

 

[Lorraine]

I had tried lookup options but that seems to only allow to lookup each field
one at a time. I would like to select a food in a form and the rest of the
fields like Caleries, protien, carbohydrates fill in with the amounts from
that record in the first field. Lookups don't seem to work that way. The
query option sounds possible for what I want but I'm not very good when it
comes to this.

 

[Rick Brandt]

I had tried lookup options but that seems to only allow to lookup
each field one at a time. I would like to select a food in a form and
the rest of the fields like Caleries, protien, carbohydrates fill in
with the amounts from that record in the first field. Lookups don't
seem to work that way. The query option sounds possible for what I
want but I'm not very good when it comes to this.

Create a query that contains all the fields from your second table and all of
the desired fields from the first table (except the primary key). Create a join
between the two on the Food-ID key field.

That query will show all of your fields from the second table and for each food
item entered will also show all of the data you want to see from the first
table. Switch to datasheet view on the query and make sure that you can still
edit the fields from the second table (you would not want to update the fields
from the first table using this query).

If that works then save the query and bind your form currently bound to the
second table to the query. As soon as you enter a food itme you should see all
of the related fields from the first table automatically populate, but these
will be lookups not copies. Just in case, set the Locked property of any
control displaying fields from the first table to True. You don't want to
accidentally change a value in the first table from this form.

 

[Lorraine]

Thats great Rick - Disregard the post in the other group. this is the
direction I was hoping someone could point me to.

Thanks again I will post back to let you know if I was able to do what you
say.

 

[Lorraine]

Hi & Happy Holidays

I think I did as you said correctly. when I create a form using the new
query I drag over the fields and they do not show in the form when I change
from design view to regular view. The form is blank. I even created combo &
lookup box in the form and nothing shows up all field disappear in the form
when I try to use the form????

 

[John W. Vinson]

I think I did as you said correctly. when I create a form using the new
query I drag over the fields and they do not show in the form when I change
from design view to regular view. The form is blank. I even created combo &
lookup box in the form and nothing shows up all field disappear in the form
when I try to use the form????

This suggests that the Query you're using a) doesn't return any records so you
can't see the existing records, and b) is not updateable, so you can't see the
New Record either.

Please open the Query in SQL view (View... SQL on the menu) and post the SQL
text here. It will also help if you indicate which field is the Primary Key of
each table and what relationships you have defined.

Often it's best to handle one to many relationships by using a Form (for the
"one" side table) with a Subform (for the "many") rather than basing a form on
a query.

John W. Vinson [MVP]

 

[Lorraine]

I will John & thanks for repling on the Holidays. I don't know why I 'm so
caught up in this databse but I want it to work so bad. LOL

Hold on and I 'll post it tonight.

Thanks again everyone for help me

L

 

[Lorraine]

Wow - so many thingings that can be done. Heres the text from that query
_________________________________________________________________
SELECT [daily food table].Date, [daily food table].Time, [daily food
table].Food AS [daily food table_Food], [daily food table].[Serving size] AS
[daily food table_Serving size], [daily food table].GramsCarbohydrates AS
[daily food table_GramsCarbohydrates], [daily food table].GramsProtein AS
[daily food table_GramsProtein], [daily food table].GramsFat AS [daily food
table_GramsFat], [daily food table].Calories AS [daily food table_Calories],
[daily food table].Sodium AS [daily food table_Sodium], [daily food
table].Vitamins AS [daily food table_Vitamins], [daily food table].PersonID
AS [daily food table_PersonID], [daily food table].Notes AS [daily food
table_Notes], [daily food table].Sugar AS [daily food table_Sugar], [Food
Table].Food AS [Food Table_Food], [Food Table].[Serving size] AS [Food
Table_Serving size], [Food Table].GramsCarbohydrates AS [Food
Table_GramsCarbohydrates], [Food Table].GramsProtein AS [Food
Table_GramsProtein], [Food Table].GramsFat AS [Food Table_GramsFat], [Food
Table].Calories AS [Food Table_Calories], [Food Table].Sodium AS [Food
Table_Sodium], [Food Table].Vitamins AS [Food Table_Vitamins], [Food
Table].PersonID AS [Food Table_PersonID], [Food Table].Notes AS [Food
Table_Notes], [Food Table].Sugar AS [Food Table_Sugar], [Food Table].*
FROM [Food Table] INNER JOIN [daily food table] ON ([Food Table].Food =
[daily food table].Food) AND ([Food Table].[Serving size] = [daily food
table].[Serving size]) AND ([Food Table].GramsCarbohydrates = [daily food
table].GramsCarbohydrates) AND ([Food Table].GramsProtein = [daily food
table].GramsProtein) AND ([Food Table].GramsFat = [daily food
table].GramsFat) AND ([Food Table].Calories = [daily food table].Calories);

_____________________________________________________________________

 

[John W. Vinson]

Wow - so many thingings that can be done. Heres the text from that query
_________________________________________________________________
SELECT [daily food table].Date, [daily food table].Time, [daily food
table].Food AS [daily food table_Food], [daily food table].[Serving size] AS
[daily food table_Serving size], [daily food table].GramsCarbohydrates AS
[daily food table_GramsCarbohydrates], [daily food table].GramsProtein AS
[daily food table_GramsProtein], [daily food table].GramsFat AS [daily food
table_GramsFat], [daily food table].Calories AS [daily food table_Calories],
[daily food table].Sodium AS [daily food table_Sodium], [daily food
table].Vitamins AS [daily food table_Vitamins], [daily food table].PersonID
AS [daily food table_PersonID], [daily food table].Notes AS [daily food
table_Notes], [daily food table].Sugar AS [daily food table_Sugar], [Food
Table].Food AS [Food Table_Food], [Food Table].[Serving size] AS [Food
Table_Serving size], [Food Table].GramsCarbohydrates AS [Food
Table_GramsCarbohydrates], [Food Table].GramsProtein AS [Food
Table_GramsProtein], [Food Table].GramsFat AS [Food Table_GramsFat], [Food
Table].Calories AS [Food Table_Calories], [Food Table].Sodium AS [Food
Table_Sodium], [Food Table].Vitamins AS [Food Table_Vitamins], [Food
Table].PersonID AS [Food Table_PersonID], [Food Table].Notes AS [Food
Table_Notes], [Food Table].Sugar AS [Food Table_Sugar], [Food Table].*
FROM [Food Table] INNER JOIN [daily food table] ON ([Food Table].Food =
[daily food table].Food) AND ([Food Table].[Serving size] = [daily food
table].[Serving size]) AND ([Food Table].GramsCarbohydrates = [daily food
table].GramsCarbohydrates) AND ([Food Table].GramsProtein = [daily food
table].GramsProtein) AND ([Food Table].GramsFat = [daily food
table].GramsFat) AND ([Food Table].Calories = [daily food table].Calories);

Well... this doesn't seem right.

You're joining the two tables on what looks like *EVERY SINGLE FIELD*! Unless
there is a record in [daily food table] with exactly equal values of every one
of Food and Serving Size and Grams Carbohydrate and... <etc.> you won't see
anything; and if there IS a record with all those fields in both tables you're
storing all that data redundantly.

STOP. You're going about this *wrong*. Sorry, but you really are!

Each table should have:

- a Primary Key, some unique value that exists only in that one record in the
table.

- Fields containing data pertaining to that record. Attributes of the food -
GramsProtein, GramsFat, Calories - etc. - should exist only in the Foods
table; fields pertaining to a serving or a given day's portion - ServingSize
perhaps - should exist *ONLY* in the daily table.

You do not need, and emphatically *should not have*, lots of fields
overlapping in the two tables! You're using a relational database; use it
relationally! Relational databases use the "Grandmother's Pantry Principle":
"A place - ONE place! - for everything, everything in its place". The Foods
table should store information about foods... and that information should
*ONLY* be stored there. The servings table should contain a "foreign key" -
the Food if that's the primary key, or the FoodID if you have a numeric
primary key - as a link to the foods table, *AND NOTHING ELSE* about the food.
You don't *need* to store that information redundantly; that's what a Query or
a Subform or a Combo Box are for, to join data from two different tables
together.

Seriously: take a look at some of these tutorials. The relational data model
takes some real work to get your head around, but you'll find it incredibly
powerful when you do.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Have a merry Christmas... now I've got to go check my apple dumplings and see
if the pumpkin pie (home-made whole wheat crust, local organic pumpkin,
neighbor's fresh eggs <g>) is cool enough to eat!

John W. Vinson [MVP]

 

[Lorraine]

Thank you so much for your help and leading me in the correct direction. I
know that I'm going about it the incorrect way because I really do not
understand this part of access. I'm looking at these Tutorials tonight.

Thanks again I really do appreciate it

L

Wow - so many thingings that can be done. Heres the text from that query
_________________________________________________________________
SELECT [daily food table].Date, [daily food table].Time, [daily food
table].Food AS [daily food table_Food], [daily food table].[Serving size] AS
[daily food table_Serving size], [daily food table].GramsCarbohydrates AS
[daily food table_GramsCarbohydrates], [daily food table].GramsProtein AS
[daily food table_GramsProtein], [daily food table].GramsFat AS [daily food
table_GramsFat], [daily food table].Calories AS [daily food table_Calories],
[daily food table].Sodium AS [daily food table_Sodium], [daily food
table].Vitamins AS [daily food table_Vitamins], [daily food table].PersonID
AS [daily food table_PersonID], [daily food table].Notes AS [daily food
table_Notes], [daily food table].Sugar AS [daily food table_Sugar], [Food
Table].Food AS [Food Table_Food], [Food Table].[Serving size] AS [Food
Table_Serving size], [Food Table].GramsCarbohydrates AS [Food
Table_GramsCarbohydrates], [Food Table].GramsProtein AS [Food
Table_GramsProtein], [Food Table].GramsFat AS [Food Table_GramsFat], [Food
Table].Calories AS [Food Table_Calories], [Food Table].Sodium AS [Food
Table_Sodium], [Food Table].Vitamins AS [Food Table_Vitamins], [Food
Table].PersonID AS [Food Table_PersonID], [Food Table].Notes AS [Food
Table_Notes], [Food Table].Sugar AS [Food Table_Sugar], [Food Table].*
FROM [Food Table] INNER JOIN [daily food table] ON ([Food Table].Food =
[daily food table].Food) AND ([Food Table].[Serving size] = [daily food
table].[Serving size]) AND ([Food Table].GramsCarbohydrates = [daily food
table].GramsCarbohydrates) AND ([Food Table].GramsProtein = [daily food
table].GramsProtein) AND ([Food Table].GramsFat = [daily food
table].GramsFat) AND ([Food Table].Calories = [daily food table].Calories);

Well... this doesn't seem right.

You're joining the two tables on what looks like *EVERY SINGLE FIELD*! Unless
there is a record in [daily food table] with exactly equal values of every one
of Food and Serving Size and Grams Carbohydrate and... <etc.> you won't see
anything; and if there IS a record with all those fields in both tables you're
storing all that data redundantly.

STOP. You're going about this *wrong*. Sorry, but you really are!

Each table should have:

- a Primary Key, some unique value that exists only in that one record in the
table.

- Fields containing data pertaining to that record. Attributes of the food -
GramsProtein, GramsFat, Calories - etc. - should exist only in the Foods
table; fields pertaining to a serving or a given day's portion - ServingSize
perhaps - should exist *ONLY* in the daily table.

You do not need, and emphatically *should not have*, lots of fields
overlapping in the two tables! You're using a relational database; use it
relationally! Relational databases use the "Grandmother's Pantry Principle":
"A place - ONE place! - for everything, everything in its place". The Foods
table should store information about foods... and that information should
*ONLY* be stored there. The servings table should contain a "foreign key" -
the Food if that's the primary key, or the FoodID if you have a numeric
primary key - as a link to the foods table, *AND NOTHING ELSE* about the food.
You don't *need* to store that information redundantly; that's what a Query or
a Subform or a Combo Box are for, to join data from two different tables
together.

Seriously: take a look at some of these tutorials. The relational data model
takes some real work to get your head around, but you'll find it incredibly
powerful when you do.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Have a merry Christmas... now I've got to go check my apple dumplings and see
if the pumpkin pie (home-made whole wheat crust, local organic pumpkin,
neighbor's fresh eggs <g>) is cool enough to eat!

John W. Vinson [MVP]