Report Grouping

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Can anyone help?
This query runs off (2) tables: Paint & Paint Detail.
Which forms have been made for: Paint Detail is a subform
allowing multiple details for one Paint record.

Here is the query results and the order they actually
appear:
The fields are:
Record/Plant/Project/Code/Notes/Description/Y1/Y2/Y3/Y4
First line of Query results:
4 / Paint / Manuf / F / Spray / Nescessary / 72 / 75/ 31 /0
2nd line of results:
5 / Paint / Manuf / F / Costs / Nescessary / 18 / 18/ 9 / 0
3rd line
3 / Paint / Manuf / F / C1 / Nescessary / 3 / 0 / 0 / 0
4th line
3 / Paint / Manuf / F / C2 / Nescessary / 2 / 0 / 0 / 0
5th line
3 / Paint / Manuf / F / C3 / Nescessary / 1 / 0 / 0 / 0
6th line
3 / Paint / Manuf / F / C / Nescessary / 0 / 89 / 78 / 70
7th line
37 / Paint / Manuf / F / Proce / Nescessary / 0 / 0 / 0 /
18

The query is sorting the information as needed (Y1 Z-A
then Y2 Z-A and so on. The problem is when the report
runs.
The report needs to group all the same records together
(in this one all the (3)'s should be grouped). However
when making the report and putting record as a header, it
ask for ascending or descending. Therefore, if Ascending
is chosen, All record 3's appear as the top line when
actually record 4 should be first as in the query. Is
there anyway to make the report group by record# without
having it sort it by record#?

Thank you in advance for any and all help,

Paul
 
Paul said:
This query runs off (2) tables: Paint & Paint Detail.
Which forms have been made for: Paint Detail is a subform
allowing multiple details for one Paint record.

Here is the query results and the order they actually
appear:
The fields are:
Record/Plant/Project/Code/Notes/Description/Y1/Y2/Y3/Y4
First line of Query results:
4 / Paint / Manuf / F / Spray / Nescessary / 72 / 75/ 31 /0
2nd line of results:
5 / Paint / Manuf / F / Costs / Nescessary / 18 / 18/ 9 / 0
3rd line
3 / Paint / Manuf / F / C1 / Nescessary / 3 / 0 / 0 / 0
4th line
3 / Paint / Manuf / F / C2 / Nescessary / 2 / 0 / 0 / 0
5th line
3 / Paint / Manuf / F / C3 / Nescessary / 1 / 0 / 0 / 0
6th line
3 / Paint / Manuf / F / C / Nescessary / 0 / 89 / 78 / 70
7th line
37 / Paint / Manuf / F / Proce / Nescessary / 0 / 0 / 0 /
18

The query is sorting the information as needed (Y1 Z-A
then Y2 Z-A and so on. The problem is when the report
runs.
The report needs to group all the same records together
(in this one all the (3)'s should be grouped). However
when making the report and putting record as a header, it
ask for ascending or descending. Therefore, if Ascending
is chosen, All record 3's appear as the top line when
actually record 4 should be first as in the query. Is
there anyway to make the report group by record# without
having it sort it by record#?
Click to expand...

Like I said before, add a field that calculates the maximum
Y values per record number. You may be able to do it in
your original query, but it might get messy so let's write
another query that's based on your current query.

SELECT *,
(SELECT Max(Y1)
FROM basequery As T
WHERE T.Record = B.Record) As MaxY1,
(SELECT Max(Y2)
FROM basequery As T
WHERE T.Record = B.Record) As MaxY2,
(SELECT Max(Y3)
FROM basequery As T
WHERE T.Record = B.Record) As MaxY3,
(SELECT Max(Y4)
FROM basequery As T
WHERE T.Record = B.Record) As MaxY4
FROM basequery As B

Now you can set the report to first group on the expression:
=Format(MaxY1,"000") & Format(MaxY2,"000") &
Format(MaxY3,"000") & Format(MaxY4,"000") &
Format(Record,"000")

and set the next level to sort on the expression:
=Format(Y1,"000") & Format(Y2,"000") & Format(Y3,"000") &
Format(Y4,"000")

I'm assuming that you want to use the higer numbered Ys as
tie breakers when two or more records have the same lower
numbered Y values. If the Y2, Y3 & Y4 values are
irrelevant, then you can remove those parts from all of the
above.
 
Paul said:
I went into a new query and SQL and with the exception of
replacing "basequery" with test (which is the basequery).
However, when the query runs it asks for a parameter value
for T.Record and B.Record. If 3 is typed all the Max's
are filled in with the top values for record 3
Click to expand...

Replace "Record" with the name of the record number field.

If that doesn't fix it, double check that the table aliases
(As T and As B) are still part of the query.
 
Sinced you aliased Paint_Detail as B, you need to refer to it as B in the
subqueries. Also since you aliased Paint as T in the subqueries, you have to
use T as the tablename in the where clause.

SELECT (SELECT Max (Y1)
FROM Paint as T
WHERE T.Record_Number = B.Record_Number) AS MaxY1,
(SELECT Max (Y2)
FROM Paint As T
WHERE T.Record_Number = B.Record_Number) AS MaxY2,
(SELECT Max (Y3)
FROM Paint As T
WHERE T.Record_Number = B.Record_Number) AS MaxY3,
(SELECT Max (Y4)
FROM Paint As T
WHERE T.Record_Number = B.Record_Number) AS MaxY4, *
FROM Paint_Detail AS B;

I hope that works
 
Back
Top