Combining Tables by Query

[jlclark4]

Good Morning,



I have two tables with similar information (they are too big to combined). In a query I want the name and the score to appear. (The form has questions with scores and names) I want to combined them so its one continues table (Names for both Table 1 & 2 and scores from both 1 & 2) Is this possible? I can set up a sample if that helps!

Thanks,

[ajetrumpet]

they're too big for a union?

[jlclark4]

If you mean copying the fields and putting them in one table, then yes...its exceeds the 255 field limit. I have a book for Access 2003. I'm researching this Union thing you mentioned. Its using SQL? I would create two queries with the data needed and matching from both tables? What would I do from there? Im having trouble understanding what the book is telling me...is it possible I can create a sample and someone help me with it?

[ajetrumpet]

unions are for RECORDS, not fields.

you want students and scores in one table? what are we talking about here? student and score info across fields or down records?

if they're across fields, the structure is pretty bad. move it back to excel and transpose the information so it's vertical and then import it back into access. that might be your best bet to get it into database-friendly structure, actually

[jlclark4]

For the sake of size limits I deleted a lot of fields but the main ones I need are there. It will be easier to explain this way. If you notice, Query 1 and Query 2 have the same exact field names (but from different tables) and the information stored in those tables are the same type. I need to compile a list that has both of the queries together...is that possible? I want to create it down records not across fields.

[ajetrumpet]

why not this:

SELECT Table2.Date, Table2.Name, Table2.[Cert #], Table2.Score
FROM Table2

UNION

SELECT Table1.Date, Table1.Name, Table1.[Cert #], Table1.Score
FROM Table1;

[jlclark4]

Got it. Thanks!

[ajetrumpet]

Got it. Thanks!

no problem, bud. congrats! =)

[jlclark4]

Can you not use the Union to join two queries?

SELECT qryCDSC.Date, qryCDSC.Name, qryCDSC.TeamLead, qryCDSC.Manager, qryCDSC.[Cert #], qryCDSC.Score
FROM qryCDSC
UNION 
SELECT qryCDSI.Date, qryCDSI.Name, qryCDSI.TeamLead, qryCDSI.Manager, qryCDSI.[Cert #], qryCDSI.Score
FROM qryCDSI,

It gave me "Syntax error in FROM clause"

[jlclark4]

I figured it out! Sorry I used , instead of ;