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
> > >
> >
>
