Generating a report based on a given condition access 2007

[Enchantix]

Hi All,

I have a table with the following columns that i wanted to report based on the shift colour the product was manufactured in but im not sure what statements to use for my query.

The shift colur relates to the 3rd character from the left of the Batch number, where B= Blue, K = Black, P = purple, and R = Red.

Date, Product, Product total, Sum of downgrades, Batch #, Downgrade 1, Downgrade2,..50
10/10 15CD 1398 4 PWB100 2 , 2 ,
10/10 15CD 1398 3 PWK107 1 , 2 ,
10/10 15CD 1398 10 PWP205 8 , 2 ,
15/10 17NS 744 8 PWB408 3 , 2 ,
15/10 17NS 744 11 PWR550 6 , 2 ,
15/10 17NS 744 12 PWR551 4 , 2 ,




Ive generated a query ( group by/ sum) to get a single row for each product and shift colour:

-used the MID function to get me the 3rd letter for the shift colour so i can group them.

- Used the total (by Max) for the product total to get it to a singe row for each product.

- summed the downgrades.

- Total falldown % = (sum of downgrades/Product total).



i just need my report to produce the following for each shift colour besides each other under the detail section based on the date inputs:

ie.

from 10/10/2016 to 15/10/2016

Label Header

Product: 15CD

Detail

Blue - B shift: Red-R shift: Black - Shift: Purple - P Shift

Total falldown: 0.3%

Product total: 1398

Top 5 downgrades
based on sum
(Transposed)

Downgrade 1 2

Downgrade 2 2

Downgrade 3 .

Downgrade 4 .

Downgrade 5 .





Product: 17NS

Detail

Blue - B Shift Red - R shift: Black shift , Purple Shift

Total falldown: 1.1% Total falldown: 3.1%

Product total: 744 Product total: 744

Top 5 downgrades Top 5 downgrades
based on sum based on sum
(transposed) (Transposed)

Downgrade 1 3 Downgrade 1 10

Downgrade 2 2 Downgrade 2 4

Downgrade 3 . Downgrade 3 .

Downgrade 4 . Downgrade 4 .

Downgrade 5 . Downgrade 5 .


If anyone has some guidance that will be greatly appreciated.


Kind regards

 

[Enchantix]

Not sure you caught my meaning since a sub report has it's own query, thus you should have a separate query for each, not combine them into one.

I think you'll have to post a sample of your data, but please don't use your prior method with lots of row spacing in between. Put it in Excel, ensure the columns fit your data, and dump a sample in a post. You'll get a nicely laid out table. The fact that your data is in columns is not conducive to how data is supposed to be stored in a database, so that may be problematic. It should be in rows.

Ive managed to construct the subreports with a top N query and they work well together.

Thank you so much for your help.


I just have one more hurdle. i now need to generate a report chart for my data below taking into account a number of fields "Product","Shift Colour" for the "total falldown" averaged over a given period. This can be in a single chart or multiple charts for the different products.

Ive added an image of a pivot chart below of what id like to do. ive had several attempts in access to try to get it in the access report chart but it doesnt seem to come off the same. is it possible to do this in access report charts?

[TABLE="width: 393"]
<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4636;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1706;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5233;"> <tbody>[TR]
[TD="class: xl68, width: 89, bgcolor: silver"]Date[/TD]
[TD="class: xl68, width: 130, bgcolor: silver"]Product[/TD]
[TD="class: xl68, width: 48, bgcolor: silver"]Face[/TD]
[TD="class: xl68, width: 109, bgcolor: silver"]Shift Colour[/TD]
[TD="class: xl68, width: 147, bgcolor: silver"]AvgOfTotalFalldown[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]7/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]1.5mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]4.57%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]8/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]1.5mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]4.27%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]9/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 2[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]4.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]2.04%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]12/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 2[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]4.01%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]12/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 2[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]4.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]K[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]3.81%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]13/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 3[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]2.4mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]K[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]2.13%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]13/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 3[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]2.4mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]4.15%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]13/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 4[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]4.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]3.23%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]13/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 4[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]4.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]K[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]5.21%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 5[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]2.4mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]7.80%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 5[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]2.4mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]K[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]1.91%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 6[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]K[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]2.71%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 6[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]P[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]2.39%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 6[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]3.09%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 7[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]12.22%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 7[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]P[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]7.63%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]14/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 7[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]13.95%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]15/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 8[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]10.73%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]15/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 8[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]K[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]3.91%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]15/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 8[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]4.66%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]15/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 9[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]2.17%[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 89, bgcolor: transparent"]15/09/2016[/TD]
[TD="class: xl70, width: 130, bgcolor: transparent"]Product 9[/TD]
[TD="class: xl70, width: 48, bgcolor: transparent"]3.0mm[/TD]
[TD="class: xl70, width: 109, bgcolor: transparent"]R[/TD]
[TD="class: xl71, width: 147, bgcolor: transparent"]2.36%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Micron]

This is just my opinion, though I know I'm not alone. Access charting stinks. It always will as long as there's Excel for sale. I couldn't be of much help with Access charting. The one and only time I used it was an experience I'm still trying to forget.

 

[Enchantix]

This is just my opinion, though I know I'm not alone. Access charting stinks. It always will as long as there's Excel for sale. I couldn't be of much help with Access charting. The one and only time I used it was an experience I'm still trying to forget.

Fair enough. Sorry to bring back those memories

Thank you mate i really appreciate your guidance.