Show all fields on a report

[Legends77]

I made a query that sums up by group what happened each month. The problem is that when you look at the report since not all groups are in each month, so looking at the report can be "confusing". I went ahead and made a table that contains all the groups but no matter how / where I like the subgroup table, I am not able to solve the problem. Someone suggested using a crosstab query but not having any luck and the time crunch i am in does not give me a great deal of time to "read up". Any and all suggestions would be welcomed.

Thanks in advance.

 

[dhookom]

Can you provide any information on your:
- table structures
- Report record source SQL
- filtering/criteria in the record source

I expect you need a LEFT or RIGHT JOIN in your record source.

Duane
Hook'D on Access
MS Access MVP

 

[Legends77]

Currently all the data is in one query. This query is actually as query of a query of a query to get to the point of combining all the data I needed.
SQL:
SELECT CPGroupSummary1.SubGroup, CPGroupSummary1.FiscalYear, CPGroupSummary1.MonthNumber, CPGroupSummary1.MonthID, CPGroupSummary1.BroadGroup, CPGroupSummary1.[SumOfGross SQFT], CPGroupSummary1.[SumOfReject SQFT], IIf([SumOfGross SQFT]=0,0,[SumOfReject SQFT]/[SumOfGross SQFT]) AS RejectPercentSQFT, CPGroupSummary1.[SumOfNet SQFT], CPGroupSummary1.[SumOfGross STDF], CPGroupSummary1.[SumOfReject STDF], CPGroupSummary1.[SumOfNet STDF], IIf([SumOfGross STDF]=0,0,[SumOfReject STDF]/[SumOfGross STDF]) AS RejectPercentSTDF, CPGroupSummary1.SumOfNetRuntime, CPGroupSummary1.SumOfUnschedDelay, CPGroupSummary1.SumOfSchedDelay, CPGroupSummary1.SumOfNonAvail, IIf([SumOfGross SQFT]=0,0,[SumOfNet SQFT]/[SumOfDesignSQFT]) AS MAD, [SumOfNetRuntime]+[SumOfUnschedDelay]+[SumOfSchedDelay] AS TotalTime, IIf([SumOfUnschedDelay]=0,0,[SumOfUnschedDelay]/[TotalTime]) AS UnschedPercent, IIf([SumOfSchedDelay]=0,0,[SumOfSchedDelay]/[TotalTime]) AS SchedPercent, IIf(([SumOfUnschedDelay]+[SumOfSchedDelay])=0,0,([SumOfUnschedDelay]+[SumOfSchedDelay])/[TotalTime]) AS TotalDelayPercent, CPGroupSummary1.SumOfDesignSQFT, CPGroupSummary1.PickSize, IIf([SubGroup]="Scheduled Time",0,((([SumOfGross SQFT]/[SumOfNettimeM])/60)*1)/[PickSize]) AS PPM, CPGroupSummary1.SumOfNettimeM
FROM CPGroupSummary1
WHERE (((CPGroupSummary1.MonthNumber)=1));

Now, I have created a table that does include all the subgroups but think you are right, it's probably the join types between the query and the table.

Here is the sql if I include the table:
SELECT Subgroup.SubGroup, CPGroupSummary1.FiscalYear, CPGroupSummary1.MonthNumber, CPGroupSummary1.MonthID, CPGroupSummary1.BroadGroup, CPGroupSummary1.[SumOfGross SQFT], CPGroupSummary1.[SumOfReject SQFT], IIf([SumOfGross SQFT]=0,0,[SumOfReject SQFT]/[SumOfGross SQFT]) AS RejectPercentSQFT, CPGroupSummary1.[SumOfNet SQFT], CPGroupSummary1.[SumOfGross STDF], CPGroupSummary1.[SumOfReject STDF], CPGroupSummary1.[SumOfNet STDF], IIf([SumOfGross STDF]=0,0,[SumOfReject STDF]/[SumOfGross STDF]) AS RejectPercentSTDF, CPGroupSummary1.SumOfNetRuntime, CPGroupSummary1.SumOfUnschedDelay, CPGroupSummary1.SumOfSchedDelay, CPGroupSummary1.SumOfNonAvail, IIf([SumOfGross SQFT]=0,0,[SumOfNet SQFT]/[SumOfDesignSQFT]) AS MAD, [SumOfNetRuntime]+[SumOfUnschedDelay]+[SumOfSchedDelay] AS TotalTime, IIf([SumOfUnschedDelay]=0,0,[SumOfUnschedDelay]/[TotalTime]) AS UnschedPercent, IIf([SumOfSchedDelay]=0,0,[SumOfSchedDelay]/[TotalTime]) AS SchedPercent, IIf(([SumOfUnschedDelay]+[SumOfSchedDelay])=0,0,([SumOfUnschedDelay]+[SumOfSchedDelay])/[TotalTime]) AS TotalDelayPercent, CPGroupSummary1.SumOfDesignSQFT, CPGroupSummary1.PickSize, IIf([CPGroupsummary1].[subgroup]="Scheduled Time",0,((([SumOfGross SQFT]/[SumOfNettimeM])/60)*1)/[PickSize]) AS PPM, CPGroupSummary1.SumOfNettimeM
FROM CPGroupSummary1 RIGHT JOIN Subgroup ON CPGroupSummary1.SubGroup = Subgroup.SubGroup;

 

