Get records from two 1:N relationships without every combination onto a report (1 Viewer)

chrisleng

Registered User.
Local time
Today, 00:50
Joined
Feb 24, 2010
Messages
145
I have data in two tables like:

IDParentIDValue
16ID61
17ID62
18ID63

and
IDParentIDCode
35ID6A
36ID6B

and I want the report to show this in the detail:

ParentIDValueCode
ID61A
ID62B
ID63

instead of how a query would normally give: (without using two queries and two subreports in the detail band (because they're memo fields and growing gets tricky))

ParentIDValueCode
ID61A
ID61B
ID62A
ID62B
ID63A
ID63B


is this possible with queries at all? I wondered about doing a Union but then I may as well just use two queries and two subreports instead of the union
 
Last edited:

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
Hi. I think you will need at least two queries, but you can nest them as subqueries, if you don't want to see two separate queries.
 

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
Hi. I stand corrected. I just gave it a try using an OUTER JOIN, and I only used one query. Here's what I tried:
Code:
SELECT T1.ID, T1.F1, T2.F1
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.ID=T2.ID
Hope that helps...
 

chrisleng

Registered User.
Local time
Today, 00:50
Joined
Feb 24, 2010
Messages
145
That's odd, I don't get the same result using an outer join, I get the 6 records,

Or, well, 15 records because I have 5 records in Table1 and 3 records in Table2 but I didn't want to make my post too long!
 

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
Can you post a sample copy of your db? Thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:50
Joined
Feb 19, 2013
Messages
11,804
think you need something that will associate 1 with A, 2 with B. Or something that defines order - 2 follows 1, B follows A. I suspect your example is too simplistic to reflect the real life situation but you could add 64 to value in table 1 which is the ascii value for A in table 2 - so join on that

Code:
SELECT T1.ID, T1.F1, T2.F1
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.ID=T2.ID
WHERE chr(T1.F1+64)=T2.F1
 

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
Here's what my little test look like:

Table1
t1.PNG

Table2
t2.PNG

Query Result
qry.PNG
 

Pat Hartman

Super Moderator
Staff member
Local time
, 19:50
Joined
Feb 19, 2002
Messages
28,612
Just because the two tables contain the same foreign key doesn't mean that joining them makes sense and you have already discovered that. The solution is two subreports. One to show each set of data.
 

chrisleng

Registered User.
Local time
Today, 00:50
Joined
Feb 24, 2010
Messages
145
Hi Pat, I only wanted to join them for display reasons not data relation reasons, cheers,
 

chrisleng

Registered User.
Local time
Today, 00:50
Joined
Feb 24, 2010
Messages
145
Here's what my little test look like:

Table1
View attachment 79023

Table2
View attachment 79024

Query Result
View attachment 79025
Ahh, I see how that works now, your ID's are sequencial so 3 and "name3" both share ID 3, because I've got data from two different tables filtered by Set=ID6, with their own ID's of e.g. 16, 17, 18 from table 1 and 35, 36 from table 2 I can't join the way you do on ID=ID, I'll add a unique ID column to my sample data to demonstrate
 

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
Ahh, I see how that works now, your ID's are sequencial so 3 and "name3" both share ID 3, because I've got data from two different tables filtered by Set=ID6, with their own ID's of e.g. 16, 17, 18 from table 1 and 35, 36 from table 2 I can't join the way you do on ID=ID, I'll add a unique ID column to my sample data to demonstrate
Ah, that makes sense. Well, without a unique value to connect the two tables, you'll end up with a Cartesian Product.
 

chrisleng

Registered User.
Local time
Today, 00:50
Joined
Feb 24, 2010
Messages
145
yup, I was hoping there was an easy workaround to get only the unique values both sides
 

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
yup, I was hoping there was an easy workaround to get only the unique values both sides
Well, if you look at the cartesian result you originally got, all records are basically unique. No two records are the same. So, to the computer, you got unique results. However, to you, it's not unique enough. So, you'll have to give the computer additional information/instruction to get to the same result as you expected.
 

chrisleng

Registered User.
Local time
Today, 00:50
Joined
Feb 24, 2010
Messages
145
Yea I know what you mean, each record is unique but it's actually an either A or B unique on two columns which isn't an option in SQL I dont think
 

theDBguy

I’m here to help
Local time
, 16:50
Joined
Oct 29, 2018
Messages
8,630
Yea I know what you mean, each record is unique but it's actually an either A or B unique on two columns which isn't an option in SQL I dont think
You might have to consider using VBA for this.
 

MajP

You've got your good things, and you've got mine.
Local time
, 19:50
Joined
May 21, 2018
Messages
2,785
Need two rank queries
qryOne
Code:
SELECT A.Field1, A.Value, (SELECT Count(*)
FROM Table1 AS B
WHERE B.ID < A.ID) AS Rank
FROM Table1 AS A;
qry2
Code:
SELECT A.Field1, A.Code, (SELECT Count(*)
FROM Table2 AS B
WHERE B.ID < A.ID) AS Rank
FROM Table2 AS A;
If values are always more than codes then. If that is not the case you will have to do another right join and then a union query
Code:
SELECT
qryOne.Field1,
qryOne.Value,
qryTwo.Code
FROM qryOne
LEFT JOIN
qryTwo
ON
(qryOne.Rank = qryTwo.Rank) AND (qryOne.Field1 = qryTwo.Field1);
qryThree

Field1ValueCode
1d61A
1d62B
1d63
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom