3 tables in query (one to many x2)

[dk]

this is probably obvious but i cant see the wood for the trees.

anyway, i have a query with three tables. table1 has unqiue primary key
(id) that is joined to table2 (the many side).
table1s unique primary key is also joined to table3 (again the many
side).


so my query is showing the following....

table1.id table1.data table2.id table2.link table2.value table3.id
table3.link table3.value
4 sample 7 4 10 9
4 50
4 sample 7 4 10 10 4
70
4 sample 6 4 5 9
4 50
4 sample 6 4 5 10
4 70


in this example table1 has only one record and both table2 and table3
have only two records. if i remove either table2 or table3 i get the
correct results but not with both together.

i want to show the following...

table1.id table1.data table2.id table2.link table2.value table3.id
table3.link table3.value
4 sample 7 4 10 9
4 50
4 sample 6 4 5 10 4
70

I dont want a join between table2 and table3 as im just interested in
the total value.

I suspect i could do this from the query using just table1 and table2
and use the dsum function and specify table3s.id as the criteria.

any help appreciated,,

 

[David S via AccessMonster.com]

Hi dk,

If you want the total values, wouldn't the output look like:
4 sample 30 240

The output you've specified looks like it shows the first record in table2
with the first record in table3, and the second record with the second record
- but without some other sort of link between the two tables, I don't see how
an SQL statement could make that sort of decision. I'm not very familiar with
the dsum function, but I don't think it would be able to handle this sort of
ambiguity anyway.

You could get to this sort of output using a GROUP BY with FIRST and LAST
qualifiers, but then you'd have to stick the output of those queries back
together again with a union query. And it wouldn't handle more than two
records, and wouldn't handle one record very well either...

 

[David S via AccessMonster.com]

Hi dk,

If you want the total values, wouldn't the output look like:
4 sample 30 240

The output you've specified looks like it shows the first record in table2
with the first record in table3, and the second record with the second record
- but without some other sort of link between the two tables, I don't see how
an SQL statement could make that sort of decision. I'm not very familiar with
the dsum function, but I don't think it would be able to handle this sort of
ambiguity anyway.

You could get to this sort of output using a GROUP BY with FIRST and LAST
qualifiers, but then you'd have to stick the output of those queries back
together again with a union query. And it wouldn't handle more than two
records, and wouldn't handle one record very well either...

 

[dk]

Thanks David S,

You're correct the total values would be 4, sample, 30, 240. This is my
problem - There are only two records in table2 and the value should be
15 not 30. ditto table3 the value should be 120 not 240.

It seems like when viewing all three tables together in the query,
multiple instances of the same record are getting displayed (so every
combination is shown). As said previously when just the two tables are
shown everything is fine - its just when introducing the third table
when things go wrong.

Normally i would solve this problem using the following...

table1 (with a one to many join to...) table2 (with a one to many join
to...) table3

which does display the data correctly in the query but obviously
requires a record in table2 in order to be related to table3

now the method im using which is....

table1 (with a one to many join to...) table2 AND
table1 (with a one to many join to...) table3

this solves the problem that a record can exist in table2 OR table3
which are not really dependant on each other. the downside is the
problem im having showing multiple instances of the same record.

Im sure ive made an obvious mistake and just cant see it.

 

[David S via AccessMonster.com]

(Sorry about the double post earlier, btw)

Ah, I see what you mean - so does this mean you do actually want the correct
totals for each table? You could do this with:

Query2:
SELECT table1.id, table1.data, Sum(table2.value) AS SumOfvalue
FROM table1 INNER JOIN table2 ON table1.id = table2.link
GROUP BY table1.id, table1.data;

Query3:
SELECT table1.id, table1.data, Sum(table3.value) AS SumOfvalue
FROM table1 INNER JOIN table3 ON table1.id = table3.link
GROUP BY table1.id, table1.data;

QueryBoth:
SELECT Query2.id, Query2.data, Query2.SumOfvalue, Query3.SumOfvalue
FROM Query2 INNER JOIN Query3 ON (Query2.data = Query3.data) AND (Query2.id =
Query3.id);

If you wanted to show records from both tables that didn't necessarily match,
you would need to change QueryBoth above to include not only matching records,
but all of the records from Query2 that didn't have matching record in Query3
- let's call it QueryLeft:
SELECT Query2.id, Query2.data, Query2.SumOfvalue, Query3.SumOfvalue
FROM Query2 LEFT JOIN Query3 ON (Query2.data = Query3.data) AND (Query2.id =
Query3.id);

And then you'd need another query to show all of the records from Query3 that
didn't have a match in Query2, called QueryRight:
SELECT Query3.id, Query3.data, Query2.SumOfvalue, Query3.SumOfvalue
FROM Query2 RIGHT JOIN Query3 ON (Query2.data = Query3.data) AND (Query2.id =
Query3.id)
WHERE Query2.SumOfvalue Is Null;

Then, you use a UNION query to put them together:
SELECT * FROM QueryLeft UNION SELECT * FROM QueryRight

Does that give you what you were looking for?

 

[dk]

Fantastic...Many thanks David S

had tried the subqueries but had forgot about that second join!!! That
solves the problem.

I'd actually been toying with the idea of changing the source of the
query by using just two tables for the data (and setting up an
indicator field to identify a '2' or a '3' record). This would allow me
the control that i was looking for recording my data whilst hopefully
making calculations easier (table2 will be positive value, table3 will
be negative value- records balance to zero etc within the same field).

I will give your other answer a try also (just to see the effect it has
on the data).

Thanks again!!

 

[David S via AccessMonster.com]

I'd actually been toying with the idea of changing the source of the

query by using just two tables for the data (and setting up an
indicator field to identify a '2' or a '3' record). This would allow me
the control that i was looking for recording my data whilst hopefully
making calculations easier (table2 will be positive value, table3 will
be negative value- records balance to zero etc within the same field).

Absolutely - if you've got the ability to change your table that way, it
would work much better, especially if you want to do things with the two
measures together like this. You would need to be a little more clever in
using it on forms and things, but those are surmountable, and it also makes
it easier if you want to, say, introduce a third measure into the mix . Good
luck with it!