Can't figure how to do a query

[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

 

[Douglas J Steele]

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

 

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

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.

 

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

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?