Access Database from Excel worksheet

[Les]

I've a worksheet to keep track of custom planters:


Red Yellow Green Blue
-------------------------------------------------------
5) 2 2
-------------------------------------------------------
2) 2 1 1
-------------------------------------------------------
3) 1 1 2
-------------------------------------------------------

There are 5 of the first planter. It has 2 red and 2 green.
There are 2 of the second planter. 2 yellow, 1 green, and 1 blue.
Etc.

I would like to use this as a "template" to keep track of the data with
Access'07. In the example above there are 10 (5+2+3) planters, but only 3
different variations. The database should have a place to keep track of each
variation of planter (howmany, ofwhat, howmanyofwhat).

I am unable to visualize how Access'07 would handle this. It seems like it
should be simple. Any help getting me going is most appreciated.

Thanks, Les

 

[Duane Hookom]

I am unable to understand how 2 red + 2 green = 5.
I would think you need to track planters where each planter is a record in a
table. The record might store the color and whatever else is an attribute of
the planter.

 

[Les]

I am unable to understand how 2 red + 2 green = 5.
There are "5" planters which each have 2 red and 2 green.
Each planter will have 1 or more of any of the four colors. And there will
be specific numbers of different variations.
In the first example (first row):
There are "5" planters. Each of these five has 2 red and 2 green. These "5"
planters might be called "redgreen" planter.
My problem is I can't visualize know how to keep track of all of these
'attributes' (the "5", each having 2 red, and each having 2 green) in the
record.
Thanks, Les

 

[Duane Hookom]

I think I understand. Is this like having 5 flower planters each with 2 red
flowers and 2 green flowers. If this is the case, I would consider creating a
table of planters:
tblPlanters
=============
plaPlaID autonumber primary key
plaDescription
.....

tblPlanterFlowers
============
plfPlFID autonumber primary key
plfPlaID long, links to tblPlanters.plaPlaID
plfColor red, green, or other color
plfQty quanitity of each color in the planter

 

[Les]

Please help me understand this (from your example):
tbl PlanterFlowers - Table with 4 fields
plfPlFID - is a field identifying each record
plfPlaID - ties the record to the 'tblPlanter' records
plfColor - is a field that accepts input for "one"
particular color?
plfQty - the field which keeps track of how many of
"that one" particular color.

Suppose 'plfColor' has the color 'red' and plfQty has the number '2'.
Now suppose that this particular planter also has 'green' in it. Where is
this data kept?

Thanks again,
Les

 

[Duane Hookom]

If you need to add another color, you add another record to tblPlanterFlowers
with the same plfPlaID, plfColor="green" and the quantity. That's how
relational databases work. You would have color records for each color
related to the planter record.

 

[Les]

Duane,
I'm progressing with this project little by little. But I'm stuck.
Here's what I've got:
tblPlanter fieldPlanterID fieldDescription
fieldQuantity
tblPlanterSelections fieldID fieldPlistID fieldPlanterID
fieldPltTypeQuantity
tblPlantList fieldPlistID fieldPlantName
fieldPlantPicture (attachment)
-----------------------
formPlanter
subFormPlanterSelection
-----------------------
formPlanter lets me enter fieldDecription and fieldQuantity
It has the subFormPlanterSelection

subFormPlanterSelection lets me enter individual selections to be included
in each planter.
It has fieldPltTypeQuantity, and fieldPlantName (lookup from
tblPlantList)

In the subFormPlanterSelection I can choose 'how many' and 'PlantName' with
a comboBox linked to tblPlantList.
And, it does just what it should as long as I stop there. However, as soon
as I try to also include fieldPlantPicture Access will not allow it.

***Do you know if it is possible to include a picture control in the
subFormPlanterSelection?***

Thanks, Les

 

[Duane Hookom]

It is difficult to display an image attached to a record on a continuous
form. I have only heard of a possible solution that uses an unbound "pseudo"
subform that loads information from multiple records into unbound controls.

I would probably add an image control onto the main form and then change the
picture/source of the control as the user changes focus to different records
in the subform.

 

[Les]

Duane,
It displays the 'attachment' picture as long as the record to which the
subForm is working with has already been established. If I add records to
the table using only tblPlantList 'text' data (fieldPlantName) there are no
problems. I can build and add records at will. Once I add the 'attachment'
control to the subForm, all previously established record WILL show the
coresponding picture. However as soon as I try to add a new record to the
subForm, the program balks and will not let me precede.
If I do what you suggest below I can display only 'one' picture at a time.
Right? Is there a way to display all plant selections simultaneously? In
other word, if a planter has red, green, and yellow plants in it, can the
pictures associated with those 3 plants be displayed at the same time?

Thanks, Les