Help please with combo boxes and multiple selections.

[FatMan]

Hi all:
I need some help with my database and am not sure if the post belongs here
or in one of the other discussion groups. If this is the wrong group please
excuse my mistake.

What I have is a database that records and tracks what agriculture chemicals
have been applied to a grower's orchard, what part of the orchard the spray
has been applied to and what pest was targeted by the application of the
spray. In my current database I have a combo box where the user can select:

Block - part of orchard sprayed (combo box name - SprayBlockID)
Product - agriculture chemical sprayed on the block (combo box name -
SprayProdID)
Pest - pest targeted by product sprayed (combo box name - SprayPestID)

Everything works fine when the grower only applies one product, over one
block and targeting one best. Management approved this but when it was
rolled out to our growers we soon discovered that they mix any number of
products in a single tank and then proceed to spray this over any number of
blocks and target any number of pest.

My question is this....how can I deal with the multiple products being
applied over multiple blocks targeting multiple pest. I would really like to
keep the combo boxes so the user has a drop down list to select everything
from.

Can this be done and if so how?

Thanks,
FatMan

 

[John W. Vinson]

Hi all:
I need some help with my database and am not sure if the post belongs here
or in one of the other discussion groups. If this is the wrong group please
excuse my mistake.

What I have is a database that records and tracks what agriculture chemicals
have been applied to a grower's orchard, what part of the orchard the spray
has been applied to and what pest was targeted by the application of the
spray. In my current database I have a combo box where the user can select:

Block - part of orchard sprayed (combo box name - SprayBlockID)
Product - agriculture chemical sprayed on the block (combo box name -
SprayProdID)
Pest - pest targeted by product sprayed (combo box name - SprayPestID)

Everything works fine when the grower only applies one product, over one
block and targeting one best. Management approved this but when it was
rolled out to our growers we soon discovered that they mix any number of
products in a single tank and then proceed to spray this over any number of
blocks and target any number of pest.

My question is this....how can I deal with the multiple products being
applied over multiple blocks targeting multiple pest. I would really like to
keep the combo boxes so the user has a drop down list to select everything
from.

Can this be done and if so how?

Thanks,
FatMan

You need another table.

You cannot store multiple values (chemicals) in one field, or select them with
one combo box. For each many to many relationship you need *THREE* tables,
e.g.:

Pests
PestID <primary key>
SpeciesName
CommonName
<other info about the pest>

Product
ProductID <primary key>
Tradename
ChemicalName
MSDS <maybe a hyperlink to the safety datasheet document>
<other info about the agent>

ProductUsage
PestID <what pest are we talking about>
ProductID <what are we trying to kill it with>
<information about this agent on this pest, e.g. recommended application
rate, frequency, etc.>

Blocks
BlockNo <primary key>
<GPS coordinates or other info about the location of the block>

Applications
BlockNo <what block you're spraying>
ProdcutID <what are you spraying it with>
ApplicationDate
ApplicationRate
<etc>

You can thus store multiple applications (which could be applied as a mixture
or separately) of multiple agents on multiple blocks, just by adding records
to the Applications table.

(don't tell my organic gardener wife that I posted this <g>)

 

[FatMan]

John:
Thanks for your post. I am sorry but I must not have provided enough
background info. I do have different tables for the blocks, pest, product as
well as applications (the actually spraying details). So I do have
everything in multiple tables as you have suggested.

My problem is more of a data entry issue. The grower doesn't want to have
to do any more data entry than absolutely necessary. For example….. a grower
puts chemical A and B in the spray tank and then proceeds to spray block 1
and 2 targeting pest X and Z.
With my current database using the combo boxes the grower would have to
enter 8 records.

This is unacceptable to the grower as my example above is very simple but in
reality the grower might have any number of chemicals in the tank, targeting
any number of pest and spray it over any number of blocks. What the grower
would like to do is make his/her selections of chemicals, pest and blocks and
have the program automatically enter all the required records.

Any ideas?

Thanks,
FatMan

 

[John W. Vinson]

This is unacceptable to the grower as my example above is very simple but in
reality the grower might have any number of chemicals in the tank, targeting
any number of pest and spray it over any number of blocks. What the grower
would like to do is make his/her selections of chemicals, pest and blocks and
have the program automatically enter all the required records.

Doable, with some hassle. You could have a Form with either subforms or
multiselect listboxes, and VBA code to construct an append query. It's maybe
more complex than a simple post on a newsgroup will be able to cover though!

At the least I'd suggest two more tables - Tanks (or Mixtures or
Formulations), related many to many to Chemicals by a MixtureComponents table
which would let you define the contents of a tank (ingredients and amounts).
Then you could have the input form allow you to spray 10 gal/acre of Tank
31...