[dhookom]

I'm confused because you seem to use "group" and "subgroup" interchangeably. If you want all the groups then you should have a table of all groups which get joined to your query.

Aren't you seeing all of teh subgroup values in the second query in datasheet view?

What happened to MonthNumber = 1 in the WHERE CLAUSE? If you apply a filter on left side of the join, it won't display all the records from the right side since the criteria field will be Null (not 1).

Duane
Hook'D on Access
MS Access MVP

 

[Legends77]

my apologies. The condition was taken off when I added the "subgroup" table. Yes, I am seeing all the subgroups in the summary2 query but only if they have values for the month. I.E. subgroup 7.25-EU only is in Feb but no other month displays 7.25-EU. Ultimately I would like each month to display all groups and only fill in what is required for each month. This will make comparing month to month in the report much easier. Right now when I look at a month, I only see the subgroups used in that particular month.

 

[dhookom]

If you want to see all months and subgroups then create a query with all months and subgroups.

SELECT DISTINCT Subgroup.SubGroup, CPGroupSummary1.MonthNumber
FROM SubGroup, CPGroupSummary;

Then use this in your second query with RIGHT JOINS from both the MonthNumber and SubGroup.

Duane
Hook'D on Access
MS Access MVP

 

[Legends77]

Okay, the subgroup table has been updated to include not only the monthno but also the year to allow for future growth. The problem is that if I do a RIGHT JOIN of the below, no change:
SELECT Subgroup.SubGroup, Subgroup.Monthno, Subgroup.FY, CPGroupSummary1.SubGroup, CPGroupSummary1.FiscalYear, CPGroupSummary1.MonthNumber, CPGroupSummary1.MonthID, CPGroupSummary1.BroadGroup, CPGroupSummary1.[SumOfGross SQFT], CPGroupSummary1.[SumOfReject SQFT], IIf([SumOfGross SQFT]=0,0,[SumOfReject SQFT]/[SumOfGross SQFT]) AS RejectPercentSQFT, CPGroupSummary1.[SumOfNet SQFT], CPGroupSummary1.[SumOfGross STDF], CPGroupSummary1.[SumOfReject STDF], CPGroupSummary1.[SumOfNet STDF], IIf([SumOfGross STDF]=0,0,[SumOfReject STDF]/[SumOfGross STDF]) AS RejectPercentSTDF, CPGroupSummary1.SumOfNetRuntime, CPGroupSummary1.SumOfUnschedDelay, CPGroupSummary1.SumOfSchedDelay, CPGroupSummary1.SumOfNonAvail, IIf([SumOfGross SQFT]=0,0,[SumOfNet SQFT]/[SumOfDesignSQFT]) AS MAD, [SumOfNetRuntime]+[SumOfUnschedDelay]+[SumOfSchedDelay] AS TotalTime, IIf([SumOfUnschedDelay]=0,0,[SumOfUnschedDelay]/[TotalTime]) AS UnschedPercent, IIf([SumOfSchedDelay]=0,0,[SumOfSchedDelay]/[TotalTime]) AS SchedPercent, IIf(([SumOfUnschedDelay]+[SumOfSchedDelay])=0,0,([SumOfUnschedDelay]+[SumOfSchedDelay])/[TotalTime]) AS TotalDelayPercent, CPGroupSummary1.SumOfDesignSQFT, CPGroupSummary1.PickSize, IIf(CPGroupsummary1.subgroup="Scheduled Time",0,((([SumOfGross SQFT]/[SumOfNettimeM])/60)*1)/[PickSize]) AS PPM, CPGroupSummary1.SumOfNettimeM
FROM Subgroup RIGHT JOIN CPGroupSummary1 ON (Subgroup.SubGroup = CPGroupSummary1.SubGroup) AND (Subgroup.Monthno = CPGroupSummary1.MonthNumber) AND (Subgroup.FY = CPGroupSummary1.FiscalYear);

