Prev: Report Design question
Next: highlight current record in continious form
From: WhathaveIdone? on 10 Aug 2010 17:33
I have some tables, trying to create a normalized many to many
situation. I the the following tables:
tblStates (US States, that is)
tblProducts
ProdStates (to link the two tables together)
The tblStates and tblProducts tables have a many to many relationship.
What is the best way to create a from to be able to select all the
applicable US States that pertain to this product? No. I am not using
a multi select field in my tables as of yet. I was avoiding that
option.

However, it seems to boggle me that I cannot wrap my mind around this
concept, relationally.

Sincerely,
WhathaveIdone?
From: Bob Quintal on 10 Aug 2010 17:52
"WhathaveIdone?" <brasus04(a)gmail.com> wrote in
news:12810560-2455-4d9d-b822-44a4ba261212(a)g6g2000pro.googlegroups.com
:

> I have some tables, trying to create a normalized many to many
> situation. I the the following tables:
> tblStates (US States, that is)
> tblProducts
> ProdStates (to link the two tables together)
> The tblStates and tblProducts tables have a many to many
> relationship. What is the best way to create a from to be able to
> select all the applicable US States that pertain to this product?
> No. I am not using a multi select field in my tables as of yet. I
> was avoiding that option.
>
> However, it seems to boggle me that I cannot wrap my mind around
> this concept, relationally.
>
> Sincerely,
> WhathaveIdone?
>

I'd create a form with a combobox to select a Product, two listboxes,
one showing the states that have been assigned to the Product and the
second listing the states not selected for that Product and a pair of
command buttons, the first to add a row of the selected Product and
State to the ProdStates table and the other to delete the row from
the Prodstates table.

I have a little demo database that shows how to code this. If you
want it, email me.
From: Marshall Barton on 10 Aug 2010 17:56
WhathaveIdone? wrote:

>I have some tables, trying to create a normalized many to many
>situation. I the the following tables:
>tblStates (US States, that is)
>tblProducts
>ProdStates (to link the two tables together)
>The tblStates and tblProducts tables have a many to many relationship.
>What is the best way to create a from to be able to select all the
>applicable US States that pertain to this product? No. I am not using
>a multi select field in my tables as of yet. I was avoiding that
>option.
>

The usual way to do that is to have a main form bound to
tblProducts and a subform bound to tblProducts with the
productid fields linking them together.

The subform would have a combo box bound to the state field
and use tblStates as its row source.

--
Marsh
From: WhathaveIdone? on 10 Aug 2010 18:16
On Aug 10, 3:56 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> WhathaveIdone? wrote:
> >I have some tables, trying to create a normalized many to many
> >situation. I the the following tables:
> >tblStates (US States, that is)
> >tblProducts
> >ProdStates (to link the two tables together)
> >The tblStates and tblProducts tables have a many to many relationship.
> >What is the best way to create a from to be able to select all the
> >applicable US States that pertain to this product? No. I am not using
> >a multi select field in my tables as of yet. I was avoiding that
> >option.
>
> The usual way to do that is to have a main form bound to
> tblProducts and a subform bound to tblProducts with the
> productid fields linking them together.
>
> The subform would have a combo box bound to the state field
> and use tblStates as its row source.
>
> --
> Marsh

Now, what I was trying to avoid is a form with 50 checkboxes that are
tied to the productID. Maybe I shouldn't be avoiding this method at
all. Any thoughts? Pros vs. Cons?
I also should mention that selecting a state means that product is NOT
available there (Its a lot easier than selecting which states it IS
available in. Usually there are only 10 or less). The results, I want
to concatenate in my report like AZ, DE, etc
That listbox idea sounds like it might work. If you do have code on
that, that would be great. I'm not a pro, but I can read the code at
least.
From: WhathaveIdone? on 10 Aug 2010 18:27
On Aug 10, 3:52 pm, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote:
> "WhathaveIdone?" <brasu...(a)gmail.com> wrote innews:12810560-2455-4d9d-b822-44a4ba261212(a)g6g2000pro.googlegroups.com
> :
>
>
>
>
>
> > I have some tables, trying to create a normalized many to many
> > situation. I the the following tables:
> > tblStates (US States, that is)
> > tblProducts
> > ProdStates (to link the two tables together)
> > The tblStates and tblProducts tables have a many to many
> > relationship. What is the best way to create a from to be able to
> > select all the applicable US States that pertain to this product?
> > No. I am not using a multi select field in my tables as of yet. I
> > was avoiding that option.
>
> > However, it seems to boggle me that I cannot wrap my mind around
> > this concept, relationally.
>
> > Sincerely,
> > WhathaveIdone?
>
> I'd create a form with a combobox to select a Product, two listboxes,
> one showing the states that have been assigned to the Product and the
> second listing the states not selected for that Product and a pair of
> command buttons, the first to add a row of the selected Product and
> State to the  ProdStates table and the other to delete the row from
> the Prodstates table.
>
> I have a little demo database that shows how to code this. If you
> want it, email me.

I'd love to see your database and that option. It sounds better than
anything else I've seen.
 |  Next  |  Last
Pages: 1 2
Prev: Report Design question
Next: highlight current record in continious form