If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
Why is this strSQL not working??? Please Help
No apologies needed... can take all the ideas I can get!!!
-- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "SteveS" limbim53 at yahoo dot com wrote in message ... My apologies for butting in........ -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
#32
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#33
|
|||
|
|||
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 |
#34
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#35
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#36
|
|||
|
|||
Why is this strSQL not working??? Please Help
Oh, ok, well if you got it working that good.
Brian "Gina Whipp" wrote in message ... 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 |
#37
|
|||
|
|||
Why is this strSQL not working??? Please Help
OK,
In your original post you said: "If I take out the Dlookup part all is well ......". What does the SQL string look like if you add: Debug.Print SQL ? If you replace the DLOOKUP() with a value, say 5, is all still well? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, 5 AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, snip Debug.Print SQL What does the string look like? I still say that embedding a VBA function inside a SQL string, the function won't be evaluated. The function return value needs to be concatenated to the SQL string. Did you try concatenating the DLOOKUP() function to the SQL string (see my first post)? My ideas on your problem..... I'll back to sleep now. -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
#38
|
|||
|
|||
Why is this strSQL not working??? Please Help
SLEEP!!! You get to SLEEP???? I got it working Steve by using Inconsistent
Updates, not the best way but it works for what the end-user wants and no talking him out of it (I tried). -- Gina Whipp "SteveS" limbim53 at yahoo dot com wrote in message ... OK, In your original post you said: "If I take out the Dlookup part all is well .....". What does the SQL string look like if you add: Debug.Print SQL ? If you replace the DLOOKUP() with a value, say 5, is all still well? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, 5 AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, snip Debug.Print SQL What does the string look like? I still say that embedding a VBA function inside a SQL string, the function won't be evaluated. The function return value needs to be concatenated to the SQL string. Did you try concatenating the DLOOKUP() function to the SQL string (see my first post)? My ideas on your problem..... I'll back to sleep now. -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
Thread Tools | |
Display Modes | |
|
|