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

ThyTony

New member
Local time
Tomorrow, 05:18
Joined
Feb 14, 2022
Messages
9
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.
 

Attachments

  • IMG_20220301_141959.jpg
    IMG_20220301_141959.jpg
    442.9 KB · Views: 203

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
16,826
create a Crosstab query first.
 

ThyTony

New member
Local time
Tomorrow, 05:18
Joined
Feb 14, 2022
Messages
9
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

New member
Local time
Tomorrow, 05:18
Joined
Feb 14, 2022
Messages
9
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

New member
Local time
Tomorrow, 05:18
Joined
Feb 14, 2022
Messages
9
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! :(
 

Attachments

  • CrossTab_Progress_Year.PNG
    CrossTab_Progress_Year.PNG
    33.9 KB · Views: 160
  • SelectedFields.PNG
    SelectedFields.PNG
    66.4 KB · Views: 176

SHANEMAC51

Active member
Local time
Tomorrow, 01:18
Joined
Jan 28, 2022
Messages
294

Progresstyptotal1234567
D-1 EstimatedPrice568151.61568151.61
D-2 ProjectCost00
D-3 EstimatedPrice-ProjectCost568151.61568151.61
E-:1 EstimatedPrice12818301281830
E-:2 ProjectCost942208.43942208.43
E-:3 EstimatedPrice-ProjectCost339621.57339621.57
F-1 EstimatedPrice91720209172020
F-2 ProjectCost960000960000
F-3 EstimatedPrice-ProjectCost82120208212020
G-1 EstimatedPrice3779439.61894533589986.6
G-2 ProjectCost3865069.441566003708469.44
G-3 EstimatedPrice-ProjectCost-85629.8432853-118482.84
H-1 EstimatedPrice7086163.7522650004593118.79228044.96
H-2 ProjectCost7064338.9622650004568794230544.96
H-3 EstimatedPrice-ProjectCost21824.79024324.79-2500
 
Last edited:

SHANEMAC51

Active member
Local time
Tomorrow, 01:18
Joined
Jan 28, 2022
Messages
294
I thought of doing 3 different crosstab and union the 3 but it turns out to be impossible




Query TEMP00


Code:
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

Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
16,826
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.
 

Attachments

  • ProjectCrosstab.accdb
    500 KB · Views: 111

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:18
Joined
Feb 19, 2002
Messages
36,894
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

New member
Local time
Tomorrow, 05:18
Joined
Feb 14, 2022
Messages
9
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.
 

Attachments

  • SortedFields.PNG
    SortedFields.PNG
    38.9 KB · Views: 173

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:18
Joined
May 7, 2009
Messages
16,826
you need the "normal" rows of the query in detail section.
the "3 additional rows" in report footer.
then you can format them separately.
 

Users who are viewing this thread

Top Bottom