Crosstab/Transform with an join

[Richard]

I got an crosstab query that shows all stores based with how many items a
store bought based on a where artikel id = 'ddds'
If an store didnt bought any of the article it wont show up because of the
where clause. How kan I overcome this problem?
If read something about an join but I cant make any sense of it.

Can someone help me on this?

code:
TRANSFORM Sum(dbo_SalesQuery.aantbest) AS SumOfaantbest
SELECT dbo_SalesQuery.Klant, Sum(dbo_SalesQuery.aantbest) AS Totaal
FROM dbo_SalesQuery
WHERE (( dbo_SalesQuery.Artikel Like "%124507%" )
GROUP BY dbo_SalesQuery.Klant PIVOT dbo_SalesQuery.Artikel;

Thanks in advance, Richard

 

[Jerry Whittle]

Open the dbo_SalesQuery query in SQL View. Does is say Inner Join between the
two tables? If so, try changing it to Left Join. If that doesn't work, try
Right Join.

 

[Gary Walter]

I got an crosstab query that shows all stores based with how many items a
store bought based on a where artikel id = 'ddds'
If an store didnt bought any of the article it wont show up because of the
where clause. How kan I overcome this problem?
If read something about an join but I cant make any sense of it.

Can someone help me on this?

code:
TRANSFORM Sum(dbo_SalesQuery.aantbest) AS SumOfaantbest
SELECT dbo_SalesQuery.Klant, Sum(dbo_SalesQuery.aantbest) AS Totaal
FROM dbo_SalesQuery
WHERE (( dbo_SalesQuery.Artikel Like "%124507%" )
GROUP BY dbo_SalesQuery.Klant PIVOT dbo_SalesQuery.Artikel;

Hi Richard,

In addition to Jerry's sage help,
I might "divide-and-conquer."

I would suggest 2 saved queries (views?)...

qryAllStores
(I assume "Klant" means store?)

SELECT DISTINCT Klant FROM dbo_SalesQuery;

qryFilterArtikel

SELECT
Klant,
Artikel,
aantbest
FROM
dbo_SalesQuery
WHERE
Artikel Like "%124507%";

{you would need to rewrite qryFilterArtikel
when you want to change filter}

the crosstab would then be:

TRANSFORM Nz(Sum(F.aantbest), 0) AS SumOfaantbest
SELECT
A.Klant,
Sum(F.aantbest) AS Totaal
FROM
qryAllStores AS A
LEFT JOIN
qryFilterArtikel AS F
ON
A.Klant = F.Klant
GROUP BY
A.Klant
PIVOT
F.Artikel;

that might be one way (of many) to show all stores
in your row heading but only sums for specific Artikels
in your PIVOT columns.

one other way (maybe) would use query sql's in FROM clause
but crosstabs have a tendency to choke on some subqueries...

TRANSFORM Nz(Sum(F.aantbest), 0) AS SumOfaantbest
SELECT
A.Klant,
Sum(F.aantbest) AS Totaal
FROM
(SELECT DISTINCT d.Klant FROM dbo_SalesQuery AS d) AS A
LEFT JOIN
qryFilterArtikel AS F
ON
A.Klant = F.Klant
GROUP BY
A.Klant
PIVOT
F.Artikel;

or (doubtful)

TRANSFORM Nz(Sum(F.aantbest), 0) AS SumOfaantbest
SELECT
A.Klant,
Sum(F.aantbest) AS Totaal
FROM
(
SELECT
DISTINCT d.Klant
FROM
dbo_SalesQuery AS d
) AS A
LEFT JOIN
(
SELECT
q.Klant,
q.Artikel,
q.aantbest
FROM
dbo_SalesQuery AS q
WHERE
q.Artikel Like "%124507%"
)
AS F
ON
A.Klant = F.Klant
GROUP BY
A.Klant
PIVOT
F.Artikel;

 

[Gary Walter]

Hi Richard,

I guess I forgot to ask...

is this an Access project or .mdb?

how are you using "%" wildcard if
it is .mdb?

how could you be designing crosstab
if it is a project?

If it is am .mdb, I really would make
2 tables from the "divide-and-conquer"
queries.

then, when I needed the crosstab,
I would empty the tables, append
with latest data

and use the local tables in the crosstab.

with that little coding, your crosstab
will actually be zippier (if that is a word).

good luck,

gary

 

[Richard]

Hi Gary,

somseone before me made an project in ASP with Access. in Access there is an
linked table to MS SQL.
He did this because of the easy way Access allowed crosstabbing.

I actually did it like you said, took 2 query's but did it in MS SQL. The
idea about 2 query's worked out just fine

thx a lot.

Richard