Compound Query 1

[Positivethinking]

Hello everyone, I need to create a query(ies) that allow to display all Subjects and Tubes with all the Analytes, notice that TABLE1 has Analytes1,2,3 and TABLE2 has Analytes4,5. Subj 1A has all 5 Analytes, a Subject could be in table 1 but not in table 2 or viceversa, or could be in both. The desired display is all the way below. Your help is much appreciated. Thanks

TABLE1
MyID Subj Tube Site An1 An2 An3
1A 1 1 A 2 3 4
1A 1 2 A 3 9 0
8A 8 1 A 7 5 6


TABLE2
MyID Subj Tube Site An4 An5
1A 1 1 A 0 5
1A 1 2 A 0 4
2C 2 1 C 4 7
2C 2 2 C 2 9
3D 3 2 D 3 9
5C 5 1 C 1 6



DESIRED DISPLAY
MyID Subj Tube Site An1 An2 An3 An4 An5
1A 1 1 A 2 3 4 0 5
1A 1 2 A 3 9 0 0 4
2C 2 1 C - - - 4 7
2C 2 2 C - - - 2 9
3D 3 2 D - - - 3 9
5C 5 1 C - - - 1 6
8A 8 1 A 7 5 6 - -

 

[dhookom]

You would first normalize your data with a union query:
quniPositiveThinking

SELECT MyID, Subj, Tube, Site, "An1" AS AN, An1 AS TheVal
FROM PTTable1
UNION ALL
SELECT MyID, Subj, Tube, Site, "An2", An2
FROM PTTable1
UNION ALL
SELECT MyID, Subj, Tube, Site, "An3", An3
FROM PTTable1
UNION ALL
SELECT MyID, Subj, Tube, Site, "An4", An4
FROM PTTable2
UNION ALL
SELECT MyID, Subj, Tube, Site, "An5", An5
FROM PTTable2;

Then create a crosstab from the results:

TRANSFORM First(quniPositiveThinking.TheVal) AS FirstOfTheVal
SELECT quniPositiveThinking.MyID, quniPositiveThinking.Subj, quniPositiveThinking.Tube, quniPositiveThinking.Site
FROM quniPositiveThinking
GROUP BY quniPositiveThinking.MyID, quniPositiveThinking.Subj, quniPositiveThinking.Tube, quniPositiveThinking.Site
PIVOT quniPositiveThinking.AN;

Duane
Hook'D on Access
MS Access MVP

 

[Positivethinking]

Thanks a lot dhookom, i appreaciate your help
your suggestion works quit well

although this is just an example and i have lots of analytes(ABOUT 40 on each table) in about 6 tables that i need to join and that may make to run the query slow.

I have come up with this idea and it also gives the same result, i'd like to hear anyone's feed back about this:
-------------------------------------------------------
COMMON SUBJECTS IN TABLE 1 AND TABLE 2, BASED ON MyID and Tube
SELECT Table1.MyID, Table1.Subj, Table1.Tube, Table1.Site, Table1.An1, Table1.An2, Table1.An3, Table2.An4, Table2.An5
FROM Table1 INNER JOIN Table2 ON (Table1.MyID = Table2.MyID) AND (Table1.Tube = Table2.Tube)
GROUP BY Table1.MyID, Table1.Subj, Table1.Tube, Table1.Site, Table1.An1, Table1.An2, Table1.An3, Table2.An4, Table2.An5;
--------------------------------------------------------
SUBJECTS PRESENT IN TABLE 1 BUT NOT IN TABLE 2
SELECT Table1.MyID, Table1.Subj, Table1.Tube, Table1.Site, Table1.An1, Table1.An2, Table1.An3
FROM Table1 LEFT JOIN Table2 ON Table1.MyID = Table2.MyID
WHERE (((Table2.MyID) Is Null));
----------------------------------------------------
SUBJECTS PRESENT IN TABLE 2 BUT NOT IN TABLE 1
SELECT Table2.MyID, Table2.Subj, Table2.Tube, Table2.Site, Table2.An4, Table2.An5
FROM Table2 LEFT JOIN Table1 ON Table2.MyID = Table1.MyID
WHERE (((Table1.MyID) Is Null))
-------------------------------------------------------
AND FINALLY A QUERY JOIN THE 3 PREVIOUS QUERIES
SELECT * from commonsubj
union SELECT MyID, Subj, Tube, Site, An1,An2,An3,"-","-" from [Table1 Without Matching Table2]
UNION SELECT MyID, Subj, Tube, Site, "-","-","-", An4,An5 from [Table2 Without Matching Table1]


AGAIN Thanks a lot for your replies.

 

[dhookom]

You could just create a union query of the MyID, Subj, Tube, Site fields from all tables which would remove all duplicates. Then do a LEFT or RIGHT JOIN to all the other tables.

Duane
Hook'D on Access
MS Access MVP

 

[Positivethinking]

dhookom
that's a great idea, i tested in access and it works, i hope it works in sybase sql (infomaker)


Thanks a lot for your help

 

[Positivethinking]

forgot the *, you deserve it!