join properties or SQL view to return ALL fields in crasstab query from BOTH tables?

[wiklendt]

join properties or SQL view to return ALL jfields in crasstab query from BOTH tables?

hi, i have a crosstab query with two tables:

1) samples tested
2) targets to test against

i want to return ALL samples against ALL targets. at the moment, it seems the join properties in a query allows ONLY 3 ways to do it (i need "the 4th"!)

EITHER
all samples, but not all targets (does not return targets with no hits from a sample)

OR
all targets, but not all samples (does not return samples with no hits against a target)

OR
some of both, but not all of either (no negatives at all)

WHAT I WANT
is to return ALL samples with ALL targets (to return any targets that had no hits AND any samples that gave no hits) but in the one query, because i later am select-querying this crosstab, and would prefer not to have to make several to refer to. (indeed to review just on query to check the sample/target results would be a big help already)

my SQL for the three join types look like this, if it's any help to anyone:
(edit: in the following "qryRLBrun_results_forreport.Name" is the Sample, and "tblRLBtargets.ShortName" is the Target field - the other fields are extra info we thought would be handy in that cross tab, but i suppose can be added downstream if that helps my join issue)

"joined fields from both tables are equal"

TRANSFORM Count(qryRLBrun_results_forreport.RunDate) AS CountOfRunDate
SELECT qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev, Count(qryRLBrun_results_forreport.RunDate) AS [Total Of RunDate]
FROM tblRLBtargets INNER JOIN qryRLBrun_results_forreport ON tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target
GROUP BY qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev
PIVOT tblRLBtargets.ShortName;

"all records from target table"

TRANSFORM Count(qryRLBrun_results_forreport.RunDate) AS CountOfRunDate
SELECT qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev, Count(qryRLBrun_results_forreport.RunDate) AS [Total Of RunDate]
FROM tblRLBtargets LEFT JOIN qryRLBrun_results_forreport ON tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target
GROUP BY qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev
PIVOT tblRLBtargets.ShortName;

"all records from sample table"

TRANSFORM Count(qryRLBrun_results_forreport.RunDate) AS CountOfRunDate
SELECT qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev, Count(qryRLBrun_results_forreport.RunDate) AS [Total Of RunDate]
FROM tblRLBtargets RIGHT JOIN qryRLBrun_results_forreport ON tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target
GROUP BY qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev
PIVOT tblRLBtargets.ShortName;

no one else seems to ever wanted to do this, unless i'm using the completely wrong keywords in my searches....

i've tried placing two instances of the target table in the query, and had jjoin properties set appropriately, but they cascade rather than for all records (i.e., targets > samples > targets does NOT return all targets for all samples for all targets)

and i have no idea how to manipulate this in SQL view... never worked with pivottables before (at least, the first time i realised i was using a pivot table each time i did a cross tab was when i just pasted that code in a second ago....)

anyone know how to overcome this issue?

