Welcome to SPN

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

Sign Up Now!

Can't figure how to do a query

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

Tags:
  1. Jones Barton

    Jones Barton
    Expand Collapse
    Guest

    Hi everybody,
    I am quite stumped on how to write a query. I have two tables that hold
    securites for different periods I need to combine both into one table but the
    twist is that they can have different securities in each table. A short
    example would be.

    Table 1
    Security 9/30/2005Market_value
    IBM 1000
    Microsoft 2000
    XYZ 3000

    Table 2
    Security 12/31/2005Market_value
    IBM 1500
    Microsoft 2500
    America Fund 3000

    The results I would like would be

    Table 1
    Security 9/30/2005Market_value 12/31/2005Market_value
    IBM 1000 1500
    Microsoft 2000 2500
    XYZ 3000
    America Fund 3000


    The tables show that XYZ was dropped after 9/31/2005 and America Funds was
    brought in. I have struggled with this query for a couple of days. Any help
    would be appreciated.

    Jones
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Sorry to seem blunt, but the problem is that your table isn't properly
    normalized.

    Each company should represent a separate row in a table, not a separate
    column on a common row.

    Jeff Conrad has a good set of resources related to table design at
    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    news:0F33898B-6B68-44E6-A321-D053D6B00F20@microsoft.com...
    > Hi everybody,
    > I am quite stumped on how to write a query. I have two tables that hold
    > securites for different periods I need to combine both into one table but

    the
    > twist is that they can have different securities in each table. A short
    > example would be.
    >
    > Table 1
    > Security 9/30/2005Market_value
    > IBM 1000
    > Microsoft 2000
    > XYZ 3000
    >
    > Table 2
    > Security 12/31/2005Market_value
    > IBM 1500
    > Microsoft 2500
    > America Fund 3000
    >
    > The results I would like would be
    >
    > Table 1
    > Security 9/30/2005Market_value 12/31/2005Market_value
    > IBM 1000 1500
    > Microsoft 2000 2500
    > XYZ 3000
    > America Fund 3000
    >
    >
    > The tables show that XYZ was dropped after 9/31/2005 and America Funds was
    > brought in. I have struggled with this query for a couple of days. Any

    help
    > would be appreciated.
    >
    > Jones
    >
    >
    >
    >
    >
     
  4. Jones Barton

    Jones Barton
    Expand Collapse
    Guest

    Douglas,

    I appreciate your honesty, With that being said, I don't have any control
    on how I get the data, Is there any way to accomplish my hopeful outcome
    with a query?

    Jones

    "Douglas J Steele" wrote:

    > Sorry to seem blunt, but the problem is that your table isn't properly
    > normalized.
    >
    > Each company should represent a separate row in a table, not a separate
    > column on a common row.
    >
    > Jeff Conrad has a good set of resources related to table design at
    > http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    > news:0F33898B-6B68-44E6-A321-D053D6B00F20@microsoft.com...
    > > Hi everybody,
    > > I am quite stumped on how to write a query. I have two tables that hold
    > > securites for different periods I need to combine both into one table but

    > the
    > > twist is that they can have different securities in each table. A short
    > > example would be.
    > >
    > > Table 1
    > > Security 9/30/2005Market_value
    > > IBM 1000
    > > Microsoft 2000
    > > XYZ 3000
    > >
    > > Table 2
    > > Security 12/31/2005Market_value
    > > IBM 1500
    > > Microsoft 2500
    > > America Fund 3000
    > >
    > > The results I would like would be
    > >
    > > Table 1
    > > Security 9/30/2005Market_value 12/31/2005Market_value
    > > IBM 1000 1500
    > > Microsoft 2000 2500
    > > XYZ 3000
    > > America Fund 3000
    > >
    > >
    > > The tables show that XYZ was dropped after 9/31/2005 and America Funds was
    > > brought in. I have struggled with this query for a couple of days. Any

    > help
    > > would be appreciated.
    > >
    > > Jones
    > >
    > >
    > >
    > >
    > >

    >
    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    What are the names of the fields in your table? You might be able to write a
    query that normalizes the data, and then use that query as the basis for
    subsequent queries.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    news:9FBA5373-CCB6-472D-A310-732D8E48D8D4@microsoft.com...
    > Douglas,
    >
    > I appreciate your honesty, With that being said, I don't have any control
    > on how I get the data, Is there any way to accomplish my hopeful outcome
    > with a query?
    >
    > Jones
    >
    > "Douglas J Steele" wrote:
    >
    >> Sorry to seem blunt, but the problem is that your table isn't properly
    >> normalized.
    >>
    >> Each company should represent a separate row in a table, not a separate
    >> column on a common row.
    >>
    >> Jeff Conrad has a good set of resources related to table design at
    >> http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    >> news:0F33898B-6B68-44E6-A321-D053D6B00F20@microsoft.com...
    >> > Hi everybody,
    >> > I am quite stumped on how to write a query. I have two tables that
    >> > hold
    >> > securites for different periods I need to combine both into one table
    >> > but

    >> the
    >> > twist is that they can have different securities in each table. A
    >> > short
    >> > example would be.
    >> >
    >> > Table 1
    >> > Security 9/30/2005Market_value
    >> > IBM 1000
    >> > Microsoft 2000
    >> > XYZ 3000
    >> >
    >> > Table 2
    >> > Security 12/31/2005Market_value
    >> > IBM 1500
    >> > Microsoft 2500
    >> > America Fund 3000
    >> >
    >> > The results I would like would be
    >> >
    >> > Table 1
    >> > Security 9/30/2005Market_value 12/31/2005Market_value
    >> > IBM 1000 1500
    >> > Microsoft 2000 2500
    >> > XYZ 3000
    >> > America Fund 3000
    >> >
    >> >
    >> > The tables show that XYZ was dropped after 9/31/2005 and America Funds
    >> > was
    >> > brought in. I have struggled with this query for a couple of days.
    >> > Any

    >> help
    >> > would be appreciated.
    >> >
    >> > Jones
    >> >
    >> >
    >> >
    >> >
    >> >

    >>
    >>
    >>
     
  6. Jones Barton

    Jones Barton
    Expand Collapse
    Guest

    Hi Douglas

    In each table I have Fields named History_dte (date), Manager_long, Entity,
    Asset_Class, TotalMv
    one table has 12/31/2005 data the other 09/30/2005 data in it.


    Jones

    "Douglas J. Steele" wrote:

    > What are the names of the fields in your table? You might be able to write a
    > query that normalizes the data, and then use that query as the basis for
    > subsequent queries.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    > news:9FBA5373-CCB6-472D-A310-732D8E48D8D4@microsoft.com...
    > > Douglas,
    > >
    > > I appreciate your honesty, With that being said, I don't have any control
    > > on how I get the data, Is there any way to accomplish my hopeful outcome
    > > with a query?
    > >
    > > Jones
    > >
    > > "Douglas J Steele" wrote:
    > >
    > >> Sorry to seem blunt, but the problem is that your table isn't properly
    > >> normalized.
    > >>
    > >> Each company should represent a separate row in a table, not a separate
    > >> column on a common row.
    > >>
    > >> Jeff Conrad has a good set of resources related to table design at
    > >> http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    > >> news:0F33898B-6B68-44E6-A321-D053D6B00F20@microsoft.com...
    > >> > Hi everybody,
    > >> > I am quite stumped on how to write a query. I have two tables that
    > >> > hold
    > >> > securites for different periods I need to combine both into one table
    > >> > but
    > >> the
    > >> > twist is that they can have different securities in each table. A
    > >> > short
    > >> > example would be.
    > >> >
    > >> > Table 1
    > >> > Security 9/30/2005Market_value
    > >> > IBM 1000
    > >> > Microsoft 2000
    > >> > XYZ 3000
    > >> >
    > >> > Table 2
    > >> > Security 12/31/2005Market_value
    > >> > IBM 1500
    > >> > Microsoft 2500
    > >> > America Fund 3000
    > >> >
    > >> > The results I would like would be
    > >> >
    > >> > Table 1
    > >> > Security 9/30/2005Market_value 12/31/2005Market_value
    > >> > IBM 1000 1500
    > >> > Microsoft 2000 2500
    > >> > XYZ 3000
    > >> > America Fund 3000
    > >> >
    > >> >
    > >> > The tables show that XYZ was dropped after 9/31/2005 and America Funds
    > >> > was
    > >> > brought in. I have struggled with this query for a couple of days.
    > >> > Any
    > >> help
    > >> > would be appreciated.
    > >> >
    > >> > Jones
    > >> >
    > >> >
    > >> >
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Sorry: I don't see how that table definition relates to the sample data
    you've given!

    How about showing a few sample rows from each table?

    Are you saying the data is coming to you from an external source, and that's
    why there are two separate tables?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    news:836414E3-5EB9-439C-9499-870DF089B12D@microsoft.com...
    > Hi Douglas
    >
    > In each table I have Fields named History_dte (date), Manager_long,

    Entity,
    > Asset_Class, TotalMv
    > one table has 12/31/2005 data the other 09/30/2005 data in it.
    >
    >
    > Jones
    >
    > "Douglas J. Steele" wrote:
    >
    > > What are the names of the fields in your table? You might be able to

    write a
    > > query that normalizes the data, and then use that query as the basis for
    > > subsequent queries.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in message
    > > news:9FBA5373-CCB6-472D-A310-732D8E48D8D4@microsoft.com...
    > > > Douglas,
    > > >
    > > > I appreciate your honesty, With that being said, I don't have any

    control
    > > > on how I get the data, Is there any way to accomplish my hopeful

    outcome
    > > > with a query?
    > > >
    > > > Jones
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > >> Sorry to seem blunt, but the problem is that your table isn't

    properly
    > > >> normalized.
    > > >>
    > > >> Each company should represent a separate row in a table, not a

    separate
    > > >> column on a common row.
    > > >>
    > > >> Jeff Conrad has a good set of resources related to table design at
    > > >>

    http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
    > > >>
    > > >> --
    > > >> Doug Steele, Microsoft Access MVP
    > > >> http://I.Am/DougSteele
    > > >> (no e-mails, please!)
    > > >>
    > > >>
    > > >> "Jones Barton" <JonesBarton@discussions.microsoft.com> wrote in

    message
    > > >> news:0F33898B-6B68-44E6-A321-D053D6B00F20@microsoft.com...
    > > >> > Hi everybody,
    > > >> > I am quite stumped on how to write a query. I have two tables that
    > > >> > hold
    > > >> > securites for different periods I need to combine both into one

    table
    > > >> > but
    > > >> the
    > > >> > twist is that they can have different securities in each table. A
    > > >> > short
    > > >> > example would be.
    > > >> >
    > > >> > Table 1
    > > >> > Security 9/30/2005Market_value
    > > >> > IBM 1000
    > > >> > Microsoft 2000
    > > >> > XYZ 3000
    > > >> >
    > > >> > Table 2
    > > >> > Security 12/31/2005Market_value
    > > >> > IBM 1500
    > > >> > Microsoft 2500
    > > >> > America Fund 3000
    > > >> >
    > > >> > The results I would like would be
    > > >> >
    > > >> > Table 1
    > > >> > Security 9/30/2005Market_value

    12/31/2005Market_value
    > > >> > IBM 1000 1500
    > > >> > Microsoft 2000 2500
    > > >> > XYZ 3000
    > > >> > America Fund 3000
    > > >> >
    > > >> >
    > > >> > The tables show that XYZ was dropped after 9/31/2005 and America

    Funds
    > > >> > was
    > > >> > brought in. I have struggled with this query for a couple of days.
    > > >> > Any
    > > >> help
    > > >> > would be appreciated.
    > > >> >
    > > >> > Jones
    > > >> >
    > > >> >
    > > >> >
    > > >> >
    > > >> >
    > > >>
    > > >>
    > > >>

    > >
    > >
    > >
     

Share This Page