Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

    > >
    > >
    > >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page