append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to join two tables in a different way..
both the tables have got 3 columns in common but there are 5 additional
columns in second table
what i need is to append the rows with the common columns to the first table
and add the additional columns from table 2 to table 1..what is the
appropriate sql syntax for that???


thanks
 
Why are you storing duplicate information in two tables? And, why do you
want to add further redundancy by copying more data from one table to
another? Is it your plan to append all of this data, then delete one of the
tables?

If not, you are breaking database normalization rules and failing to take
advantage of the power a relational database offers.

In a relational database you store each piece of information once, and only
once. Then, you relate the two tables to each other.

If you will explain what the fields are in each table, perhaps we can help
you "normalize" your data.
 
hi rick.
thanks for your reply. well my problrb is i have two tables.. one has got
2000 data and other has 2001
no. of columns are same in both but the values r different...for ex in
2000,i have got volumes for 12 different months for the year 2000 and in the
othe table for 2001..how do i design the query so that volume columns from
2001 get added to 2000 table across and the rest of the rows get added down
the 2000 table..

pls help
 
Sounds like you made two spreadsheet, one for each year. Access is a
relational database, not a spreadsheet. If your field names contain data
(like "May" or "June") then you have probably not normalized your design.
If your table names contain data (like "2001" or "2000") then you have
definitely not normalized your design.

You would have to step back and create a normalized relational database if
you hope to get information out of it using Access queries and reports.

Your table should be something more like...

TblMonthlyBalance
AccountNumber?
AccountDate
AccountBalance

I'm not sure what that first field would be. If you are recording a balance
in a bunch of G/L accounts, for example, then the above table would do it.


You can then create reports and use criteria to pull out 2001 data and 2002
data. I believe that crosstab queries would let you see items using months
across the top.


The only real answer here is that you tried to use Access as a spreadsheet
and (as you have found) that does not work very well.

Hope that gets you on the right track.
 
hi rick
i ll show u what my data is like..

table1 year2000
company service_id vol_jan vol_feb.... vol_dec
xza 12 23 76 87
88......................................


table 2 year 2001
company service_id vol_jan vol_feb.... vol_dec

vvv 77 66 44 3.8
..................................................

i need to append these two tables..how do i achieve this??
 
As previously stated, if your field names contain data (vol_jan) then you do
not have a normalized database design.

If your table names contain data (2000, 2001, etc.) then you really have a
poorly designed data structure.

Build a database, not a spreadsheet and you will be able to get data like
you want in ACCESS. If you want to continue your current structure, do it
in Excel.
 
Hi Rick

i think i wasnt able to explain my table properly to u..the vol_Jan dfield
means its the volume for jan ie it contains numbers only and not any dates...
now suggest me how should i append two tables with volumes for different
years???
 
Hi Rick

i think i wasnt able to explain my table properly to u..the vol_Jan dfield
means its the volume for jan ie it contains numbers only and not any dates...
now suggest me how should i append two tables with volumes for different
years???
Click to expand...

You're missing Rick's point.

Yes, your field vol_Jan contains numbers.

But the NAME of the field indicates that the number in this field
pertains to January. The name of the Table evidently contains the
information that it's January 2005.

What you are doing here is storing DATA - years and months - in
tablenames and fieldnames.

Doing so is *bad design* and will make your job MUCH MORE DIFFICULT.
It's good design for a spreadsheet, but it is *incorrect* design for a
relational database! You should store data *in fields*, as data, not
in *fieldnames*.

A much better design would be to have a tall, thin table with fields
SaleYear, SaleMonth, Volume with records like

2001; 1; 12305
2001; 2; 15440
2001; 3; 14209

in place of a 2001 table with fields like

Vol_Jan Vol_Feb Vol_Mar
12305 15440 14209


John W. Vinson[MVP]
 
Back
Top