Now, if I do a LEFT JOIN (below) the missing subgroups appear but no month number appears therefore it does not group properly on the report:

SELECT Subgroup.SubGroup, Subgroup.Monthno, Subgroup.FY, CPGroupSummary1.SubGroup, CPGroupSummary1.FiscalYear, CPGroupSummary1.MonthNumber, CPGroupSummary1.MonthID, CPGroupSummary1.BroadGroup, CPGroupSummary1.[SumOfGross SQFT], CPGroupSummary1.[SumOfReject SQFT], IIf([SumOfGross SQFT]=0,0,[SumOfReject SQFT]/[SumOfGross SQFT]) AS RejectPercentSQFT, CPGroupSummary1.[SumOfNet SQFT], CPGroupSummary1.[SumOfGross STDF], CPGroupSummary1.[SumOfReject STDF], CPGroupSummary1.[SumOfNet STDF], IIf([SumOfGross STDF]=0,0,[SumOfReject STDF]/[SumOfGross STDF]) AS RejectPercentSTDF, CPGroupSummary1.SumOfNetRuntime, CPGroupSummary1.SumOfUnschedDelay, CPGroupSummary1.SumOfSchedDelay, CPGroupSummary1.SumOfNonAvail, IIf([SumOfGross SQFT]=0,0,[SumOfNet SQFT]/[SumOfDesignSQFT]) AS MAD, [SumOfNetRuntime]+[SumOfUnschedDelay]+[SumOfSchedDelay] AS TotalTime, IIf([SumOfUnschedDelay]=0,0,[SumOfUnschedDelay]/[TotalTime]) AS UnschedPercent, IIf([SumOfSchedDelay]=0,0,[SumOfSchedDelay]/[TotalTime]) AS SchedPercent, IIf(([SumOfUnschedDelay]+[SumOfSchedDelay])=0,0,([SumOfUnschedDelay]+[SumOfSchedDelay])/[TotalTime]) AS TotalDelayPercent, CPGroupSummary1.SumOfDesignSQFT, CPGroupSummary1.PickSize, IIf(CPGroupsummary1.subgroup="Scheduled Time",0,((([SumOfGross SQFT]/[SumOfNettimeM])/60)*1)/[PickSize]) AS PPM, CPGroupSummary1.SumOfNettimeM
FROM Subgroup LEFT JOIN CPGroupSummary1 ON (Subgroup.SubGroup = CPGroupSummary1.SubGroup) AND (Subgroup.Monthno = CPGroupSummary1.MonthNumber) AND (Subgroup.FY = CPGroupSummary1.FiscalYear);

Any ideas or thoughts as to where I went wrong?

 

[dhookom]

I would think the second query would show every possible combination of Subgroup, MonthNo, and FY. Are you sure every combination has a record in your table Subgroup?

Duane
Hook'D on Access
MS Access MVP

 

[Legends77]

