Cross Queries

[zminek21]

Hi,

I have a problem with cross-queries.

I need to show in table in the line all records of base "placowki" and in the column all records of base "koordynator".

So...when the value in table for exemplary line is null...i need to see there value 0....and not hide this line. It is possible?

In addition, I would like to add summary-rows for column and line.

In Annex I send an example.

Thanks!



Mariusz

[June7]

I think your options are:

1. build another query that uses the crosstab as data source:
SELECT *, Nz([Aleksandra Rudka],0) As RudkaCount FROM KwerendaKrzyz;

2. build a report and do the Nz calc in a textbox

3. do a fake crosstab http://www.datapigtechnologies.com/f.../crosstab.html
The expressions would be like: Nz(Sum(IIf([Koordynator="Aleksandra Rudka",1,0)),0)



To get sums, open query and click Totals from the ribbon. This opens a Totals row, pick the operation you want under each field.
Or build a report and have calcs in textboxes in report footer.

[zminek21]

Thank you

Ok, "disappearing columnt" is done....now rows. If we set the date criteria to >#2013-09-09# .... disappears eg rows "Krosno". Is it possible to show row "Krosno" with result 0 0 0 for each column? AAA2.zip

[June7]

Why are you summing the Koordynator field? This is the Koordynator ID.

You have relationships joined on the wrong fields. I am surprised Access allowed you to join a number field to a text field in the Relationship builder. PK/FK fields must be same data type. Joins should be between IDkoordynator to Koordyntor and IDplacowka to Placowka.

Join Type: Include ALL records from 'Wizyty' and only those from ...

SELECT Placówki.Placówka, Sum(IIf([Wizyty].[Koordynator]=4,1,0)) AS Przemek, Sum(IIf([Wizyty].[Koordynator]=2,1,0)) AS Asia, Sum(IIf([Wizyty].[Koordynator]=3,1,0)) AS Basia
FROM Wizyty RIGHT JOIN Placówki ON Wizyty.Placówka = Placówki.IDplacowka
WHERE (((Wizyty.Data)>#9/9/2013#))
GROUP BY Placówki.Placówka;

Krosno still will not show because it does not have any records that meet the date criteria. To assure every Placowka is always listed requires at least one record that meets the criteria. This can be accomplished if there is a dataset that associates each Placowka with every possible date in Wizyty. This dataset can be created with a cartesian query:

SELECT Placówki.IDplacowka, Wizyty.Data FROM Placówki, Wizyty;

Without a JOIN clause, every record from each table will join to every record of the other table. This query can be used in other query to assure a record for every Placowka.

SELECT Placówki.Placówka, Sum(IIf([Wizyty].[Koordynator]=4,1,0)) AS Przemek, Sum(IIf([Wizyty].[Koordynator]=2,1,0)) AS Asia, Sum(IIf([Wizyty].[Koordynator]=3,1,0)) AS Basia
FROM (Wizyty RIGHT JOIN AllDates ON (Wizyty.Placówka = AllDates.IDplacowka) AND (Wizyty.Data = AllDates.Data)) LEFT JOIN Placówki ON AllDates.IDplacowka = Placówki.IDplacowka
WHERE (((Wizyty.Data)>#9/9/2013#))
GROUP BY Placówki.Placówka;

Dates in Wizyty are saved as dd/mm/yyyy. This causes me issues when I try to sort/filter because I am in U.S. and follow mm/dd/yyyy structure which is the Access default. So my test of the above queries fails to return desired records. Review http://allenbrowne.com/ser-36.html

[zminek21]

It doesn't work...Krosno still is not showing....but I think that it is good way to solve this problem. I'll be working on it.