combine data

[shammy]

hi,
I'm using Accsess 2000. I had a problem to combine data in 3 different table but with same format /structure. I try to use SQL Query..(Select * from A UNION Select * from B) but there is errors " too many fields define".FYI my tables has more than 50 fields.Pls help me!!!

[PinkPanther2003]

With 50 fields in your table you may want to revisit your table design. A good normalized table should be that large.

The syntax you have used is correct. Therefore the problem must lie elsewhere.

Do you have any Memo or Hyperlink fields in your table?

HTH,

Peter

[shammy]

hi Peter,
thanks for your reply. Anyway, I'm not sure where the problem is? Could be the table field? The real situation is, I want to combine data from 3 tables in each table there is duplicate data. How do I go about? Any other way to do instead of SQL query. By the way, there's no hyperlink and memo in these table.

[KnooKie]

compare tables...........

http://www.databasejournal.com/scrip...le.php/1579951

or

append all 3 tables data into one table and do a groupby query and run a make table query on that. Duplicates will have gone.

[PinkPanther2003]

UNION removes duplicates automatically.

UNION ALL includes duplicates in the output.

Carefully check you field names and data types and their ordinal position in the query, they have to all be the same. Don't use *, explicitly state what fields you want in the output. It should be easier to debug that way.

HTH,

Peter

[shammy]

hi pinkpanther,
I need * field to be combined/display (> 50)...do I need to specify each one????

[PinkPanther2003]

Well 50 fields leaves alot of room for error now doesn't it!

No you can use * but remember the fields must be of the same data types at the same ordinal position...
eg Query 1 Field 10, Query 2 Field 10, Query 3 Field 10 etc, must all be the same data type. Otherwise you'll get an error. So yes you can use * but it's harder to debug. Try adding 10 fields at a time and seeing if you get an error after adding each field. It will take the longest but will work!

HTH,

Peter