This may be an easy enough fix. It may be challenging though, so if
you are up to the task, I'd love your thoughts. But those of you who
are familiar with Allen Browne, he has been a lifesaver to me and is
an absolute genius. I am using his ConcatRelated Function utility.
http://allenbrowne.com/func-concat.html I have a layout as such:

tblStates with fields: StateID (autonumber), StateAbb, and StateName
tblProducts including field: ProdID(autonumber)
tblProdState with fields: ProdID, StateID

I am treating this as a many to many relationship because many states
can have many products and many products can have many states. (i.e.
Product1 is available in AL,GA,NY; Product2 is available in CA, GA,
MI, NY, UT)
1st Question: Is this an appropriate relationship?

I am using Allen Browne's ConcatRelated Function utility
http://allenbrowne.com/func-concat.html to show this on a form:
frmMain.txtStates
the ConcatRelated function is working perfectly. The ConcatRelated
shows the list of related data per record on my form separated by
",".
However, I want to show the StateAbb for each of the stateID's in
txtStates instead of the StateID in that list. So, I have not been
able to successfully nest a dlookup function into that function.
2nd Question: Any thoughts about where to do that and/or how?

WhathaveIdone? wrote:

SQL statment it would be hard for someone to make a really valid suggestion.

Your statement "However, I want to show the StateAbb for each of the
stateID's in txtStates instead of the StateID in that list." What does
that mean? Does it work but you are getting a state number instead of
abbreaviation? What is a small example of your current output?

Allen's examples use a "table" to use in the concatenation. You could
use a query if you need to concatenate multiple fields.
Select [Name] & "-" & [Addr1] As NameAndAddress From ...
and use that query in your concatenation function.

On Sep 16, 10:48*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
I always worry about making sure I have enough information in a post
to let others know what I am doing. So I appologize if it is not clear
enough. Let me try and explain.
I am doing my best to keep this normalized in my DB as best I can. I
have a text box on a form where I have used the function from Allen
Browne's ConcatRelated utility. That function pulls the data as such:

=ConcatRelated("StateID","tblProdState","ProdID =" & [ProdID])
(The form is based on the table tblProducts and so it includes the
field ProdID from that table)
tblStates M:M tblProducts (therefore, I have tblProdState to try and
normalize the data)

From the ConcatRelated function, I get the StateID's separated by
commas(ex: 3, 15, 46) where I would like, instead, to show the
corresponding StateAbb from the tblStates table (ex:AL, TX, VA) where
StateID corresponds . Normally, I would use Dlookup to get the value I
want from the tblStates table for a single value in my txtStates
control on the form. But where this field has multiple values for each
of the ProdID records, I don't know as readily where to put or how to
use the Dlookup function in this case, or if I would even need to use
it at all (i.e.query or other alternative) I hope that didn't just
confuse things more and I was a little bit more descriptive of the
issue. But please let me know if you have questions.
-WhathaveIdone?

WhathaveIdone? wrote:

In Allen's example he has
SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders",
"CompanyID = " & [CompanyID])
FROM tblCompany;

So add StateAbbr to your list in the Select statement.
Or
=ConcatRelated("StateAbbr","tblProdState","ProdID =" & [ProdID])

You use tblProdState. I'd substitute that with a query most likely.
I'd link the state table with tblProdState and be done with it. Ex:
=ConcatRelated("StateAbbr","qryProdState","ProdID =" & [ProdID])

It seems pretty basic, your problem. I suppose you can make it more
complicated than it is. Then again, I may not understand your problem.

On Sep 16, 12:44*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

You totally solved my problem!! I knew I had to be making it way
harder than it had to be.
So I created qryProdState from tblProdState. I added a field
Statelookup("State","qryProdState","StateID = " & [StateID])
Then, I put =ConcatRelated("State","qryProdState","ProdID =" &
[ProdID])

Sometimes, you just have to bounce it off someone to see things
clearly. I could have made a real mess of this one. Thanks!!

-WhathaveIdone?

WhathaveIdone? wrote:

Hmmm...the dlookup can make it slower. I figured you were getting a
mass of records for a record source. Anyway, do you have a States
table? If so, create a query and drop tblProdState and StateCodes to
it. You can use an inner join on StatesID but you can use a left join
if there's some recs without a state ID in the ProdState table. Then
drag the * from tblProdState and the state abbrv or state name to
another column. Like I said, that'd be faster than Dlookup and less
typing. Anyway, glad you have it figured out.