Converting SQL to VBA code

[Davidyam]

Hi,

I need help converting from SQL to VBA, can anyone help please. Thank you very much.

TRANSFORM Sum([Overtime Records].Duration) AS SumOfDuration
SELECT [Overtime Records].Officer, Sum([Overtime Records].Duration) AS [Total Of Duration]
FROM [Overtime Records]
GROUP BY [Overtime Records].Officer
PIVOT [Overtime Records].Status;


TRANSFORM Sum([Off-in-Lieu records].[Number of hours of off taken]) AS [SumOfNumber of hours of off taken]
SELECT [Off-in-Lieu records].Officer, Sum([Off-in-Lieu records].[Number of hours of off taken]) AS [Total Of Number of hours of off taken]
FROM [Off-in-Lieu records]
GROUP BY [Off-in-Lieu records].Officer
PIVOT [Off-in-Lieu records].Status;




Thanks you.

Best Regards.
David Yam

[June7]

What do you mean by 'convert SQL to VBA'? That isn't really possible. VBA can recognize and run SQL statements that are constructed in code but that is not a 'conversion'. If what you want to do is take an SQL statement built with Access query designer and write it into a code procedure, that is possible.

However, I have never seen a pivot query SQL written into a VBA procedure.

Perhaps if you explained more about why you want to do this and what you are really trying to accomplish, we can better advise.

[Davidyam]

Hi June,

Thanks for replying.
Actually I wan to create an overtime database.
Currently I have created a form whereby it calculates the hours base on my VBA code and is reflected in the table. I have also created a crosstab query but I think it is not useful thus I wan to change it to VBA code.

I have 3 main tables: Overtime records, Off-in-lieu records and account overview
Overtime records :Officer, duration of OT hours clocked and the status are my main concerns.
Off-in-lieu records :Officer, numbers of hours of off taken and status
Account overview :Balance

I would like to sum-up the total amount of duration of OT hours by distinct officer with status approved from the overtime records table using VBA codes and minus away the total number of hours of off taken by the similar distinct officer with status approved from the Off-in-lieu records table and show it in the third table account overview where it shows the balance hours left by the officers.

Thank you again

Best regards,
David Yam

[June7]

First of all, the third table for storing aggregate data should not be needed. Queries should be able to perform and display the summary calcs you want.

Try: Do two GROUP BY (totals) query on the Overtime and Off-in-lieu tables. Join those two queries to Officers table. Use the third query as RecordSource for a report to display records.