More Report Grouping problems
Ralated to previous question.
Hi all
I have a customer that manufactures Custom picture frames. When he has his week’s orders he wants to see a report that shows the Following
Frame type (Shape or Section)
Finish (Colour)
Thickness
Width
Quantity and Length of long edges
Quantity and Length of short edges
So he wants to see all the same frame types with the same colour, thickness and width together.
This is easy enough with the long lengths but how do I structure the subReport for the short lengths which are the same in every other respect except for length.
By Adding another sorting level to the report to group the shorts below the longs I get the enclosed report. The short lengths in blue are sorted as are the long lengths. But you can see that the addition of a short grouping splits the Long lengths into two becuase the shorts details are on the same row of the underlying table. You can also see that where the shorts are the same as the longs IE a square frame that it is impossible to total the shorts and longs together as you would do subconsiously if calculating manually.
At the moment I see no option but to create a new table and add the shorts to the longs in the same column and base the query and report on that. Just seems odd to have to do that.
I really thought I had it cracked with Jeff Coachmans responce but when I printed a report today I got the enclosed. If you are online jeff, Help Project.pdf
Hi all
I have a customer that manufactures Custom picture frames. When he has his week’s orders he wants to see a report that shows the Following
Frame type (Shape or Section)
Finish (Colour)
Thickness
Width
Quantity and Length of long edges
Quantity and Length of short edges
So he wants to see all the same frame types with the same colour, thickness and width together.
This is easy enough with the long lengths but how do I structure the subReport for the short lengths which are the same in every other respect except for length.
By Adding another sorting level to the report to group the shorts below the longs I get the enclosed report. The short lengths in blue are sorted as are the long lengths. But you can see that the addition of a short grouping splits the Long lengths into two becuase the shorts details are on the same row of the underlying table. You can also see that where the shorts are the same as the longs IE a square frame that it is impossible to total the shorts and longs together as you would do subconsiously if calculating manually.
At the moment I see no option but to create a new table and add the shorts to the longs in the same column and base the query and report on that. Just seems odd to have to do that.
I really thought I had it cracked with Jeff Coachmans responce but when I printed a report today I got the enclosed. If you are online jeff, Help Project.pdf
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Image below, I hope
You can see that what is required is all 900 lengths and 800 lengths etc are grouped together. and the sample shows how I have had to do it. A query makes a new table and a second query appends the shorts, calling them both "Length" the resultant report then groups all lengths together. and that is what is required. Just a strange thing to have to do.
JeffCoachmanGroupingReports.mdb
You can see that what is required is all 900 lengths and 800 lengths etc are grouped together. and the sample shows how I have had to do it. A query makes a new table and a second query appends the shorts, calling them both "Length" the resultant report then groups all lengths together. and that is what is required. Just a strange thing to have to do.
JeffCoachmanGroupingReports.mdb
ASKER
If that's what needs to be done, then so be it.
If it were me, then I would not separate the Lengths into "Long" and "Short" distinct fields.
I would just make one field ("Length") then create a formula/function to determine whether it was long or short, in a derived field (LengthDesig)
Something roughly like this:
If Length>600 then
LengthDesig="Long"
elseIf Length<=600 then
LengthDesig="Short"
end if
This would also be much easier from a data entry stand point.
Having the fields separate (with no validation) a user could enter Long values of: 5,12, 3...etc, ...and short values of 1725, 1425,1888817, ...etc
To give further credence to the One field approach, note that in your report there is no visible distinction between Long or Short anyway...
;-)
This would be far easier than running a make table query and an append query.
Remember, using the "Run two queries" technique will require you to automate the running of these queries immediately before the report is opened (each time you need to run the report)
This means that if the report is opened directly, it may not contain the most up to date info.
:-O
Finally, I am still perplexed as to why this table has no primary key.
...but these are all issue that you as the developer must consider.
JeffCoachman
Hello DatabaseDek
(no points, please, just adding a few comments)
> Just a strange thing to have to do.
It is in fact quite natural. It makes perfect sense to describe a frame by its two measurements, so they are on the same record. It also makes sense for production to isolate the elements: two vertical and two horizontal borders to manufacture and process.
This is such a good example of the limits of normalization that I will certainly reuse it! The normalized approach would be to have one table for frames, and a linked list of borders making up the frame. Quite silly, but that would be “normal”. In such a structure, you could create your report immediately. As it stands, you need an intermediate step to “normalize” the data (in this case because two fields need to be treated as records).
However, you don't need a make-table and append query. A UNION query will do nicely. Based on Jeff's uploaded database:
SELECT ID, FType, Finish, SectionWidth, Thickness, Long As Length, Quantity, 'L' As SL
FROM Table1
UNION ALL
SELECT ID, FType, Finish, SectionWidth, Thickness, Short, Quantity, 'S'
FROM Table1
ORDER BY 2,3,4,5,6
The ordering is optional, it will be overwritten by the report's sorting and grouping anyway. Note that both length fields are now records; an additional field SL identifies the source of each record, should that information be needed at any point.
So, yes, it is a strange thing to have to do, until you understand what you are doing. You take the object “frame” managed by your table and you extract another object: “borders”, with twice as many records!
Cheers!
(°v°)
(no points, please, just adding a few comments)
> Just a strange thing to have to do.
It is in fact quite natural. It makes perfect sense to describe a frame by its two measurements, so they are on the same record. It also makes sense for production to isolate the elements: two vertical and two horizontal borders to manufacture and process.
This is such a good example of the limits of normalization that I will certainly reuse it! The normalized approach would be to have one table for frames, and a linked list of borders making up the frame. Quite silly, but that would be “normal”. In such a structure, you could create your report immediately. As it stands, you need an intermediate step to “normalize” the data (in this case because two fields need to be treated as records).
However, you don't need a make-table and append query. A UNION query will do nicely. Based on Jeff's uploaded database:
SELECT ID, FType, Finish, SectionWidth, Thickness, Long As Length, Quantity, 'L' As SL
FROM Table1
UNION ALL
SELECT ID, FType, Finish, SectionWidth, Thickness, Short, Quantity, 'S'
FROM Table1
ORDER BY 2,3,4,5,6
So, yes, it is a strange thing to have to do, until you understand what you are doing. You take the object “frame” managed by your table and you extract another object: “borders”, with twice as many records!
Cheers!
(°v°)
<It is in fact quite natural. It makes perfect sense to describe a frame by its two measurements, so they are on the same record. It also makes sense for production to isolate the elements: two vertical and two horizontal borders to manufacture and process.>
...OK
Perhaps I missed something perceptually...
Jeff
...OK
Perhaps I missed something perceptually...
Jeff
I don't think you missed it, Jeff. As “database guys”, we automatically normalize in our heads, and this is exactly what was required — at least formally — for the current question. So we were in the “borders production data model”. But if you think of the entire frame, say to order the back panel or the glass cover (for some models, I guess), you need the full metrics of the frame, right? So, anyway, I realised that in this particular case, both data models were meaningful. That is what I found interesting, because it's the first time I come across such a clear and simple example.
Cheers!
(°v°)
Cheers!
(°v°)
Did we mention that it's great to have you back...
:-)
:-)
Thanks for that! (^v°)
ASKER
Harfang
Thank you for your comments. I have done as you asked and not awarded any points. So thanks will have to do I suppose.
In case I didn't mention it. Thanks.
Thank you for your comments. I have done as you asked and not awarded any points. So thanks will have to do I suppose.
In case I didn't mention it. Thanks.
I would have no problem with a split ofthe points as it is alwasys best to know more than 1 approach...
;-)
Jeff
;-)
Jeff
ASKER
I think you more than earned the points with the amount of effort you put in, Harfang recognises that and you have solved other problems for me with your posts.
Thank you both
Thank you both
Image files are viewable directly in the post.
PDF files require:
You to save the file as a PDF
Us to Have the PDF reader
Us to Open the PDF directly
;-)
2. As with your last question you need to take a closer look at the design of this table... and the report.
In most cases having more than 3 grouping levels make the report hard to follow...
3. Please resubmit your sample DB (following the steps I posted in your previous question)
But most importantly you must include some sort of graphic clearly showing the *exact* output you are expecting based on this sample data...
JeffCoachman