(edit: snapshot of design view is attached, if that's any help to anyone)

(edit: i just noticed all the SQLs there have INNER, LEFT or RIGHT joins, i'm going to play around with changing them to OUTER joins, and see if that does anything.... seems hopeful.... wish me luck!)

 

[wiklendt]

damn.... OUTER JOIN gives me a "syntax error in TRANSFORM statement"... (though i simply changed the word INNER for OUTER... i didn't suppose it'd be that easy, but i thought i'd try!)

(edit: and "LEFT OUTER JOIN" or "RIGHT OUTER JOIN" seem to return the same as LEFT JOIN and RIGHT JOIN)

 

[boblarson]

You actually will want a UNION query instead of a joined query (as Access does not have the capability to give you a FULL JOIN which is what you would need).

 

[wiklendt]

Hi bob, ha ha - just found this link as you werwe writing that.
http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join

thanks, will attempt to get this working and post back with (hopefully successful) results..

 

[wiklendt]

ok, i get some kind of return (as opposed to error) when i use this code for just the UNION part (thought i'd start with the basics:

([COLOR=RoyalBlue]SELECT[/COLOR]
qryRLBrun_results_forreport.Name, 
qryRLBrun_results_forreport.PlateAbbrev, 
qryRLBrun_results_forreport.RunDate 
[COLOR=RoyalBlue]FROM [/COLOR]tblRLBtargets 
[COLOR=RoyalBlue]LEFT JOIN[/COLOR] qryRLBrun_results_forreport 
[COLOR=RoyalBlue]ON [/COLOR]tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target 
[COLOR=Red][B]UNION[/B][/COLOR]
[COLOR=RoyalBlue]SELECT[/COLOR]
qryRLBrun_results_forreport.Name, 
qryRLBrun_results_forreport.PlateAbbrev, 
qryRLBrun_results_forreport.RunDate 
[COLOR=RoyalBlue]FROM [/COLOR]tblRLBtargets 
[COLOR=RoyalBlue]RIGHT JOIN [/COLOR]qryRLBrun_results_forreport 
[COLOR=RoyalBlue]ON [/COLOR]tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target 
[COLOR=RoyalBlue]WHERE [/COLOR]qryRLBrun_results_forreport.RLB_Target IS NULL)

but i still have to check if it returns expected values... (it only displays Name, PlateAbbrev, and RunDate: no targets, so i'm not sure if this is just a pre-emptive to crosstab?)

but when i try to wrap that UNION statement with a TRANSFORM statement, i get the "this operation is not allow in subqueries".

[COLOR=RoyalBlue]TRANSFORM [/COLOR]([COLOR=RoyalBlue]SELECT[/COLOR]...as above) [COLOR=RoyalBlue]GROUP BY[/COLOR] qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev
[COLOR=RoyalBlue]PIVOT [/COLOR]tblRLBtargets.ShortName;

i left off the

Count(qryRLBrun_results_forreport.RunDate) [COLOR=RoyalBlue]AS [/COLOR]CountOfRunDate

between the "TRANSFORM" and "(SELECT" bits b/c it was giving me an error: "syntax error in TRANSFORM statement".

 

[wiklendt]

perhaps i need to make the union query, and then a new crosstab based on that union query?... so long as all the results are displayed in ONE of these! i'll try that first...

(edit: hmmm... that was a stupid idea, it works just like my original crosstab.... with one or the other negatives inclusive but not both)

 

[wiklendt]

ok, i THINK this is good code for a UNION query inside a TRANSFORM (crosstab query), HOWEVER... when i try to run this query, i get the error "this operation is not allow in subqueries".

(edit: oops, better past in the code! when i try to run, i get the error, and then it highlights my "(SELECT... ...)" part)

[COLOR=RoyalBlue]TRANSFORM[/COLOR] 
[COLOR=Red][B]([/B][/COLOR][COLOR=RoyalBlue]SELECT [/COLOR]
qryRLBrun_results_forreport.Name, 
qryRLBrun_results_forreport.RLB_Target, 
qryRLBrun_results_forreport.PlateAbbrev, 
qryRLBrun_results_forreport.RunDate 
[COLOR=RoyalBlue]FROM [/COLOR]tblRLBtargets 
[COLOR=RoyalBlue]LEFT JOIN[/COLOR] qryRLBrun_results_forreport 
[COLOR=RoyalBlue]ON [/COLOR]tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target 
[COLOR=Red]UNION [/COLOR][COLOR=RoyalBlue]
SELECT 
[/COLOR]qryRLBrun_results_forreport.Name, 
qryRLBrun_results_forreport.RLB_Target, 
qryRLBrun_results_forreport.PlateAbbrev, 
qryRLBrun_results_forreport.RunDate 
[COLOR=RoyalBlue]FROM [/COLOR]tblRLBtargets [COLOR=RoyalBlue]
RIGHT JOIN [/COLOR]qryRLBrun_results_forreport 
[COLOR=RoyalBlue]ON[/COLOR] tblRLBtargets.TargetID = qryRLBrun_results_forreport.RLB_Target [COLOR=RoyalBlue]
WHERE [/COLOR]qryRLBrun_results_forreport.RLB_Target[COLOR=RoyalBlue] IS NULL[/COLOR][COLOR=Red][B]) [/B][/COLOR]
[COLOR=RoyalBlue]GROUP BY [/COLOR]qryRLBrun_results_forreport.Name, qryRLBrun_results_forreport.PlateAbbrev 
[COLOR=RoyalBlue]PIVOT [/COLOR]tblRLBtargets.ShortName;

it would seem that crosstabs are only possible with a simple select-type of query.... i have tried to create two different crosstabs (one showing all targets, the other showing all samples) and then joining them with a UNION, but they both have slightly different fields (one doesn't have the 'negative' fields of the other), so i can't really join them either, can i...?

does anyone know if it is possible to do a UNION wrapped in a TRANSFORM in any other way?