Welcome to SPN

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

Sign Up Now!

Open Query If Condition is Met

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

  1. Todd

    Todd
    Expand Collapse
    Guest

    I have a database with 3 tables. Each table has an account number field
    (account number are 16 digits and in a TEXT format) however the tables are
    completely different as are the account numbers that populate them. To go
    with the tables are queries that will bring in desired information. What i
    would like is for the user to input an account number on a form and have some
    code in the background that will find the account number in the table it is
    in and open the corresponding query. Is this possible? Thanks for the help. I
    have been trying to write code with DLookup but can't seem to get it to do
    what I want.
     
  2. Loading...


  3. Lynn Trapp

    Lynn Trapp
    Expand Collapse
    Guest

    Todd,
    Why do you have the same kind of information in 3 different tables?

    --
    Lynn Trapp
    MS Access MVP
    www.ltcomputerdesigns.com
    Access Security: www.ltcomputerdesigns.com/Security.htm
    Jeff Conrad's Access Junkie List:
    http://home.bendbroadband.com/conradsystems/accessjunkie.html



    "Todd" <Todd@discussions.microsoft.com> wrote in message
    news:20C02530-C0CB-4ED9-A7AF-63DE319449DB@microsoft.com...
    >I have a database with 3 tables. Each table has an account number field
    > (account number are 16 digits and in a TEXT format) however the tables are
    > completely different as are the account numbers that populate them. To go
    > with the tables are queries that will bring in desired information. What i
    > would like is for the user to input an account number on a form and have
    > some
    > code in the background that will find the account number in the table it
    > is
    > in and open the corresponding query. Is this possible? Thanks for the
    > help. I
    > have been trying to write code with DLookup but can't seem to get it to do
    > what I want.
    >
     
  4. Todd

    Todd
    Expand Collapse
    Guest

    It is not the same information. The tables all have different information
    they just have a field called account number, but each table will have
    different acct numbers

    "Lynn Trapp" wrote:

    > Todd,
    > Why do you have the same kind of information in 3 different tables?
    >
    > --
    > Lynn Trapp
    > MS Access MVP
    > www.ltcomputerdesigns.com
    > Access Security: www.ltcomputerdesigns.com/Security.htm
    > Jeff Conrad's Access Junkie List:
    > http://home.bendbroadband.com/conradsystems/accessjunkie.html
    >
    >
    >
    > "Todd" <Todd@discussions.microsoft.com> wrote in message
    > news:20C02530-C0CB-4ED9-A7AF-63DE319449DB@microsoft.com...
    > >I have a database with 3 tables. Each table has an account number field
    > > (account number are 16 digits and in a TEXT format) however the tables are
    > > completely different as are the account numbers that populate them. To go
    > > with the tables are queries that will bring in desired information. What i
    > > would like is for the user to input an account number on a form and have
    > > some
    > > code in the background that will find the account number in the table it
    > > is
    > > in and open the corresponding query. Is this possible? Thanks for the
    > > help. I
    > > have been trying to write code with DLookup but can't seem to get it to do
    > > what I want.
    > >

    >
    >
    >
     
  5. Lynn Trapp

    Lynn Trapp
    Expand Collapse
    Guest

    Can you post the structure of the tables? It seems to me there is probably a
    way to combine the tables (perhaps even with the use of a Union query) and,
    then, only have to have one query.

    --
    Lynn Trapp
    MS Access MVP
    www.ltcomputerdesigns.com
    Access Security: www.ltcomputerdesigns.com/Security.htm
    Jeff Conrad's Access Junkie List:
    http://home.bendbroadband.com/conradsystems/accessjunkie.html



    "Todd" <Todd@discussions.microsoft.com> wrote in message
    news:DF9458B9-0439-4CF5-9DEF-DCD5640F1C10@microsoft.com...
    > It is not the same information. The tables all have different information
    > they just have a field called account number, but each table will have
    > different acct numbers
    >
    > "Lynn Trapp" wrote:
    >
    >> Todd,
    >> Why do you have the same kind of information in 3 different tables?
    >>
    >> --
    >> Lynn Trapp
    >> MS Access MVP
    >> www.ltcomputerdesigns.com
    >> Access Security: www.ltcomputerdesigns.com/Security.htm
    >> Jeff Conrad's Access Junkie List:
    >> http://home.bendbroadband.com/conradsystems/accessjunkie.html
    >>
    >>
    >>
    >> "Todd" <Todd@discussions.microsoft.com> wrote in message
    >> news:20C02530-C0CB-4ED9-A7AF-63DE319449DB@microsoft.com...
    >> >I have a database with 3 tables. Each table has an account number field
    >> > (account number are 16 digits and in a TEXT format) however the tables
    >> > are
    >> > completely different as are the account numbers that populate them. To
    >> > go
    >> > with the tables are queries that will bring in desired information.
    >> > What i
    >> > would like is for the user to input an account number on a form and
    >> > have
    >> > some
    >> > code in the background that will find the account number in the table
    >> > it
    >> > is
    >> > in and open the corresponding query. Is this possible? Thanks for the
    >> > help. I
    >> > have been trying to write code with DLookup but can't seem to get it to
    >> > do
    >> > what I want.
    >> >

    >>
    >>
    >>
     
  6. Todd

    Todd
    Expand Collapse
    Guest

    It is probably going to have to be more than one because I am going to need
    reports to generate from the queries which are going to be nothing alike.

    For example:
    Tbl1 will have ACCTNUM, NAME, DATE, REASON FOR DISPUTE
    Tbl2 will have ACCTNUM, NAME, DATE, REASON FOR DISPUTE, DATE OF RETURN,
    RETURN REASON
    Tbl3 will have ACCT NUM, NAME, DATE, REASON FOR CANCELLATION, DATE OF
    CANCELLATION

    This is a sample. Some tables may have 3 or 4 fields and some may have 10
    or 12 fields depending on the dispute type.

    "Lynn Trapp" wrote:

    > Can you post the structure of the tables? It seems to me there is probably a
    > way to combine the tables (perhaps even with the use of a Union query) and,
    > then, only have to have one query.
    >
    > --
    > Lynn Trapp
    > MS Access MVP
    > www.ltcomputerdesigns.com
    > Access Security: www.ltcomputerdesigns.com/Security.htm
    > Jeff Conrad's Access Junkie List:
    > http://home.bendbroadband.com/conradsystems/accessjunkie.html
    >
    >
    >
    > "Todd" <Todd@discussions.microsoft.com> wrote in message
    > news:DF9458B9-0439-4CF5-9DEF-DCD5640F1C10@microsoft.com...
    > > It is not the same information. The tables all have different information
    > > they just have a field called account number, but each table will have
    > > different acct numbers
    > >
    > > "Lynn Trapp" wrote:
    > >
    > >> Todd,
    > >> Why do you have the same kind of information in 3 different tables?
    > >>
    > >> --
    > >> Lynn Trapp
    > >> MS Access MVP
    > >> www.ltcomputerdesigns.com
    > >> Access Security: www.ltcomputerdesigns.com/Security.htm
    > >> Jeff Conrad's Access Junkie List:
    > >> http://home.bendbroadband.com/conradsystems/accessjunkie.html
    > >>
    > >>
    > >>
    > >> "Todd" <Todd@discussions.microsoft.com> wrote in message
    > >> news:20C02530-C0CB-4ED9-A7AF-63DE319449DB@microsoft.com...
    > >> >I have a database with 3 tables. Each table has an account number field
    > >> > (account number are 16 digits and in a TEXT format) however the tables
    > >> > are
    > >> > completely different as are the account numbers that populate them. To
    > >> > go
    > >> > with the tables are queries that will bring in desired information.
    > >> > What i
    > >> > would like is for the user to input an account number on a form and
    > >> > have
    > >> > some
    > >> > code in the background that will find the account number in the table
    > >> > it
    > >> > is
    > >> > in and open the corresponding query. Is this possible? Thanks for the
    > >> > help. I
    > >> > have been trying to write code with DLookup but can't seem to get it to
    > >> > do
    > >> > what I want.
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 15 Jun 2006 12:57:01 -0700, Todd
    <Todd@discussions.microsoft.com> wrote:

    >It is probably going to have to be more than one because I am going to need
    >reports to generate from the queries which are going to be nothing alike.
    >
    >For example:
    >Tbl1 will have ACCTNUM, NAME, DATE, REASON FOR DISPUTE
    >Tbl2 will have ACCTNUM, NAME, DATE, REASON FOR DISPUTE, DATE OF RETURN,
    >RETURN REASON
    >Tbl3 will have ACCT NUM, NAME, DATE, REASON FOR CANCELLATION, DATE OF
    >CANCELLATION
    >
    >This is a sample. Some tables may have 3 or 4 fields and some may have 10
    >or 12 fields depending on the dispute type.


    This might be a good case for Subclassing, one of the few instances
    where One to One relationships are commonly used.

    If you had a table of Disputes, primary key ACCTNUM, with a second
    field DisputeType, you could relate it one-to-one to each of these
    tables. Your account number search could then home in on just the
    desired record; you could base your (say) Cancellation report on a
    query joining Disputes to Tbl3.


    John W. Vinson[MVP]
     
  8. Todd

    Todd
    Expand Collapse
    Guest

    I am a little confused. I would have to join about 10 tables and all the
    fields in a single query. The query would have around 100 fields and I would
    not want to see but the ones for the specific table the account number is in.
    Is there an example of the "subclassing" somewhere?

    "John Vinson" wrote:

    > On Thu, 15 Jun 2006 12:57:01 -0700, Todd
    > <Todd@discussions.microsoft.com> wrote:
    >
    > >It is probably going to have to be more than one because I am going to need
    > >reports to generate from the queries which are going to be nothing alike.
    > >
    > >For example:
    > >Tbl1 will have ACCTNUM, NAME, DATE, REASON FOR DISPUTE
    > >Tbl2 will have ACCTNUM, NAME, DATE, REASON FOR DISPUTE, DATE OF RETURN,
    > >RETURN REASON
    > >Tbl3 will have ACCT NUM, NAME, DATE, REASON FOR CANCELLATION, DATE OF
    > >CANCELLATION
    > >
    > >This is a sample. Some tables may have 3 or 4 fields and some may have 10
    > >or 12 fields depending on the dispute type.

    >
    > This might be a good case for Subclassing, one of the few instances
    > where One to One relationships are commonly used.
    >
    > If you had a table of Disputes, primary key ACCTNUM, with a second
    > field DisputeType, you could relate it one-to-one to each of these
    > tables. Your account number search could then home in on just the
    > desired record; you could base your (say) Cancellation report on a
    > query joining Disputes to Tbl3.
    >
    >
    > John W. Vinson[MVP]
    >
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 15 Jun 2006 13:59:01 -0700, Todd
    <Todd@discussions.microsoft.com> wrote:

    >I am a little confused. I would have to join about 10 tables and all the
    >fields in a single query. The query would have around 100 fields and I would
    >not want to see but the ones for the specific table the account number is in.
    > Is there an example of the "subclassing" somewhere?


    I was thinking more that you would have ten queries, one for each
    report. They'd all use the same master table but each would use its
    own related table for the fields specific for that report.

    You would have a separate query on the master table to identify which
    type of issue was involved; this could use some VBA code to select the
    issue type, and open the appropriate report.

    John W. Vinson[MVP]
     

Share This Page