Creating an Array in Access Report

[Marckas]

I really need help in creating an Array in Access Report so like when it shows one suipplier, it shows all its points from the different mind. For Example, ABS Supplier would have quality points for every month. The report should show the quality points of just that supplier in the right slot, same goes for all the other fields. Like if the quality pint for ABC supplier is 50 for jan 2002, 48 feb 2002, 47 march 2002 and so on and show this in a report. This goes for 4 other fields to be the same way, placing it on the right slot automatically and then calculating totals...I know i have to create an array, but i dont know how to do it in a Access Report.

 

[BSman]

This can be very tricky to do in a report. Look up the sample cross tab report in the Access sample databases as a start. I have done the same thing in a way that I prefer, but the basic concept started with looking at the cross tab report.

It's complicated, but I'll try to give some starting ideas:

1. Your controls on the form will not be linked to any field in the source table.
2. The source should probably be a query (perhaps a grouping query to calculate your quality points).

If the source query includes the supplier, month, and points, you can do the following to provide dynamically set month headings (so the report can be run for any 12 months, or 6 months, or whatever).

Using, for example, two month/year controls (start and end months) on the form that calls the report, the headings (again, controls not linked to any specific data field) txtHead1, txtHead2, txtHead3, etc., should be populated with the approriate values for each month based on the start/end values. Be sure the data stored in each txtHeadx will match the value in the source query for the month.

For simplification, label the controls in each column with the same number (ex: txtHead1, txtPoints1, txtTotPoints1, etc.). (There's a way of building the name of a column using the numers in code so you can reference the right column in a for intX = 1 to 12 statement.)

Set up a group footer that breaks on the supplier. In the (non-visible) detail area, for each record determine the month, match the month to the value in txtHeadx, then place the value of Points into the same txtPointsx. In the group footer (that is visible) put the vendor name in an appropriate control.

Remember that your detail must have a control to hold each value (month, points), because you can't work directly with the values from the query/table. (Yes, you could use a recordset, but that is even more complicated.)

I know this sounds (and is) complicated, but if you experiment with it you should be able to figure it out.