Query Report

[Guest]

I am basically trying to transform some data in a table for a report.

Table - tMAINTENANCE

Fields
ST_SEC_ID, LOCATION, TREAT, YEAR
Data
“122â€, "MAIN STâ€, â€CRâ€, “2000â€
“123â€, “PERRY DRâ€, “CRâ€, “2001â€
“124â€, “WERCH DRâ€, “CRâ€, “2003â€

I create several queries:
Query tMaintenance for YEAR = 2000
Date returned: “122â€, “MAIN STâ€, “CRâ€, “2000â€
Similar queries for 2001, 2002, 2003

Then a query for all the records in tMaintenace and with JOINS to each of
the above queries. This query ends up with the data in this form:

ST_SEC_ID, LOCATION, [2000], [2001], [2002], [2003]
“122â€, “MAIN STâ€, CR,â€â€,â€â€,â€â€
“123â€, “PERRY DRâ€, “â€, CR,â€â€,â€â€
“124â€, “WERCH DRâ€, “â€, “â€, “â€, CR

I end up with too many queries when I take it out to 2020. Can this be done
differently and easier?

 

[Duane Hookom]

Have you considered using a crosstab query? Your row headings would be
ST_SEC_ID and LOCATION. The YEAR field would be the column heading and
First(TREAT) as the value.

 

[Guest]

I have tried the crosstab query. The results of TREAT as the value is the
number "1". I would like it to be the value in the cell value such as "CR"
not the count. The results are

"993", "4", "", "", "1", "1", "", "1", "1"

It should be

"993", "4", "", "", "CR", "OL", "", "CS", "CS"


Here is the query:

TRANSFORM Count(tMaintenance.TREATMENT) AS CountOfTREATMENT
SELECT tMaintenance.ST_SEC_ID, Count(tMaintenance.TREATMENT) AS [Total Of
TREATMENT]
FROM tMaintenance
GROUP BY tMaintenance.ST_SEC_ID
PIVOT tMaintenance.YEAR;

Thanks the help.

Have you considered using a crosstab query? Your row headings would be
ST_SEC_ID and LOCATION. The YEAR field would be the column heading and
First(TREAT) as the value.

--
Duane Hookom
MS Access MVP

I am basically trying to transform some data in a table for a report.

Table - tMAINTENANCE

Fields
ST_SEC_ID, LOCATION, TREAT, YEAR
Data
"122", "MAIN ST", "CR", "2000"
"123", "PERRY DR", "CR", "2001"
"124", "WERCH DR", "CR", "2003"

I create several queries:
Query tMaintenance for YEAR = 2000
Date returned: "122", "MAIN ST", "CR", "2000"
Similar queries for 2001, 2002, 2003

Then a query for all the records in tMaintenace and with JOINS to each of
the above queries. This query ends up with the data in this form:

ST_SEC_ID, LOCATION, [2000], [2001], [2002], [2003]
"122", "MAIN ST", CR,"","",""
"123", "PERRY DR", "", CR,"",""
"124", "WERCH DR", "", "", "", CR

I end up with too many queries when I take it out to 2020. Can this be
done
differently and easier?

 

[Duane Hookom]

I suggested "First(TREAT)" and you used "Count(TREATMENT)".

--
Duane Hookom
MS Access MVP
--

I have tried the crosstab query. The results of TREAT as the value is the
number "1". I would like it to be the value in the cell value such as "CR"
not the count. The results are

"993", "4", "", "", "1", "1", "", "1", "1"

It should be

"993", "4", "", "", "CR", "OL", "", "CS", "CS"


Here is the query:

TRANSFORM Count(tMaintenance.TREATMENT) AS CountOfTREATMENT
SELECT tMaintenance.ST_SEC_ID, Count(tMaintenance.TREATMENT) AS [Total Of
TREATMENT]
FROM tMaintenance
GROUP BY tMaintenance.ST_SEC_ID
PIVOT tMaintenance.YEAR;

Thanks the help.

Have you considered using a crosstab query? Your row headings would be
ST_SEC_ID and LOCATION. The YEAR field would be the column heading and
First(TREAT) as the value.

--
Duane Hookom
MS Access MVP

I am basically trying to transform some data in a table for a report.

Table - tMAINTENANCE

Fields
ST_SEC_ID, LOCATION, TREAT, YEAR
Data
"122", "MAIN ST", "CR", "2000"
"123", "PERRY DR", "CR", "2001"
"124", "WERCH DR", "CR", "2003"

I create several queries:
Query tMaintenance for YEAR = 2000
Date returned: "122", "MAIN ST", "CR", "2000"
Similar queries for 2001, 2002, 2003

Then a query for all the records in tMaintenace and with JOINS to each
of
the above queries. This query ends up with the data in this form:

ST_SEC_ID, LOCATION, [2000], [2001], [2002], [2003]
"122", "MAIN ST", CR,"","",""
"123", "PERRY DR", "", CR,"",""
"124", "WERCH DR", "", "", "", CR

I end up with too many queries when I take it out to 2020. Can this be
done
differently and easier?

 

[Guest]

A little trial and error.....did the trick. Thanks for you help!

I suggested "First(TREAT)" and you used "Count(TREATMENT)".

--
Duane Hookom
MS Access MVP
--

I have tried the crosstab query. The results of TREAT as the value is the
number "1". I would like it to be the value in the cell value such as "CR"
not the count. The results are

"993", "4", "", "", "1", "1", "", "1", "1"

It should be

"993", "4", "", "", "CR", "OL", "", "CS", "CS"


Here is the query:

TRANSFORM Count(tMaintenance.TREATMENT) AS CountOfTREATMENT
SELECT tMaintenance.ST_SEC_ID, Count(tMaintenance.TREATMENT) AS [Total Of
TREATMENT]
FROM tMaintenance
GROUP BY tMaintenance.ST_SEC_ID
PIVOT tMaintenance.YEAR;

Thanks the help.

Have you considered using a crosstab query? Your row headings would be
ST_SEC_ID and LOCATION. The YEAR field would be the column heading and
First(TREAT) as the value.

--
Duane Hookom
MS Access MVP


I am basically trying to transform some data in a table for a report.

Table - tMAINTENANCE

Fields
ST_SEC_ID, LOCATION, TREAT, YEAR
Data
"122", "MAIN ST", "CR", "2000"
"123", "PERRY DR", "CR", "2001"
"124", "WERCH DR", "CR", "2003"

I create several queries:
Query tMaintenance for YEAR = 2000
Date returned: "122", "MAIN ST", "CR", "2000"
Similar queries for 2001, 2002, 2003

Then a query for all the records in tMaintenace and with JOINS to each
of
the above queries. This query ends up with the data in this form:

ST_SEC_ID, LOCATION, [2000], [2001], [2002], [2003]
"122", "MAIN ST", CR,"","",""
"123", "PERRY DR", "", CR,"",""
"124", "WERCH DR", "", "", "", CR

I end up with too many queries when I take it out to 2020. Can this be
done
differently and easier?