Re: [MS_AccessPros] Slow combo box find as you type

 

Connie

Save yourself some work. In the case of #1 or 2, make one query with all the necessary fields as long as it runs quickly. You can always pick and choose the fields on a per-combo basis.

Bill

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> Bill--so I'm in revamp mode. Ack!!! I'm redoing combo boxes and some reports. I'm very thankful for the information though! There are many comboboxes that need several columns showing to make sure that the correct one is selected and gives the varying info the user needs(Address, Property Name, Date Listed for example). Each combo box has a need for different columns. Address will generally be Address,City. Thus I have the following question:
>
> 1. If the info comes from one table is it okay to have one query with all the info that will be required for several combos? Or will it be faster to have a specific query with only the number of fields needed.
>
> 2. Same question except for information coming from two tables.
>
> Thanks!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> >
> > Connie
> >
> > > That brings up another question. The city field in the location table uses an ID which refers to a City table. Is that bad too?
> >
> >
> > That's what I'm trying to say. Don't use a ID. Use the city. State IDs (2-char postal abbreviations) are just fine. Like CA for California. Then put the state name in a state table in case you ever need to show it in a report or something.
> >
> > I bet if you make these changes your combo box will speed up immensely.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> > >
> > > Bill,
> > >
> > > That brings up another question. The city field in the location table uses an ID which refers to a City table. Is that bad too?
> > >
> > > The toll on the queries is a huge issue for us. Perhaps this is one of the causes of the too many databases issue?
> > >
> > > Thanks again!
> > > Connie
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > >
> > > > Connie
> > > >
> > > > When I said, "breaking it into separate FIELDS defies logic ..." I meant putting street names in a separate table.
> > > >
> > > > As I said, a combination of street address (123 Main st.),city, state or Providence and postal code is going to be unique. The standard setup would be those 4 fields in the location table.
> > > >
> > > > Breaking street names out into a separate table and using an ID for it does not help you in any way other than possibly preventing typos. The cost is a large overhead for queries and tons of maintenance keeping the street names current. I can see a street table would be easier to update an address should the street name change, but a simple update query could accomplish that.
> > > >
> > > > Bill
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> > > > >
> > > > > Bill,
> > > > >
> > > > > Thanks for not judging :-)--I definitely want input.
> > > > >
> > > > > There are times where we need to change Direction1 to the end of the address and times when it needs to be at the front. But looking back and seeing the results on the database, I'd make a different decision and figure out how to work with that rare event where it needs to be changed for a specific reason.
> > > > >
> > > > > By the way all the elements of the address are in the same table. It's just that they were all separated and the street is a number which refers to a table with a list of streets. Did you mean to say "breaking it into separate FIELDS defies logic ..."? I certainly don't want to make the same mistakes in a future database so I'm trying to understand.
> > > > >
> > > > > Thanks again!
> > > > > Connie
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > > > > >
> > > > > > Connie
> > > > > >
> > > > > > No judging allowed here. You're safe to voice any and all thoughts about databases.
> > > > > >
> > > > > > I would not have a combined Street-City. It's too simple to concatenate the fields in whatever forms/reports you need. I do that all the time with First and Last names. In fact, I usually have a standard view (query) of fields I use all the time. It combines items like First and Last, Doctor, nurse, social worker, site, etc.
> > > > > >
> > > > > > Then I use that as a table in a bazillion places.
> > > > > >
> > > > > > But still, you are over-thinking your address data. 123 Main Street in Mytown, Mystate, Myzip is going to have only one location. Breaking it all out into separate tables defies logic and hurts performance. A search on 500 records should take a fraction of a second.
> > > > > >
> > > > > > Regards,
> > > > > > Bill Mosca, Founder - MS_Access_Professionals
> > > > > > http://www.thatlldoit.com
> > > > > > Microsoft Office Access MVP
> > > > > > https://mvp.support.microsoft.com/profile/Bill.Mosca
> > > > > >
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> > > > > > >
> > > > > > > John and Bill,
> > > > > > >
> > > > > > > I'm wondering if I should commit the ultimate No no...Would it work to create a field in the HomeInfo table (or a separate table) that was the combined street or streetCity and index that? And anytime the data is updated force an update of that field? As you would guess that info is used in MANY places.
> > > > > > >
> > > > > > > Hoping you are not going to excommunicate me :-)
> > > > > > > Connie
> > > > > > >
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > > > > > >
> > > > > > > > Connie-
> > > > > > > >
> > > > > > > > Because you're using an expression to generate the display field in the combo
> > > > > > > > box, an index won't help at all. You could make the query slightly more
> > > > > > > > efficient by putting an index on Nbr, Direction, Street, Direction2, and City
> > > > > > > > and then do an ORDER BY on those fields rather than the expression.
> > > > > > > >
> > > > > > > > John Viescas, author
> > > > > > > > Microsoft Office Access 2010 Inside Out
> > > > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > > > Building Microsoft Access Applications
> > > > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > > > SQL Queries for Mere Mortals
> > > > > > > > http://www.viescas.com/
> > > > > > > > (Paris, France)
> > > > > > > >
> > > > > > > > --------------------------------
> > > > > > > >
> > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > > > > > > Sent: Monday, April 30, 2012 9:36 PM
> > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > Subject: Re: [MS_AccessPros] Slow combo box find as you type
> > > > > > > >
> > > > > > > >  
> > > > > > > > John,
> > > > > > > >
> > > > > > > > There are currently 502 rows in the HomeInfo table. If I create an index based
> > > > > > > > upon 5 fields, will the query use it automatically?
> > > > > > > >
> > > > > > > > Oh--here's the query SQL for qAddressCity:
> > > > > > > > SELECT HomeInfo.HomeInfoID, IIf(IsNull([HomeInfo].[Nbr]),"",[HomeInfo].[Nbr] & "
> > > > > > > > ") & ([HomeInfo].[Direction1]+" ") & ([Street_List].[Street]+" ") &
> > > > > > > > ([HomeInfo].[Direction2]+" ") & [City_List].[City] AS AddressCity,
> > > > > > > > HomeInfo.TownshipID, HomeInfo.CityID, HomeInfo.ParcelNbr
> > > > > > > > FROM Street_List RIGHT JOIN (HomeInfo LEFT JOIN City_List ON HomeInfo.CityID =
> > > > > > > > City_List.CityID) ON Street_List.StreetID = HomeInfo.StreetID
> > > > > > > > ORDER BY IIf(IsNull([HomeInfo].[Nbr]),"",[HomeInfo].[Nbr] & " ") &
> > > > > > > > ([HomeInfo].[Direction1]+" ") & ([Street_List].[Street]+" ") &
> > > > > > > > ([HomeInfo].[Direction2]+" ") & [City_List].[City];
> > > > > > > >
> > > > > > > > Connie
> > > > > > > >
> > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > > > > > > > >
> > > > > > > > > Connie-
> > > > > > > > >
> > > > > > > > > How many rows in the underlying table? Do you have an index defined on
> > > > > > > > > AddressCity? That would help.
> > > > > > > > >
> > > > > > > > > John Viescas, author
> > > > > > > > > Microsoft Office Access 2010 Inside Out
> > > > > > > > > Microsoft Office Access 2007 Inside Out
> > > > > > > > > Building Microsoft Access Applications
> > > > > > > > > Microsoft Office Access 2003 Inside Out
> > > > > > > > > SQL Queries for Mere Mortals
> > > > > > > > > http://www.viescas.com/
> > > > > > > > > (Paris, France)
> > > > > > > > >
> > > > > > > > > ---------------------------
> > > > > > > > >
> > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > > > > > > > Sent: Monday, April 30, 2012 8:18 PM
> > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > > > > Subject: [MS_AccessPros] Slow combo box find as you type
> > > > > > > > >
> > > > > > > > >  
> > > > > > > > > Hi all!
> > > > > > > > >
> > > > > > > > > I have a form Listings where the first thing to be entered is the address via
> > > > > > > > a
> > > > > > > > > combobox whose row source is a query that draws data from the HomeInfo table.
> > > > > > > > My
> > > > > > > > > problem is that entry is increasingly slower. I type in 2 or 3 characters and
> > > > > > > > > then have to wait before I can type any other characters. An example would be
> > > > > > > > > typing in 55738 210th St Alberta and after 55738 it stops. Access says it is
> > > > > > > > > calculating, then says it's doing a query. Have to wait several seconds before
> > > > > > > > I
> > > > > > > > > can continue entering. I have tried entering the above several times and
> > > > > > > > > sometimes there's no problem and other times there is.
> > > > > > > > >
> > > > > > > > > ControlSource: HomeInfoID
> > > > > > > > >
> > > > > > > > > RowSource: SELECT qAddressCity.HomeInfoID, qAddressCity.AddressCity,
> > > > > > > > > qAddressCity.ParcelNbr FROM qAddressCity ORDER BY qAddressCity.AddressCity;
> > > > > > > > >
> > > > > > > > > On Not In List Event:
> > > > > > > > >
> > > > > > > > > 'If this property has not been entered yet, open the form for entering. (Not
> > > > > > > > > using properties feature for this because I can not click on the close button
> > > > > > > > on
> > > > > > > > > the form.)
> > > > > > > > > Dim msg, style, title
> > > > > > > > > msg = "This property is not in the database. Would you like to add it?"
> > > > > > > > > style = vbYesNo
> > > > > > > > > title = "Add Property"
> > > > > > > > > Response = Msgbox(msg, style, title)
> > > > > > > > > If Response = vbYes Then
> > > > > > > > > DoCmd.OpenForm "HomeInfo", , , , acFormAdd
> > > > > > > > > End If
> > > > > > > > >
> > > > > > > > > I also have a BeforeUpdate and AfterUpdate event, but those are not being
> > > > > > > > > triggered since I'm just typing and not tabbing or pressing Enter.
> > > > > > > > >
> > > > > > > > > HomeInfo table fields:
> > > > > > > > > HomeInfoID
> > > > > > > > > Nbr
> > > > > > > > > Direction1
> > > > > > > > > StreetID
> > > > > > > > > Direction2
> > > > > > > > > CityID
> > > > > > > > > TownshipID
> > > > > > > > > CountyID
> > > > > > > > > Notes
> > > > > > > > >
> > > > > > > > > Thanks!
> > > > > > > > > Connie
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (15)

Recent Activity:

• New Members 1
• New Links 1
• New Files 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___