Custom report in Access 2002 question

[OsheRono]

I do hope this is the right section (since VBA is so close to VB 6.0).

Well here is my dilemma:

I am trying to have Access return a customized report. The format I have been asked for it to be output as it the following:

Machine type // where the Unique name of the machine is displayed, ignores duplicate values

of times said machine has been taken in for repairs (for a specific time period)

% of total repairs (for specific time period) the above number represents.

Parts replaced/repaired // subdivision included for each machine.

Part Name  # times this part was fixed  % percentage of total repairs for machine 

//this is repeated until all machines repaired for the requested time period have been displayed. Then at the end of the report, I would like to put the total number of repairs done during that time period.

Now, I have been trying to get these to display correctly, but I have hit a snag (or several, depending on the point of view):

SQL command I am using:

SELECT DISTINCT BDC_Compresseur.Compresseur
FROM BDC_Compresseur
WHERE (((BDC_Compresseur.Compresseur)='12/12')); //12/12 is just a test value

When requesting an specific machine, I have the report show ALL instances of it, but display the count correctly (so if the count is 16, the record is displayed 16 times). If I add DISTINCT (like I did in the above example) I get it to display once, BUT the count displayed is always one.

Now I could write a code in which I have the program call the specific value and display it, then do a separate sql query to get the count, then do a separate query to do the call for EACH of the part names, then do a call for a count of each of the part name instances, while calculating the total values for each machine, then calculating their percentages, then outputting it all via for each statement to generate a report. But it seems like a lot of redundant work somehow. Any suggestions? I am not well versed in SQL commands, so perhaps there is something I am doing wrong (more than likely the answer is yes).

If there is no other way, I'll go ahead and code the report to be done in VBA alone... but it seems like a convoluted way of doing it.

Any help is appreciated.

[OsheRono]

Update: Ok I have managed to have the report display the machine types separately, as well as displaying the piece by machine type. However I can't get them to display in the correct order, that is as the piece types nested inside their corresponding machine types. A friend of mine told me to use a subquery, which I have no experience in doing.

The result is this mess:

SELECT BDC_Compresseur.Compresseur, BDC_Piece_Defaut.Type_Piece, BDC_Piece_Defaut.Type_Defaut, COUNT( BDC_Piece_Defaut.Type_Defaut) AS Interventions
FROM BDC_Piece_Defaut INNER JOIN (SELECT  BDC_Compresseur.ID_Intervention, BDC_Compresseur.Compresseur   FROM BDC_Compresseur GROUP BY  BDC_Compresseur.Compresseur,  BDC_Compresseur.ID_Intervention) BDC_Compresseur
ON BDC_Piece_Defaut.ID_Intervention = BDC_Compresseur.ID_Intervention;

which tells me that "Compresseur" is not a part of the sql string. I'm starting to lose hope :(

[AndreRet]

OsheRono, if I understand the question correctly, use the 'ORDER BY' as part of your SELECT statement. After ORDER BY, add at which order you would like the records to be displayed.