Crosstab with some 'empty' rows

[buggirl]

Hello again,

I have a fairly simple database using three tables:

1. Sample information (describes sample id, collection location etc)
2. Invert information (describes invert name, taxonomic information)
3. Invert counts (links sample information with invert information,
including abundances of inverts found in each sample)

I ran a crosstab query so that I now have a datasheet with sample id in the
first column and invert names across the top of the datasheet. The datasheet
is populated with the abundances of inverts found in the sample. It all looks
great.

However, the datasheet only includes those samples that have at least one
invertebrate I'm also interested in including those samples that did not
include any invertebrates.

How do I modify the query to include all samples, regardless of whether or
not there is a record in the "invert counts" table?

Do I need to include a dummy variable in the invert counts table so that all
samples have at least one bug in them (real or not)?

Thanks in advance for all of your advice,

buggirl

 

[John Spencer]

You may only need to change the join type in your query from an INNER
JOIN to a LEFT JOIN or a RIGHT JOIN.

In design view double-click on the join line between Sample and Invert
table and select the option for all in Sample and only in Invert table.

Now do the same thing for the join between Invert and Invert Counts
table All in Invert and only in InvertCounts table.

If this doesn't work the way you want, post the SQL statement of your
query (Menu View: SQL then copy and paste the text). That will help
people trouble shoot your query and offer suggestions on how to change it.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[buggirl]

Hi John,

Thanks for your response to my message last week. I though you had the
answer! Unfortunately, trying to correct the joins gives me the "ambiguous
outer joins" message.

I've pasted the SQL below. To clarify, the samples are actually saucers and
there are sixteen saucers on a unit. [As a biologist, I think of the saucers
nested within the units.]

Therefore, the sample table I originally described is actually two tables
(unit descriptions and saucer descriptions).

Thanks again for your wisdom,

buggirl

TRANSFORM Sum([invert counts].count) AS SumOfcount
SELECT [saucer descriptions].[sample id], [unit descriptions].[unit id],
[saucer descriptions].treatment
FROM [unit descriptions] INNER JOIN ([invert descriptions] INNER JOIN
([saucer descriptions] INNER JOIN [invert counts] ON [saucer
descriptions].[saucer id] = [invert counts].[saucer id]) ON [invert
descriptions].[invert id] = [invert counts].[invert id]) ON [unit
descriptions].[unit id] = [saucer descriptions].[unit id]
GROUP BY [saucer descriptions].[sample id], [unit descriptions].[unit id],
[saucer descriptions].treatment
PIVOT [invert descriptions].[invert name];