Why is this strSQL not working??? Please Help
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
|