Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query question

Discussion in 'Information Technology' started by wal50, Jul 28, 2006.

Tags:
  1. wal50

    wal50
    Expand Collapse
    Guest

    I thought I posted this already but I can't find it. I apologize if I did.
    Anyway, the situation is that I want to select based upon the county field
    in table 1; table 2 has a field which contains counties but (unfortunately)
    could contain multiple counties in the same field. I want to select based
    upon the county in table 1 if there is a match anywhere in the table 2 string.
    Thanks in advance and I again apologize if this is a multiple post.
    WAL50
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Sprinks

    Sprinks
    Expand Collapse
    Guest

    The difficulty you're experiencing is one reason to design your application
    with normalized tables. Putting more than a single value in a field violates
    normalization rules. You can, however, use the InStr function to find a
    string within a string. See VBA Help for more information:

    = Instr([YourFieldWithMultipleValues], [TheOtherField]) returns the position
    of the found match. You could create a boolean for finding a match,
    regardless of the position, with:

    = IIf(Nz(Instr([YourFieldWithMultipleValues], [TheOtherField])) = 0, False,
    True)

    Hope that helps.
    Sprinks

    For further information on database normalization, see the following:

    http://www.mvps.org/access/tencommandments.htm

    Glossary of database terms:
    http://www.dhdursoassociates.com/database-glossary-3.html

    Where to find information about designing a database in Microsoft Access:
    http://support.microsoft.com/?id=289533

    ACC: Database Normalization Basics
    http://support.microsoft.com/?id=100139
    http://support.microsoft.com/?id=209534
    http://support.microsoft.com/?id=283878

    Database Normalization Tips by Luke Chung
    http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

    Support WebCast: Database Normalization Basics
    http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

    Database Normalization:
    http://burks.bton.ac.uk/burks/foldoc/35/28.htm

    5 Rules of Database Normalization:
    http://www.datamodel.org/NormalizationRules.html

    "Understanding Relational Database Design" Document Available in Download
    Center:
    http://support.microsoft.com/?id=283698
    http://support.microsoft.com/?id=164172

    ACC2000: "Understanding Relational Database Design"
    http://support.microsoft.com/?id=234208

    Fundamentals of Relational Database Design:
    http://support.microsoft.com/?id=129519

    Database Deisgn Principles:
    http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp


    "wal50" wrote:

    > I thought I posted this already but I can't find it. I apologize if I did.
    > Anyway, the situation is that I want to select based upon the county field
    > in table 1; table 2 has a field which contains counties but (unfortunately)
    > could contain multiple counties in the same field. I want to select based
    > upon the county in table 1 if there is a match anywhere in the table 2 string.
    > Thanks in advance and I again apologize if this is a multiple post.
    > WAL50
     
  4. wal50

    wal50
    Expand Collapse
    Guest

    Thanks.
    PS. I inherited the file. I don't like it but I got it and am trying to get
    it changed.
    wal50

    "Sprinks" wrote:

    > The difficulty you're experiencing is one reason to design your application
    > with normalized tables. Putting more than a single value in a field violates
    > normalization rules. You can, however, use the InStr function to find a
    > string within a string. See VBA Help for more information:
    >
    > = Instr([YourFieldWithMultipleValues], [TheOtherField]) returns the position
    > of the found match. You could create a boolean for finding a match,
    > regardless of the position, with:
    >
    > = IIf(Nz(Instr([YourFieldWithMultipleValues], [TheOtherField])) = 0, False,
    > True)
    >
    > Hope that helps.
    > Sprinks
    >
    > For further information on database normalization, see the following:
    >
    > http://www.mvps.org/access/tencommandments.htm
    >
    > Glossary of database terms:
    > http://www.dhdursoassociates.com/database-glossary-3.html
    >
    > Where to find information about designing a database in Microsoft Access:
    > http://support.microsoft.com/?id=289533
    >
    > ACC: Database Normalization Basics
    > http://support.microsoft.com/?id=100139
    > http://support.microsoft.com/?id=209534
    > http://support.microsoft.com/?id=283878
    >
    > Database Normalization Tips by Luke Chung
    > http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
    >
    > Support WebCast: Database Normalization Basics
    > http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp
    >
    > Database Normalization:
    > http://burks.bton.ac.uk/burks/foldoc/35/28.htm
    >
    > 5 Rules of Database Normalization:
    > http://www.datamodel.org/NormalizationRules.html
    >
    > "Understanding Relational Database Design" Document Available in Download
    > Center:
    > http://support.microsoft.com/?id=283698
    > http://support.microsoft.com/?id=164172
    >
    > ACC2000: "Understanding Relational Database Design"
    > http://support.microsoft.com/?id=234208
    >
    > Fundamentals of Relational Database Design:
    > http://support.microsoft.com/?id=129519
    >
    > Database Deisgn Principles:
    > http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp
    >
    >
    > "wal50" wrote:
    >
    > > I thought I posted this already but I can't find it. I apologize if I did.
    > > Anyway, the situation is that I want to select based upon the county field
    > > in table 1; table 2 has a field which contains counties but (unfortunately)
    > > could contain multiple counties in the same field. I want to select based
    > > upon the county in table 1 if there is a match anywhere in the table 2 string.
    > > Thanks in advance and I again apologize if this is a multiple post.
    > > WAL50
     
  5. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Wal50,

    Be sure to show the decision-maker the Commandment #1 of the first provided
    link.

    Sprinks

    "wal50" wrote:

    > Thanks.
    > PS. I inherited the file. I don't like it but I got it and am trying to get
    > it changed.
    > wal50
    >
    > "Sprinks" wrote:
    >
    > > The difficulty you're experiencing is one reason to design your application
    > > with normalized tables. Putting more than a single value in a field violates
    > > normalization rules. You can, however, use the InStr function to find a
    > > string within a string. See VBA Help for more information:
    > >
    > > = Instr([YourFieldWithMultipleValues], [TheOtherField]) returns the position
    > > of the found match. You could create a boolean for finding a match,
    > > regardless of the position, with:
    > >
    > > = IIf(Nz(Instr([YourFieldWithMultipleValues], [TheOtherField])) = 0, False,
    > > True)
    > >
    > > Hope that helps.
    > > Sprinks
    > >
    > > For further information on database normalization, see the following:
    > >
    > > http://www.mvps.org/access/tencommandments.htm
    > >
    > > Glossary of database terms:
    > > http://www.dhdursoassociates.com/database-glossary-3.html
    > >
    > > Where to find information about designing a database in Microsoft Access:
    > > http://support.microsoft.com/?id=289533
    > >
    > > ACC: Database Normalization Basics
    > > http://support.microsoft.com/?id=100139
    > > http://support.microsoft.com/?id=209534
    > > http://support.microsoft.com/?id=283878
    > >
    > > Database Normalization Tips by Luke Chung
    > > http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
    > >
    > > Support WebCast: Database Normalization Basics
    > > http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp
    > >
    > > Database Normalization:
    > > http://burks.bton.ac.uk/burks/foldoc/35/28.htm
    > >
    > > 5 Rules of Database Normalization:
    > > http://www.datamodel.org/NormalizationRules.html
    > >
    > > "Understanding Relational Database Design" Document Available in Download
    > > Center:
    > > http://support.microsoft.com/?id=283698
    > > http://support.microsoft.com/?id=164172
    > >
    > > ACC2000: "Understanding Relational Database Design"
    > > http://support.microsoft.com/?id=234208
    > >
    > > Fundamentals of Relational Database Design:
    > > http://support.microsoft.com/?id=129519
    > >
    > > Database Deisgn Principles:
    > > http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp
    > >
    > >
    > > "wal50" wrote:
    > >
    > > > I thought I posted this already but I can't find it. I apologize if I did.
    > > > Anyway, the situation is that I want to select based upon the county field
    > > > in table 1; table 2 has a field which contains counties but (unfortunately)
    > > > could contain multiple counties in the same field. I want to select based
    > > > upon the county in table 1 if there is a match anywhere in the table 2 string.
    > > > Thanks in advance and I again apologize if this is a multiple post.
    > > > WAL50
     

Share This Page