 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / November 2008join tables sequencialy
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? > 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? 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  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com so, no other option but SQL? > > Hi > > I have 2 tables, with sea temp per date, how can i join 2 tables one [quoted text clipped - 7 lines] > SELECT * FROM TableA > UNION ALL SELECT * FROM TableB > 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. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com 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? > > 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. > 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. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com 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? > > 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, [quoted text clipped - 6 lines] > of UNION ALL, but you will have to resolve the (too many fields) problem > before it will work. > 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. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com is there a vertical limit? > 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. > is there a vertical limit? No. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com 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 <-- 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? SignatureDuane Hookom Microsoft Access MVP > hi, > [quoted text clipped - 4 lines] > mfG > --> stefan <-- 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 :-) > Yossi, > Can you step back and tell us exactly what you need? Your request is much [quoted text clipped - 16 lines] > > mfG > > --> stefan <-- 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 <-- >Hi Duane >here it is: i have 2 tables with the exact field structure and names, 240 [quoted text clipped - 6 lines] >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. SignatureJohn W. Vinson [MVP] 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? SignatureDuane Hookom Microsoft Access MVP > 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 [quoted text clipped - 5 lines] > > 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. the date field is not unique and i want to display one of the duplicate recodrs > 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 [quoted text clipped - 13 lines] > > > 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. Already responded to in another newsgroup. Please don't post the same question to multiple separate groups. Regards Jeff Boyce Microsoft Office/Access MVP > 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? 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. > Already responded to in another newsgroup. Please don't post the same > question to multiple separate groups. [quoted text clipped - 9 lines] > > 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? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.