Crosstab Dummy

[Thompyt]

Really newbie question here. I can't get my mind around it.

How do I get the cross tab to recognize only the first UniqueName where Parent is equal?





Crosstab.zip

[isladogs]

If I've understood you correctly you want to see this.



If so, you don't want a crosstab - use an aggregate query instead

SELECT Mounts.UniqueName, First(Mounts.Unique) AS FirstOfUnique, Mounts.ParentFROM Mounts
GROUP BY Mounts.UniqueName, Mounts.Parent;

However, bear in mind that the first record may not be what you were expecting - see the 2nd record above
An alternative to consider is to use Min instead of First

SELECT Mounts.UniqueName, Min(Mounts.Unique) AS MinOfUnique, Mounts.ParentFROM Mounts
GROUP BY Mounts.UniqueName, Mounts.Parent;

which gives

[Thompyt]

ridders52,
That works for me so far and is simpler. Min and First Seem to work the same for me. How do I weed out the 117's that only have 1 radio from going into first/min or last/max, but not both?

Single 117's meet both criteria technically.

Thanks!!

[isladogs]

ridders52,
That works for me so far and is simpler. Min and First Seem to work the same for me. How do I weed out the 117's that only have 1 radio from going into first/min or last/max, but not both?

Single 117's meet both criteria technically.

Thanks!!

Not sure what you mean?
Perhaps use unique records in your query - SELECT DISTINCT

[Thompyt]

Crosstab.zip

I made 2 Queries in the attached db and deleted 1 117 out from the Data table.

If you run the queries you will notice the 117 with unique = 12319 showing up in both. I tried first/Min and last/Max with the same outcome. How would I remove 12319 from 1 of the queries?

Other than putting "<>12319" in the unique Criteria.


Kind of like IIF([LastMounts].[MinOfUnique] <> [FirstMounts].[MixOfUnique], [LastMounts].[MinOfUnique], "")

I think I found it, but is there a more eloquent way?

SELECT LMounts.UniqueName, IIf([MinOfUnique]=[MaxOfUnique],"",[MaxofUnique]) AS [Second], LMounts.ParentFROM FirstMounts INNER JOIN LMounts ON (FirstMounts.Parent = LMounts.Parent) AND (FirstMounts.UniqueName = LMounts.UniqueName)
WHERE (((IIf([MinOfUnique]=[MaxOfUnique],"",[MaxofUnique])) Is Not Null));

Thanks

[isladogs]

Sorry but I'm not at all clear what you're trying to do but your suggested query wouldn't compile.
Corrected version below:

SELECT LastMounts.UniqueName, IIf([MinOfUnique]=[MaxOfUnique],'',[MaxofUnique]) AS [Second], LastMounts.Parent FROM FirstMounts INNER JOIN LastMounts ON (FirstMounts.Parent = LastMounts.Parent) AND (FirstMounts.UniqueName = LastMounts.UniqueName)
WHERE (((IIf([MinOfUnique]=[MaxOfUnique],'',[MaxofUnique])) Is Not Null));

Which gives this result



Is that what you want?

[Thompyt]

Yes that's what I was going for, My version for text, yours for #'s
I made another query with the same fields and used this:

IIf([MinOfUnique_id]=[MaxOfUnique_id],"",[MinofUnique_id])

Thanks