Here is the subgroup table and I don't see any months missing...
SubGroup Monthno FY
A 1 2010
A 2 2010
A 3 2010
A 4 2010
A 5 2010
A 6 2010
A 7 2010
A 8 2010
A 9 2010
A 10 2010
A 11 2010
A 12 2010
B 1 2010
B 2 2010
B 3 2010
B 4 2010
B 5 2010
B 6 2010
B 7 2010
B 8 2010
B 9 2010
B 10 2010
B 11 2010
B 12 2010
C 1 2010
C 2 2010
C 3 2010
C 4 2010
C 5 2010
C 6 2010
C 7 2010
C 8 2010
C 9 2010
C 10 2010
C 11 2010
C 12 2010
D 1 2010
D 2 2010
D 3 2010
D 4 2010
D 5 2010
D 6 2010
D 7 2010
D 8 2010
D 9 2010
D 10 2010
D 11 2010
D 12 2010
E 1 2010
E 2 2010
E 3 2010
E 4 2010
E 5 2010
E 6 2010
E 7 2010
E 8 2010
E 9 2010
E 10 2010
E 11 2010
E 12 2010
F 1 2010
F 2 2010
F 3 2010
F 4 2010
F 5 2010
F 6 2010
F 7 2010
F 8 2010
F 9 2010
F 10 2010
F 11 2010
F 12 2010
G 1 2010
G 2 2010
G 3 2010
G 4 2010
G 5 2010
G 6 2010
G 7 2010
G 8 2010
G 9 2010
G 10 2010
G 11 2010
G 12 2010
H 1 2010
H 2 2010
H 3 2010
H 4 2010
H 5 2010
H 6 2010
H 7 2010
H 8 2010
H 9 2010
H 10 2010
H 11 2010
H 12 2010
I 1 2010
I 2 2010
I 3 2010
I 4 2010
I 5 2010
I 6 2010
I 7 2010
I 8 2010
I 9 2010
I 10 2010
I 11 2010
I 12 2010
J 1 2010
J 2 2010
J 3 2010
J 4 2010
J 5 2010
J 6 2010
J 7 2010
J 8 2010
J 9 2010
J 10 2010
J 11 2010
J 12 2010
K 1 2010
K 2 2010
K 3 2010
K 4 2010
K 5 2010
K 6 2010
K 7 2010
K 8 2010
K 9 2010
K 10 2010
K 11 2010
K 12 2010
L 1 2010
L 2 2010
L 3 2010
L 4 2010
L 5 2010
L 6 2010
L 7 2010
L 8 2010
L 9 2010
L 10 2010
L 11 2010
L 12 2010
M 1 2010
M 2 2010
M 3 2010
M 4 2010
M 5 2010
M 6 2010
M 7 2010
M 8 2010
M 9 2010
M 10 2010
M 11 2010
M 12 2010
N 1 2010
N 2 2010
N 3 2010
N 4 2010
N 5 2010
N 6 2010
N 7 2010
N 8 2010
N 9 2010
N 10 2010
N 11 2010
N 12 2010
O 1 2010
O 2 2010
O 3 2010
O 4 2010
O 5 2010
O 6 2010
O 7 2010
O 8 2010
O 9 2010
O 10 2010
O 11 2010
O 12 2010
P 1 2010
P 2 2010
P 3 2010
P 4 2010
P 5 2010
P 6 2010
P 7 2010
P 8 2010
P 9 2010
P 10 2010
P 11 2010
P 12 2010
Q 1 2010
Q 2 2010
Q 3 2010
Q 4 2010
Q 5 2010
Q 6 2010
Q 7 2010
Q 8 2010
Q 9 2010
Q 10 2010
Q 11 2010
Q 12 2010
R 1 2010
R 2 2010
R 3 2010
R 4 2010
R 5 2010
R 6 2010
R 7 2010
R 8 2010
R 9 2010
R 10 2010
R 11 2010
R 12 2010
S 1 2010
S 2 2010
S 3 2010
S 4 2010
S 5 2010
S 6 2010
S 7 2010
S 8 2010
S 9 2010
S 10 2010
S 11 2010
S 12 2010
T 1 2010
T 2 2010
T 3 2010
T 4 2010
T 5 2010
T 6 2010
T 7 2010
T 8 2010
T 9 2010
T 10 2010
T 11 2010
T 12 2010
U 1 2010
U 2 2010
U 3 2010
U 4 2010
U 5 2010
U 6 2010
U 7 2010
U 8 2010
U 9 2010
U 10 2010
U 11 2010
U 12 2010
V 1 2010
V 2 2010
V 3 2010
V 4 2010
V 5 2010
V 6 2010
V 7 2010
V 8 2010
V 9 2010
V 10 2010
V 11 2010
V 12 2010
W 1 2010
W 2 2010
W 3 2010
W 4 2010
W 5 2010
W 6 2010
W 7 2010
W 8 2010
W 9 2010
W 10 2010
W 11 2010
W 12 2010
X 1 2010
X 2 2010
X 3 2010
X 4 2010
X 5 2010
X 6 2010
X 7 2010
X 8 2010
X 9 2010
X 10 2010
X 11 2010
X 12 2010

 

[Legends77]

I feel like slapping myself. It does work. Just that with the mulitple columns I have the month no was not displayed beside the subgroup (which is where it is at in the design view).

Thanks for the help dhookom!