Can we create a table in report which include two fields in column? (1 Viewer)

[ThyTony]

Hello everyone,

I want to create a report table from the selected fields but need it to be in the top of each other ( vertically). I'm not sure what's it's actually called but it's like merging cells in excel. I have attached the table image which was created in excel. The table shows the list of some items whose quantities bought between 2018 and 2021. Or is there any way to links MS Access data to the excel sheet where the table is created so that I can just update the table ( items name, quantities, year, total) from Access?

I really appreciate your comments and ideas.

 

[arnelgp]

create a Crosstab query first.

 

[ThyTony]

create a Crosstab query first.

So after I created a crosstab table, I can not use it in the report Record Source in my Report because there's no "Existing Fields" to add to the report. Any Idea how I can create report?

 

[ThyTony]

So after I created a crosstab table, I can not use it in the report Record Source in my Report because there's no "Existing Fields" to add to the report. Any Idea how I can create report?

I see the problem. I'm using the data entry from a form. I need to remove the criteria from the old query and put in crosstab query.

 

[ThyTony]

create a Crosstab query first.

Dear arnelgp
I have created the crosstab query which indicate the project progress of one selected WorkUnit from a form VS the ProjectYear (which I converted to column heading number and count the number of projects (ProjectID) as attached for your reference. My problem is I want to add 3 rows under "project progress"
1/ is called TotalAmount to show the sum of the "EstimatedPrice" based on the ProjectID count and the ProjectYear, for example there's total of 4 projects under "column heading 4" and the sum of Estimated Price under that column should show the total of the 4 projects as Total Amount.
2/ is called ContractAmount to show the sum of the "ProjecCost" as shown in the attachment Selected fields.
3/ is called NoContraractAmount which is the difference between the TotalAmount and ContractAmount.

I thought of doing 3 different crosstab and union the 3 but it turns out to be impossible. Please Help!

 

[SHANEMAC51]

Progress	typ	total	1	2	3	4	5	6	7
D-	1 EstimatedPrice	568151.61					568151.61
D-	2 ProjectCost	0					0
D-	3 EstimatedPrice-ProjectCost	568151.61					568151.61
E-:	1 EstimatedPrice	1281830					1281830
E-:	2 ProjectCost	942208.43					942208.43
E-:	3 EstimatedPrice-ProjectCost	339621.57					339621.57
F-	1 EstimatedPrice	9172020					9172020
F-	2 ProjectCost	960000					960000
F-	3 EstimatedPrice-ProjectCost	8212020					8212020
G-	1 EstimatedPrice	3779439.6				189453	3589986.6
G-	2 ProjectCost	3865069.44				156600	3708469.44
G-	3 EstimatedPrice-ProjectCost	-85629.84				32853	-118482.84
H-	1 EstimatedPrice	7086163.75			2265000	4593118.79	228044.96
H-	2 ProjectCost	7064338.96			2265000	4568794	230544.96
H-	3 EstimatedPrice-ProjectCost	21824.79			0	24324.79	-2500

 

[SHANEMAC51]

I thought of doing 3 different crosstab and union the 3 but it turns out to be impossible

Query TEMP00

SELECT w.Progress,"1 EstimatedPrice" as typ,column, nz(w.EstimatedPrice,0) AS sum1

FROM ProjectYearToIntQ AS w

union all

SELECT w.Progress,"2 ProjectCost", column,nz(w.ProjectCost,0) AS sum1

FROM ProjectYearToIntQ AS w

UNION ALL SELECT w.Progress,"3 EstimatedPrice-ProjectCost",column, nz(w.EstimatedPrice,0)-nz(w.ProjectCost,0) AS sum1

FROM ProjectYearToIntQ AS w

ORDER BY 1, 2

QUERY TEMP00_TOTAL

TRANSFORM sum(temp00.[sum1]) AS [Min-sum1]

SELECT temp00.[Progress], temp00.[typ], sum(temp00.[sum1]) AS total

FROM temp00

GROUP BY temp00.[Progress], temp00.[typ]

PIVOT temp00.column In (1,2,3,4,5,6,7);

 

[arnelgp]

i created temp table and queries (tbldummy, and object starting with "Z").
also created a sub to add those 3 rows.
see form1 and the code behind it.

 

[Pat Hartman]

Crosstabs can only pivot ONE column at a time. If you want to pivot 3, then you need three crosstabs. The final query is a regular join though, not a union. The problem is you need to be sure that all three queries produce the identical set of rows. If they don't you will have to do something to resolve that. Otherwise just join ct1 -->ct2-->ct3

 

[ThyTony]

i created temp table and queries (tbldummy, and object starting with "Z").
also created a sub to add those 3 rows.
see form1 and the code behind it.

Hi arnelgp,
Following your example, I could create extra row "Total project" to the union query . I tried to converted the Amt to currency data type by separating the original query into two queries but no success. I want to display the data in the report so that the upper part of report shows the project number and the lower part shows the money. I also tried with subreport but there's no relation between the two crosstabs I had created. Do you know what I should do? pls advise.

 

[arnelgp]

you need the "normal" rows of the query in detail section.
the "3 additional rows" in report footer.
then you can format them separately.