Query Results of same ID/PK to one line

[wetsnow13]

Hi and thanks for reading this. If anything is unclear I'd be happy to explain further.



I have tblMain - It has a PK and several columns of data.

tblHoriz has columns containing (FK to tblMain), X, Y, (FK to tblHorizDatum)

I have 1-4 used for FK to tblHorizDatum

I would like to query all of table main and each X and Y for tblHoriz renaming the header to coincide with the Datum and have it all appear on one record row.

I can't seem to get it all to pop onto one record row. If there are 3 data and thus 3 instances of the (FK to tblMain) in tblHoriz I get 3 rows

[orange]

See if this applies to your issue.

[wetsnow13]

I am hoping for multiple columns, not a concatenation into one single column - which is what I think that does.

I broke each of the data out into its own table and then queried using tblMain and each datum table and it returned only one row per record.

Maybe I have a join problem in my initial question?

[June7]

Show sample of actual data and desired output.

A CROSSTAB query will not serve?

[wetsnow13]

DatabaseExample.mdb Please see the attached .mdb

If I break the tblHorizCoord down into 4 tables then I can get only one record per row. This is not shown in the attachment

[June7]

I see a conventional CROSSTAB cannot act directly on tblHorizDatum.

Make qryHoriz an aggregate (GROUP BY) Totals query. There was an error in the X Datum 4 expression. Will there only be the 4 HDatum values?

SELECT tblHorizCoord.HorizCoordID, Sum(IIf([tblHorizCoord].[Hdatum]=1,[tblHorizCoord].[X],Null)) AS [X Datum 1], Sum(IIf([tblHorizCoord].[Hdatum]=1,[tblHorizCoord].[y],Null)) AS [Y Datum 1], Sum(IIf([tblHorizCoord].[Hdatum]=2,[tblHorizCoord].[X],Null)) AS [X Datum 2], Sum(IIf([tblHorizCoord].[Hdatum]=2,[tblHorizCoord].[y],Null)) AS [Y Datum 2], Sum(IIf([tblHorizCoord].[Hdatum]=3,[tblHorizCoord].[X],Null)) AS [X Datum 3], Sum(IIf([tblHorizCoord].[Hdatum]=3,[tblHorizCoord].[y],Null)) AS [Y Datum 3], Sum(IIf([tblHorizCoord].[Hdatum]=4,[tblHorizCoord].[X],Null)) AS [X Datum 4], Sum(IIf([tblHorizCoord].[Hdatum]=4,[tblHorizCoord].[y],Null)) AS [Y Datum 4]
FROM tblHorizCoord
GROUP BY tblHorizCoord.HorizCoordID
ORDER BY tblHorizCoord.HorizCoordID;

Here is another using UNION and CROSSTAB:

TRANSFORM First(Query1.[DAT]) AS FirstOfDAT
SELECT Query1.[HorizCoordID]
FROM (SELECT HorizCoordID, "X" & " " & tblHorizDatum.Hdatum AS HDAT, X AS DAT FROM tblHorizDatum RIGHT JOIN tblHorizCoord ON tblHorizDatum.HDID = tblHorizCoord.HDatum
UNION SELECT HorizCoordID, "Y" & " " & tblHorizDatum.Hdatum, Y FROM tblHorizDatum RIGHT JOIN tblHorizCoord ON tblHorizDatum.HDID = tblHorizCoord.HDatum) AS Query1
GROUP BY Query1.[HorizCoordID]
PIVOT Query1.[HDAT];

[wetsnow13]

Thank you very much for the help June7 and orange! I went with the totals query. I thought the totals was simply used for counting and gathering min/max information. From what I can gather here the GROUP BY throws it all into one record. The SUM( part really boggled my mind, but you are using the mathmatical expression to facilitate using the GROUP BY and adding what you want or NULL to nothing equates to what you want or NULL? (that is one of those statement questions).

I added to the query, using GOURP BY for the total, the memo field that I added returned a weird single character that triggers my western brain to say "what is this chinese thing". some quick searching says to delete and re-add as the "pointer" is going to the wrong spot. Any thoughts?

[June7]

Sorry, I don't understand the new question. Delete and re-add what? The field to query?