If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 identical table designs
I have 2 tables with the same numbers of fields and same name
Table 1 is the original data and Table 2 is the adjustments data, which needs to be kept seperate I am having trouble combining data from both tables. In some instances there is data in table 2 but not in table one eg account number may be in t2 not not in t1 I have considered using a junction table but this does not work Other than the ID in both tables there are no other primary keys Fields are ID Company Code (duplicate YES) Account (duplictaes YES) Value 1 value 2 Text Can some help and suggest ways of improving or meths that I can use to return data say based on both Company code and account numbers. Regards Danny |
#2
|
|||
|
|||
In this case I would try to first create a union query (this should be done
as a SQL query which you can create by starting a new query not selecting a table then go to SQL view) then use a similar statment to below. SELECT [A].* FROM [table a] as A UNION ALL SELECT [b].* FROM [table b] as B; This should essentially combine the two tables into one query. Then you can filter out what you need. Good luck, Krizhek "Danny" wrote: I have 2 tables with the same numbers of fields and same name Table 1 is the original data and Table 2 is the adjustments data, which needs to be kept seperate I am having trouble combining data from both tables. In some instances there is data in table 2 but not in table one eg account number may be in t2 not not in t1 I have considered using a junction table but this does not work Other than the ID in both tables there are no other primary keys Fields are ID Company Code (duplicate YES) Account (duplictaes YES) Value 1 value 2 Text Can some help and suggest ways of improving or meths that I can use to return data say based on both Company code and account numbers. Regards Danny |
#3
|
|||
|
|||
On Wed, 29 Jun 2005 11:32:04 -0700, "Danny"
wrote: I have 2 tables with the same numbers of fields and same name Table 1 is the original data and Table 2 is the adjustments data, which needs to be kept seperate I am having trouble combining data from both tables. In some instances there is data in table 2 but not in table one eg account number may be in t2 not not in t1 I have considered using a junction table but this does not work Other than the ID in both tables there are no other primary keys Fields are ID Company Code (duplicate YES) Account (duplictaes YES) Value 1 value 2 Text Can some help and suggest ways of improving or meths that I can use to return data say based on both Company code and account numbers. Sounds like you need a UNION query: SELECT ID, [Company Code], [Account], [Value 1], [Value 2], [Text] FROM T1 UNION ALL SELECT ID, [Company Code], [Account], [Value 1], [Value 2], [Text] FROM T2 ORDER BY whatever makes sense for your needs John W. Vinson[MVP] |
#4
|
|||
|
|||
Why must it be kept separate? Is it kept in a different physical location,
if it is you can ignore the following. Assuming the reason for the different physical location is logical or down to some "policy" decision. Keep it all in the same table, add a field to indicate whether the record is an "original" or an "adjustment". If there is a natural primary key (say an index based on both Company Code and Account) then use it instead of the "id stuff". If the combination of Company Code and Account are currently unique in both the current tables then the addition of the "original"/"adjustment" marker to the primary key will create the necessary primary key. With this construct you can now query on just one table and use the marker to differentiate from "original" and "adjustment" data. BTW If there is no natural key you could stick with the "id stuff" and just use the marker as mentioned above. -- Slainte Craig Alexander Morrison "Danny" wrote in message ... I have 2 tables with the same numbers of fields and same name Table 1 is the original data and Table 2 is the adjustments data, which needs to be kept seperate I am having trouble combining data from both tables. In some instances there is data in table 2 but not in table one eg account number may be in t2 not not in t1 I have considered using a junction table but this does not work Other than the ID in both tables there are no other primary keys Fields are ID Company Code (duplicate YES) Account (duplictaes YES) Value 1 value 2 Text Can some help and suggest ways of improving or meths that I can use to return data say based on both Company code and account numbers. Regards Danny |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Tables Relationship Question | Greg Jesky | Database Design | 6 | April 8th, 2005 06:44 PM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |