?
Solved

Access Query to list Model's shipped by month

Posted on 2009-12-18
11
Medium Priority
?
265 Views
Last Modified: 2012-05-08
How would I modify the query below to list Model's shipped by month.  Maybe the output to look like:

Product Model Number      Month Shipped      SumOfQuantity
VEDR01                                      June 2009         44
VRD01                                      June 2009                   10
VEDR01                                      July 2009                          31
VRD01                                      July 2009                   15

Note: Month Shipped doesn't have to be listed above.  Any way understandable will be fine such as:
6/2009
7/2009
SELECT [CRDM Demo Shipments].[Product Model Number], [CRDM Demo Shipments].[Actual Ship Date], Sum([CRDM Demo Shipments].Quantity) AS SumOfQuantity
FROM [CRDM Demo Shipments]
GROUP BY [CRDM Demo Shipments].[Product Model Number], [CRDM Demo Shipments].[Actual Ship Date]
ORDER BY [CRDM Demo Shipments].[Product Model Number], [CRDM Demo Shipments].[Actual Ship Date];


Clip of Output:
Product Model Number	Actual Ship Date	SumOfQuantity
V3Nitin 11'	12/30/2008	10
V3Nitin 11'	9/11/2009       	1    
V3-S II Inn	12/3/2008 	5
V3-S II INN	12/30/2008	10
V3-S II INN	6/19/2009	          5
V3-S II Inn	7/23/2009	          5
V3-S II Inn	9/3/2009	           5
V3-S II Inn	9/11/2009      	1           
V3-S II Inn	11/25/2009	5
V3-S II Out	12/3/2008	        5
V3-S II OUT	1/30/2009	        5
V3-S II OUT	6/19/2009	          5
V3-S II Out	7/23/2009	           5
V3-S II Out	9/3/2009	          5
V3-S II OUT	9/11/2009	1
V3-S II Out	11/25/2009	5
V3-Stylet-gray knob	10/2/2009	1
VEDR01	4/21/2008	                   1
VEDR01	6/6/2008	                   4
VEDR01	6/10/2008	                    2
VEDR01	6/12/2008	                     2
VEDR01	7/8/2008	                      2
VEDR01	9/16/2008	2
VEDR01	10/10/2008	2
VEDR01	10/30/2008	1
VEDR01	12/12/2008	2
VEDR01	2/17/2009	3
VEDR01	2/18/2009	1
VEDR01	2/23/2009	1
VEDR01	4/7/2009	4
VEDR01	4/24/2009	13
VEDR01	5/8/2009	1
VEDR01	5/21/2009	1
VEDR01	6/2/2009	1
VEDR01	7/1/2009	1
VEDR01	7/13/2009	1
VEDR01	7/14/2009	2
VEDR01	7/23/2009	1
VEDR01	8/4/2009	1
VEDR01	9/11/2009	6
VEDR01	9/30/2009	4
VEDR01	11/24/2009	38
VEDR01	11/30/2009	7
VEDR01	12/3/2009	2
wire/stylet guide demo 4193	11/11/2008	5

Open in new window

0
Comment
Question by:schmir1
  • 5
  • 4
  • 2
11 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 1000 total points
ID: 26084121
try this
SELECT [CRDM Demo Shipments].[Product Model Number], DATEPART(month,[CRDM Demo Shipments].[Actual Ship Date]), YEAR([CRDM Demo Shipments].[Actual Ship Date]), Sum([CRDM Demo Shipments].Quantity) AS SumOfQuantity
FROM [CRDM Demo Shipments]
GROUP BY [CRDM Demo Shipments].[Product Model Number], DATEPART(month,[CRDM Demo Shipments].[Actual Ship Date]), YEAR([CRDM Demo Shipments].[Actual Ship Date])
ORDER BY [CRDM Demo Shipments].[Product Model Number], YEAR([CRDM Demo Shipments].[Actual Ship Date]), DATEPART(month,[CRDM Demo Shipments].[Actual Ship Date])

Open in new window

0
 
LVL 16

Expert Comment

by:Sheils
ID: 26084290
Try
SELECT [CRDM Demo Shipments].[Product Model Number], Format([Actual Ship Date],"mmm yyyy") AS [Month Shipped], Sum([CRDM Demo Shipments].Quantity) AS SumOfQuantity
FROM [CRDM Demo Shipments]
GROUP BY [CRDM Demo Shipments].[Product Model Number], [CRDM Demo Shipments].[Actual Ship Date]
ORDER BY [CRDM Demo Shipments].[Product Model Number], [CRDM Demo Shipments].[Actual Ship Date];

Open in new window

0
 

Author Comment

by:schmir1
ID: 26084468
tigin44:
  When I run your query, I get "Enter Parameter Value" for "month"?
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 26

Expert Comment

by:tigin44
ID: 26084530
..
SELECT [CRDM Demo Shipments].[Product Model Number], month([CRDM Demo Shipments].[Actual Ship Date]), YEAR([CRDM Demo Shipments].[Actual Ship Date]), Sum([CRDM Demo Shipments].Quantity) AS SumOfQuantity
FROM [CRDM Demo Shipments]
GROUP BY [CRDM Demo Shipments].[Product Model Number], month([CRDM Demo Shipments].[Actual Ship Date]), YEAR([CRDM Demo Shipments].[Actual Ship Date])
ORDER BY [CRDM Demo Shipments].[Product Model Number], YEAR([CRDM Demo Shipments].[Actual Ship Date]), month([CRDM Demo Shipments].[Actual Ship Date])

Open in new window

0
 

Author Comment

by:schmir1
ID: 26084535
sb9:
Your query ran fine but it does list some months more than once.  For example, in Nov 2009, VEDR01 was shipped twice, 38 units and 7 units.  Your query lists the following for that model:
Product Model Number      Month Shipped      SumOfQuantity
VEDR01                                       Nov 2009                        38
VEDR01                                       Nov 2009               7
0
 
LVL 16

Expert Comment

by:Sheils
ID: 26084616
Does this happen with every model number that has more than one shipment in a particular month.

If it works sometime and sometime it does not work then the problem will be with the model number (usually blank spaces)

Otherwise the problem is with the query itself. Any change uploading a sample of the table CRDM Demo Shipments
0
 
LVL 16

Expert Comment

by:Sheils
ID: 26084668
I have attached a sample of what I believe your db looks like. It works fine and sum all like months

db3.mdb
0
 

Author Comment

by:schmir1
ID: 26084707
Here is the table.
DemoFortesting.mdb
0
 
LVL 16

Assisted Solution

by:Sheils
Sheils earned 1000 total points
ID: 26085015
Seems to work fine. See query1

DemoFortesting.mdb
0
 

Author Comment

by:schmir1
ID: 26116138
sb9:
  Clever use of Min.  That works fine.  

Thanks to both of you.  They both work great.
0
 

Author Closing Comment

by:schmir1
ID: 31667951
Both solutions work perfectly!!!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

592 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