Need to select number of licenses issued by month/year

petekipe
petekipe used Ask the Experts™
on
I have an Access 2003 database with a License table containing fk_customer related to a Customer table containing pk_customer.  The License table also contains date_issued, which is a full yyyy-mm-dd format field.  I need to create a query that returns the number of first licenses issued to customers for each month/year of history.  I don't need any customer information, just the count of first licenses issued by month and year.  Can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try something like this:

SELECT fk_customer, Year([date_issued]) AS [Year], Month([date_issued]) AS [Month],
Min([date_issued]) AS MinOfdate_issued, Min([license_field_name]) AS MinOfLicense_field
FROM license_table_name
GROUP BY fk_customer, Year([date_issued]), Month([date_issued])
ORDER BY fk_customer, Year([date_issued]), Month([date_issued]);

This should give you the first license # and corresponding date for each customer for each year and month they were issued a license. From there, you can just count the records by year and month.

I hope that this helps.

Doug
petekipeOwner

Author

Commented:
Doug,

Thanks for your help.  The results aren't quite right yet, but I'm not how to fix the query.  I reformatted your query just a bit to make it easier for me to read, and to supply correct field names:

SELECT        fk_customer,
                    Year([date_issued]) AS [Year],
                    Month([date_issued]) AS [Month],
                    Min([date_issued]) AS MinOfdate_issued,
                    Min([pk_license]) AS MinOfLicense_field
FROM          License
GROUP BY   fk_customer,
                    Year([date_issued]),
                    Month([date_issued])
ORDER BY   fk_customer,
                    Year([date_issued]),
                    Month([date_issued]);

I'm getting multiple rows for the same fk_customer value - it looks like it's returning the first license issued for each unique year/month combination.  What I want is the year and month of the first license issued for each unique fk_customer.  The result set should contain a number of rows equal to the number of customers that have ever been issued a license.  I want to ignore all but the first license issued.

Pete
Pete,

Sorry, I added some complexity because I thought you wanted the information broken out by year and month. To get just the first license date for each customer, use the following:

SELECT        fk_customer,
                   Min([date_issued]) AS MinOfdate_issued,
FROM          License
GROUP BY   fk_customer;
 
Doug

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial