Solved

Access SQL Count number of items per month

Posted on 2010-11-08
4
1,041 Views
Last Modified: 2012-05-10
Hi Experts,

I am new to SQL, and would like to know if there is an easy way to do the following:

I have a table called Builds, and the fields in it are BuildDate, Hull number, ShipType.

I want to make a chart that will plot the number of HullNumbers per month.

So for example, I want to select a range of Months, Say Jan 2010, to May 2010, and get a count of the number of HullNumbers  plotted on a line chart . I guess it is like a count of the records per month.

If I have a Chart called chartB, what would the SQL query look like if it was the datasource?

I know this is a pretty simple thing, but I have not had more than a few days with SQL, and am really finding it a little esoteric.

Thanks for your help.
0
Comment
Question by:WestCoastHip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 34089699
you could try something like this

SELECT COUNT(HullNumbers) AS TotalHullNumbers, Format(BuildDate,"MMM YYYY")
FROM Builds
GROUP BY Format(BuildDate,"MMM YYYY")


Create a new query in masaccess, go to sql view and paste this

Because we use COUNT we have to group all other fields
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34089703
Since your new to SQL, it might be a good idea to use the SQL wizard.

Have a butchers here as it should hopefully help you out http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34089709
Now one thing I forgot to add is the build date. The date obviously has a day number in it (assuming BuildDate is stored as a date field in the database). In order to extract just the month and year we make use of the format command and specify the strings MMM (for 3 character month) and YYYY (for 4 digit year). If you highlight the Format command then hit F1, it should bring up more info plus a list of other strings to use.

If BuildDate not saved as a date but just text with no day number then remove the Format, just leave it as BuildDate (in both uses SELECT and GROUP BY)
0
 

Author Closing Comment

by:WestCoastHip
ID: 34089719
Thanks for your help rocki! I appreciate your time, and the more I see the more I am learning.

Thanks again.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete table fields 3 47
usage of ms/excell 3d maps in ms/access 2016 6 42
Converting Access 2016 from 32-bit to 64-bit 8 57
Reoccurring Access Query 24 46
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question