Simple SQL problem from a newbie

[Gregg Warkulwiz]

I am trying to build a Macro in MS-Access 97 that will use two fields from a table named
SoftwareInfo.
The two fields to be used are "Purchase Request Number" and "Number of licenses"
I want the macro to take a purchase request number, which will have duplicates,
take the number of licenses corresponding to that purchase request number
and add it to a running number. The macro would then search for the next matching
Purchase Request Number, if any, and add the corresponding "number of licenses" to the running
total as well. If there is only one Purchase Request number, then the corresponding
number of licenses would be the beginning and end of the running total.

Does anyone have any suggestions for the SQL code for this?
Any comments welcome.
Please reply.
Thank you in advance.

Gregg Warkulwiz.
CIS Intern, Bloomsburg University of PA

[Rob Beachy]

I think you should use a query to perform this instead of a macro. Let's assume that table1 is the table that includes your two fields (Purchase Request Number and Number of Licenses). The following would be the SQL statement to take all duplicate Purchase Request Numbers and give you a total

SELECT Table1.[Purchase Request Number], Sum(Table1.[Number of Liscenses]) AS [SumOfNumber of Liscenses]
FROM Table1
GROUP BY Table1.[Purchase Request Number];


------------
Gregg Warkulwiz at 7/6/99 9:24:03 AM

I am trying to build a Macro in MS-Access 97 that will use two fields from a table named
SoftwareInfo.
The two fields to be used are "Purchase Request Number" and "Number of licenses"
I want the macro to take a purchase request number, which will have duplicates,
take the number of licenses corresponding to that purchase request number
and add it to a running number. The macro would then search for the next matching
Purchase Request Number, if any, and add the corresponding "number of licenses" to the running
total as well. If there is only one Purchase Request number, then the corresponding
number of licenses would be the beginning and end of the running total.

Does anyone have any suggestions for the SQL code for this?
Any comments welcome.
Please reply.
Thank you in advance.

Gregg Warkulwiz.
CIS Intern, Bloomsburg University of PA