Joining queries that return different no. of records.

[PaulSpell]

Help joining queries that return different nos. of records.

I am having real trouble trying to join two queries together. Both queries are based on different tables which contain the same type of data. The problem is that each query returns a different number of records as follows:

Query1
Key1 Key2 Text
xxxx xxxx some text
xxxx xxxx some text


Query2
Key1 Key2 Text
xxxx xxxx some more text
xxxx xxxx some more text
xxxx xxxx some more text
xxxx xxxx some more text


I want to show all results for the two key fields in one query without any duplicates. But when I try to combine these two queries into a third query I get the following:

Query3
Key1 Key2 Text1 Text2
xxxx xxxx some text some more text
xxxx xxxx some text some more text
xxxx xxxx some text some more text
xxxx xxxx some text some more text


But the result I want is this:

Query3
Key1 Key2 Text1 Text2
xxxx xxxx some text some more text
xxxx xxxx some text some more text
xxxx xxxx ---------- some more text
xxxx xxxx ---------- some more text

Does anyone know how to do this (if it can be done)?

 

[KenHigg]

I assume you are doing 1 -> Many on the Query1 -> Query2 join?

ken

 

[PaulSpell]

I assume you are doing 1 -> Many on the Query1 -> Query2 join?

ken

Yes, but each occurrence of key1 and key2 in the first query is joining to EACH occurrence of key1 and key2 in the second query.

To complicate things further, sometimes query1 will have more records than query2 and sometimes the opposite is true. I need to show all of the records, but only once.

Got any ideas because this ones really got me stumped?

 

[KenHigg]

This is simply the way your query should work when joined as you've described. What will you ultimatley be doing with your query? Can you give an example with some real data samples?

 

[PaulSpell]

This is simply the way your query should work when joined as you've described. What will you ultimatley be doing with your query? Can you give an example with some real data samples?

Here is an example:

Query 1 reads:
A/c No Year Text
0001 1998 descriptive text 1
0001 1998 descriptive text 2

Query 2 reads:
A/c No Year Text
0001 1998 more descriptive text 3
0001 1998 more descriptive text 4
0001 1998 more descriptive text 5


Utimately I want to compare the results of query1 with the results of query2 so that I have all of the results together as follows:


Query 3 reads:
A/c No Year Query1 Text Query2 Text
0001 1998 descriptive text 1 more descriptive text 3
0001 1998 descriptive text 2 more descriptive text 4
0001 1998 ----------------- more descriptive text 5

So what I want to do is join query1 to query2 in such a way that I get the results in query3 above.


Sorry if I haven't explained very well.

 

[PaulSpell]

Any ideas? Anyone? Please.......

 

[PaulSpell]

Sorry for not responding sooner, I've been away for a couple of days.

Anyway, at present Query3 (the query I want to contain the results) joins a table containing a full set of a/c nos and years to Query1 and Query2, using left joins on both fields to each query.

Here is the SQL:

SELECT tblPopulation.AccNo, tblPopulation.Year, Query2.Q2Text, Query1.Q1Text
FROM (tblPopulation LEFT JOIN Query2 ON (tblPopulation.Year = Query2. Year) AND (tblPopulation.AccNo = Query2.AccNo)) LEFT JOIN Query1 ON (tblPopulation.Year = Query1. Year) AND (tblPopulation.AccNo = Query1.AccNo)
GROUP BY tblPopulation.AccNo, tblPopulation.Year, Query2.Q2Text, Query1.Q1Text;


I'd really appreciate some help on this.

Thanks.

 

[PaulSpell]

So no ideas then?

 

[KenHigg]

Paul, You could group the results in a report.

???
ken

 

[PaulSpell]

Tried it and it doesn't work. Besides I really need the outpput from the query to produce spreadsheets etc.

Thanks for the idea though.

 

[KenHigg]

Could you do a temp type table that populates each time you need to look at the data using appends from each of the master tables. The when you're done, you delete the records in the temp table?

ken

 

[PaulSpell]

Could you do a temp type table that populates each time you need to look at the data using appends from each of the master tables. The when you're done, you delete the records in the temp table?

ken

Yes but I still get the unmatched records.

This is what I get now:

Query3
Key1 Key2 Text1 Text2
xxxx xxxx some text1 some more text1
xxxx xxxx some text1 some more text2
xxxx xxxx some text1 some more text3
xxxx xxxx some text1 some more text4
xxxx xxxx some text2 some more text1
xxxx xxxx some text2 some more text2
xxxx xxxx some text2 some more text3
xxxx xxxx some text2 some more text4


But the result I want is this:

Query3
Key1 Key2 Text1 Text2
xxxx xxxx some text1 some more text1
xxxx xxxx some text2 some more text2
xxxx xxxx ---------- some more text3
xxxx xxxx ---------- some more text4


Can't fathom this. I'm not sure it can be done!

 

[KenHigg]

Can you put a hundred or so records in each table, put those tables and the two queries in a seperate db and post it?

ken

 

[PaulSpell]

Pat,

You are right about what is hapenning. I did try using a union query, with the idea of then crosstabbing the data to show the two columns, but that didn't work because you can't crosstab text.

I like your idea of using sub-reports. I think I will go with this and create a seperate subreport for each section of the report.

Thanks for your help.