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.