Join two queries without a like join

[Dean0_53]

I have two queries with semester schedules. One has fall term and the other has spring term information. My client would like to be able to view everything in one large Excel file. I can actually run the query with both terms but they would like the fall term across in one row with all the column data and the spring term also on the same sheet in a separate row right next to it. The attached file shows one course with the fall term. I can run two make tables but there isn't a like join to connect these. Is there anyway to get the fall data in one set of rows and columns and the spring data all on the same file?



Thanks!

Deano

[JoeM]

I am unable to download your files from my current location (security policy), but if you have two separate queries structured similarly and would like to combine them into a single query/list, you can create a Union query to do that.
See: https://support.office.com/en-us/art...0-ad0a75541c6e

[Dean0_53]

JoeM,

I can get the Union Query to run, but both of my terms are in the same column. I would like 2018 with the 2018 data and then 2019 next to it with the 2019 data.

Is there a way to separate the terms into two columns?

[JoeM]

One way would be two update each query to have both a 2018 column and a 2019 column, and then create IF statements to display the data in the appropriate columns.
Then, the Union query should do what you want (since both underlying queries have both columns).

[Dean0_53]

I can easily design the quires to have both terms, but I don't know how to create an IF statement.

[JoeM]

Actually, you probably don't need IF statements. If the 2018 query, you would just have all the 2018 fields, and then after those the 2019 field names, returning blanks.
And then just the opposite for the 2019 query.

So really, just creating blank "dummy" place holders in each query so that each query has all the same fields in the same order.

[Dean0_53]

Not sure what you mean about returning blanks or "dummy" placeholders.

[JoeM]

Let's say that your have two very simple queries based on two Tables

Table 1 (for 2018)
- Name
- Balance

Table 2 (for 2019)
- Name
- Balance

So, change the first query so that it looks something like this:

SELECT [Name], [Balance] as Balance_2018, 0 as Balance_2019
FROM Table1

and the second query like this:

SELECT [Name], 0 as Balance_2018, [Balance] as Balance_2019
FROM Table2

Now, since both queries have the EXACT same number of fields in the EXACT same order, the UNION query between the two should work well.

[Dean0_53]

Ok thanks I will give this a shot, but probably not until tomorrow.


Thanks again!

[JoeM]

You are welcome.

If it does not work out the way you like, please post a small example of what the data in each table looks like, and what you want your expected result to look like.

[Dean0_53]

The two queries produce tables that look like this only with more data. When I run the query, I only get the fall data.

SELECT Tbl1.[Fall Term], Tbl1.Status, Tbl1.Crn, Tbl1.Subj, Tbl1.[Crse Number]
FROM Tbl1;


Union




SELECT Tbl2.[Spring Term], Tbl2.Status, Tbl2.Crn, Tbl2.Subj, Tbl2.[Crse Number]
FROM Tbl2;


They would like to be able to put the spring term and all its data right next to this.

Fall Term	Status	Crn	Subj	Crse Number
201809	Active	16286	ACCT	101
201809	Active	16304	ACCT	101

What am I missing?

Thanks,

[JoeM]

Look at my previous example again. In each query, you need two complete sets of the fields that you want to return, one for the Spring Term, one for the Fall Term.
So, in each of them, you would have a "dummy" (blank) set. So for the Spring Term, the Fall Term set would be blank. And for the Fall Term, the Spring set would be blank.

However, I am beginning to think that this may not actually be the way that you go about this.
When you say that you want the Spring and Fall Term data right next to each other, do you mean for each record (row), you will have actual Spring and Fall data (not zeroes or blanks)?
If so, then you will need to join these two queries (or tables), so that the correct Spring Term records are shown on the same line as the correct Fall Term records.
You can only do this if you have a common field between these two tables/queries that you can join on. Do you?

If so, then you do not need a Union Query at all. It is a simple query, joining the two tables on the common fields, and return all the necessary Spring/Fall fields from each table.

[Dean0_53]

The table would look similar to this. Much of the information in each table is the same but since there are for different terms, they'er not really releated. There could be a REST 130 in the FAll and a 130 in the spring that meets a different times and days or there may not be a 152 in the spring but a different REST course or courses all together.

The problem is the 201809 (or fall) column is returning many rows of REST 130 for the same information. I put a "distinct" in there but that didn't help.

Fall Term	Tbl1_Status	Tbl1_Crn	Tbl1_Subj	Tbl1_Crse Number	Spring Term	Tbl2_Status	Tbl2_Crn	Tbl2_Subj	Tbl2_Crse Number
201809	Active	16339	REST	130	201901	Active	27151	REST	102C

SELECT DISTINCT Tbl1.[Fall Term], Tbl1.Status, Tbl1.Crn, Tbl1.Subj, Tbl1.[Crse Number], Tbl2.[Spring Term], Tbl2.Status, Tbl2.Crn, Tbl2.Subj, Tbl2.[Crse Number] INTO TBLTEST
FROM Tbl1 LEFT JOIN Tbl2 ON (Tbl1.Status = Tbl2.Status) AND (Tbl1.Subj = Tbl2.Subj)
WHERE (((Tbl1.Subj)="REST") AND ((Tbl2.Subj)="REST"));

[JoeM]

The problem is the 201809 (or fall) column is returning many rows of REST 130 for the same information. I put a "distinct" in there but that didn't help.

If any of the fields being returned is different, then it is not considered to be a duplicate.
You may be better off doing the following: Create two queries, one for each table in which you return the Fields that you want and remove the duplicates there.
Then, join these two queries in a third query, where you combine the Spring and Fall data together.

[Dean0_53]

I tried that using two make-tables, but I wasn't able to get a join to work between the two tables. I think for now, I will put this aside.

Thanks for trying to help!