join tables sequencialy

[Yossi evenzur]

Hi
I have 2 tables, with sea temp per date, how can i join 2 tables one with
data from 01/01/2008 until 30/03/2008 and another table with data from
01/03/2008 to 01/11/2008. i don't want to cut the tables and don't want to
use SQL, is it possible?

 

[Yossi evenzur]

so, no other option but SQL?

Get someone else to write the SQL for you? You need a Union query and
that requires writing a small amount of SQL.

SELECT * FROM TableA
UNION ALL SELECT * FROM TableB

 

[Rick Brandt]

so, no other option but SQL?

What is your objection to using SQL? I suppose you could insert both
tables into a third table with append queries and then display that.

 

[Yossi evenzur]

don't know SQL very well (hmmm, rimes), also tried your example and got an
error massage "Too many fields defined". both tables are identical, and i
want to have a single table without the overlapping entries, do i have to
union only the date field?

 

[Rick Brandt]

don't know SQL very well (hmmm, rimes), also tried your example and got
an error massage "Too many fields defined". both tables are identical,
and i want to have a single table without the overlapping entries, do i
have to union only the date field?

Access will balk if the total of both tables exceeds 255 fields. If that
is what you have then your design is most likely messed up. If you
didn't want any duplicates in the output then you would use UNION instead
of UNION ALL, but you will have to resolve the (too many fields) problem
before it will work.

 

[Duane Hookom]

Seems to me if you got "Too many fields defined", you would tell us how many
fields you have in your tables in the query. You should also provide the SQL
view of the query.

Is your date field unique/primary key in each of the tables? Do you really
want to exclude all records that are common to both tables or just display
one of the duplicate date records?

 

[Yossi evenzur]

the date field is not unique and i want to display one of the duplicate recodrs

 

[Yossi evenzur]

hmmm, i couldn't understand from the help how union works, when i state
"union all" i'm forcing access to multiply the fields? if i have 5 fields in
both query, does union all creates a table with 10 fields?

 

[Rick Brandt]

hmmm, i couldn't understand from the help how union works, when i state
"union all" i'm forcing access to multiply the fields? if i have 5
fields in both query, does union all creates a table with 10 fields?

I believe in a UNION query the error is raised when there are too many
fields in the source tables. I don't think it matter how many fields are
in the output. Even though the output is combined vertically (by row
rather than column) the combined total of the fields in the input tables
cannot exceed 255.

 

[Yossi evenzur]

is there a vertical limit?

 

[Rick Brandt]

is there a vertical limit?

No.

 

[Stefan Hoffmann]

hi,

is there a vertical limit?

No, only the the maximum size of 2GB per Access database and the amount
of RAM should limit it, but its more then 64k rows...

mfG
--> stefan <--

 

[Jeff Boyce]

Already responded to in another newsgroup. Please don't post the same
question to multiple separate groups.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

Yossi,
Can you step back and tell us exactly what you need? Your request is much
like hitting a moving target that reveals a little at a time. This wastes a
lot of time and effort.

Do you have a field count for us? If you truly have too many fields, the
solution might look very different than a solution with a few fields.

Also, if the date field isn't unique in the individual tables than you could
have two or more records in the same table with the same date. How would you
want to handle these duplicates?

 

[Yossi evenzur]

Hi Jeff
I put this as a suggestion since i found my self, trying to consolidate 2
tables or more and resorting to SQL, so i thought it would be a nice
addition. the same time i was looking for a solution for NOW. sorry if it
caused confusion.

 

[Yossi evenzur]

Hi Duane
here it is: i have 2 tables with the exact field structure and names, 240
fields.
one table starts on 03/08/2008 and ends on 25/10/2008 the second table
starts on 26/08/2008 and end on 22/11/2008. i need treat these tables as one,
meaning, i'd like to have one table that starts on 03/08/2008 and ends on
22/11/2008 with consecutive entries and without multiple identical entries,
e.g. not to have double entries for 01/09/2008. I have 6 pairs of these excel
sheets and i really want to avoid the cut & paste and without SQL IF POSSIBLE.
hope this is more clear

 

[Stefan Hoffmann]

hi Yossi,

here it is: i have 2 tables with the exact field structure and names, 240
fields.

This indicates, that your tables are not normalized. Tables with more
then 20 fields are rare, tables with more than 200 fields...

See

http://en.wikipedia.org/wiki/Database_normalization


mfG
--> stefan <--

 

[John W. Vinson]

Hi Duane
here it is: i have 2 tables with the exact field structure and names, 240
fields.
one table starts on 03/08/2008 and ends on 25/10/2008 the second table
starts on 26/08/2008 and end on 22/11/2008. i need treat these tables as one,
meaning, i'd like to have one table that starts on 03/08/2008 and ends on
22/11/2008 with consecutive entries and without multiple identical entries,
e.g. not to have double entries for 01/09/2008. I have 6 pairs of these excel
sheets and i really want to avoid the cut & paste and without SQL IF POSSIBLE.
hope this is more clear

One thing that may not be obvious: there is *nothing* magical about SQL. Don't
confuse the language SQL (short for Structured Query Language) with the
software product SQL/Server! All queries, whether built using the query grid
or not, are SQL; it is *the* language of queries. So you're using SQL any time
you use Access.

Since you're dealing with non-normalized spreadsheet data, I fear a UNION
query will indeed give problems. However it should be possible to create a
local table in Access with the same 240 fields; you can set a unique Index on
the field or combination of (up to ten) fields which define "a unique entry".
You'll then be able to run Append queries to migrate the data from the
spreadsheets into this table; duplicates will be removed in the process by
this unique index. If there are more than ten fields that define a duplicate
you can remove the duplicates later if need be.