Below are the results from Query "TEST" which read off of a both Tables "Paint" and "Paint Detail". Of which "Paint" is a overall description of the record entered through a form and "Paint Detail" has the individual records of the description, entered through the Paint form as a subform.
Record Plant Project Code Notes Desc Y1 Y2 Y3 Y4
4 Paint Maint F Spray Nec 72 75 31
5 Paint Maint F Air Cost 18 18 9
3 Paint Maint F Booth 599 7
3 Paint Maint F Booth 590 4
3 Paint Maint F Booth 591 2
3 Paint Maint F Booth Syst 72 24
37 Paint Maint F Upgrd Proc 15 15 15
As they appear in the query the sorting is correct going in descending $ order in Y1 - Y4.
However, when making the report and "grouping" by record (header & footer), it ask for ascending or descending. Therefore, if ascending is choosen, all record 3's are grouped and appear as the first record the a line is under the recorde footer. When actually record 4 should be grouped and be the first record of the report as it is in the query. Is there anyway to make the report group by record without having to sort the record by ascending or descending?
It has been suggested to do the following in a new query based upon the current query:
Select *,
(SELECT) Max (Y1)
FROM Test As T
WHERE T.Record = B.Record) As MaxY1,
(SELECT) Max (Y2)
FROM Test As T
WHERE T.Record = B.Record) As MaxY2,
(SELECT) Max (Y3)
FROM Test As T
WHERE T.Record = B.Record) As MaxY3,
(SELECT) Max (Y4)
FROM Test As T
WHERE T.Record = B.REcord) As MaxY4
FROM Test As B
However it asks for a perameter value of T.Record and B.Record If any record number is entered it simply places the highest Y1 value for the record as MaxY1 and so on.
Please, any advice suggestions and or code is greatly appreciated!
Thank you for any and all help,
PBrown
Record Plant Project Code Notes Desc Y1 Y2 Y3 Y4
4 Paint Maint F Spray Nec 72 75 31
5 Paint Maint F Air Cost 18 18 9
3 Paint Maint F Booth 599 7
3 Paint Maint F Booth 590 4
3 Paint Maint F Booth 591 2
3 Paint Maint F Booth Syst 72 24
37 Paint Maint F Upgrd Proc 15 15 15
As they appear in the query the sorting is correct going in descending $ order in Y1 - Y4.
However, when making the report and "grouping" by record (header & footer), it ask for ascending or descending. Therefore, if ascending is choosen, all record 3's are grouped and appear as the first record the a line is under the recorde footer. When actually record 4 should be grouped and be the first record of the report as it is in the query. Is there anyway to make the report group by record without having to sort the record by ascending or descending?
It has been suggested to do the following in a new query based upon the current query:
Select *,
(SELECT) Max (Y1)
FROM Test As T
WHERE T.Record = B.Record) As MaxY1,
(SELECT) Max (Y2)
FROM Test As T
WHERE T.Record = B.Record) As MaxY2,
(SELECT) Max (Y3)
FROM Test As T
WHERE T.Record = B.Record) As MaxY3,
(SELECT) Max (Y4)
FROM Test As T
WHERE T.Record = B.REcord) As MaxY4
FROM Test As B
However it asks for a perameter value of T.Record and B.Record If any record number is entered it simply places the highest Y1 value for the record as MaxY1 and so on.
Please, any advice suggestions and or code is greatly appreciated!
Thank you for any and all help,
PBrown
