Brian,

You missed one fo my responses ( 8-) I've typed so, I feel like I missed
one of my responses!!!) btTerritoryStateID is NOT a primary key or this
thing would have worked ages ago.

I got this whole thing to work by making the query Inconsistent Updates.
Not my first choice but after trying to convince the end-user to let me set
up the form. (In one of my former replies I stated that he insisted on
doing the forms.)

tblBrokerTerritory.btTerritoryStateID is the NOT primary key of
tblBrokerTerritory


"Brian" s@y wrote in message ...
Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID to
tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you
wanted it to update.

Brian

"Gina Whipp" wrote in message
...
Query still unupdateable

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II


"Brian" s@y wrote in message
...
Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

"Gina Whipp" wrote in message
...
Brian,

Neither of those work and both StateID are foreign keys linked to a
tblStates table. Are you saying I need to turn btTerritoryStateID into
a Primary key?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II


"Brian" s@y wrote in message
...
No don't do both, if you do the inner join btBrokerID will be a field
you can select straight off
SELECT tblLister.lListerID,
btBrokerID AS BrokerID,
tblListerListing.llListingID,
tblListerListing.llSaleAmount,
tblListerListing.llInActiveOrSold,
tblListerListing.llListingTypeID,
tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue,
tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent,
tblListerListing.llGrossSDEAmount,
tblListerListing.llTerritoryName,
tblListerListing.llStateID
FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN
tblListerListing ON tblLister.lListerID = tblListerListing.llListerID)
ON
tblBrokerTerritory.btTerritoryStateID =
tblListerListing.llStateID;
(I'm assuming that your join is correct for the relationship)

OR with the subquery
SELECT tblLister.lListerID,
tblLister.lFirstName,
tblLister.lLastName,
tblLister.leMailAddress,
tblLister.lFaxNumber,
tblLister.lCellPhoneNumber,
tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
(SELECT btBrokerID FROM tblBrokerTerritory WHERE
tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID) AS
BrokerID,
tblListerListing.llListingID,
tblListerListing.llSaleAmount,
tblListerListing.llInActiveOrSold,
tblListerListing.llInvestmentPotential,
tblListerListing.llListingTypeID,
tblListerListing.llTerritoryPopulation,
tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue,
tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent,
tblListerListing.llGrossSDEAmount,
tblListerListing.llStateID,
tblListerListing.llTerritoryName
FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID

"Gina Whipp" wrote in message
...
here you go...

SELECT tblLister.lListerID,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent,
tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName,
tblListerListing.llStateID
FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN
tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID
= tblListerListing.llStateID;


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II


"Brian" s@y wrote in message
...
ok.

"Gina Whipp" wrote in message
...
Yes I tried... you want to see the SQL? Maybe I did something
wrong...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II


"Brian" s@y wrote in message
...
I don't know your relationships for sure but once you include the
primary keys it looks like it should be updateable. The subquery
should work always, did you try it?
Brian

"Gina Whipp" wrote in message
...
Yes they are slow but trying to an inner join makes the query
UNupdateable. Ibelieve I may start over again and try another
way...
--
Gina Whipp

"I feel I have been denied critical, need to know,
information!" - Tremors II


"Brian" s@y wrote in message
...
You should try to avoid those 'D' functions in your queries
since they are very slow.
Why don'y you try using a subquery instead...
(SELECT btBrokerID FROM tblBrokerTerritory WHERE
btTerritoryStateID = tblListerListing.llStateID) AS BrokerID
or just do another inner join on
tblBrokerTerritory.btTerritoryStateID =
tblListerListing.llStateID
Brian

"Gina Whipp" wrote in message
...
If I take out the Dlookup part all is well but I really need
that Dlookup. Can someone explian why?

Const strSQL = "SELECT tblLister.lListerID,
tblLister.lFirstName, tblLister.lLastName,
tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='"
& [llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount,
tblListerListing.llInActiveOrSold,
tblListerListing.llInvestmentPotential,
tblListerListing.llListingTypeID,
tblListerListing.llTerritoryPopulation,
tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue,
tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent,
tblListerListing.llGrossSDEAmount, tblListerListing.llStateID,
tblListerListing.llTerritoryName FROM tblLister INNER JOIN
tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID"

I use the above because of the below:

If Not Me.cboListTypeID = "" Then
Select Case cboListTypeID
Case "A"
strFilterSQL = strSQL
Me.RecordSource = strFilterSQL
Case "M"
strFilterSQL = strSQL & " Where [BrokerID] =
Forms![frmLogOn]![cboLoginID];"
Me.Form.AllowAdditions = False
Me.RecordSource = strFilterSQL
Case Else
strFilterSQL = strSQL & " Where [llListingTypeID] = '" &
cboListTypeID & "';"
Me.RecordSource = strFilterSQL
End Select
End If

Thanks to anyone who can help,
Gina Whipp

"I feel I have been denied critical, need to know,
information!" - Tremors II