If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab/Transform with an join
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 |
#2
|
|||
|
|||
Crosstab/Transform with an join
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Richard" wrote: 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 |
#3
|
|||
|
|||
Crosstab/Transform with an join
"Richard" wrote: 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; |
#4
|
|||
|
|||
Crosstab/Transform with an join
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 |
#5
|
|||
|
|||
Crosstab/Transform with an join
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 "Gary Walter" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|