MS Query In Excel -- Grouping! 2

[KLewisBPM]

I have a MSQuery for use in Excel. It looks the data up and prompts the user to select a start date an end date and a stock code.

It then returns the details for all records within that date range for that stock code.

e.g. 01/01/02 to 01/03/02

15 records Jan
13 records Feb
18 records March

What I'm trying to do is get the data back by month so I actually get

1 record Jan (with 15 records data totalled)
1 " Feb " " " "
1 " Mar " " " "

If I can suss this one out the user will enter 3 pieces of information start date, end date and stock code which will automatically put the figures into a graph on another work sheet.

Many thanks in advance

Kind Regards

Kelley Lewis

 

[xlbo]

In MSQuery, select your data column and press the sigma button (cycle through totals) until it gets to SUM
You should then get summed data, grouped by any "dimensions" that you have (eg month) Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[KLewisBPM]

Geoff,

I tried your suggestion, as soon as I click on the sigma button it comes up with an error.

"Too few parameters. Expected 3"

I have 3 criterias set. Start date, end Date and Stockcode.

Any Ideas? Kind Regards

Kelley Lewis

 

[xlbo]

Ummmm - not sure about that - never had that problem
A different suggestion would be to return the data to a pivot table - it's one of the options when you are returning the data to excel
As long as you have a month field in the query, you can set the month to be the row field and SUM of data in the data field Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[combo]

Check if you refer to proper data in your querry (field/table/file renamed/removed?), see the SQL file in MS Querry window.

 

[MarcLodge]

Kelley,
Combo has got it right I think, and the SQL you are describing should read something along the lines of:
Select month, count(*)
from table
where stockcode = '&stockvar'
and date between Startdate and enddate
group by month

This is rough as there will probably need to be some date processing too, to get the monthval, but should give you a pointer.
Hope this helps
Marc

 

[KLewisBPM]

This is the SQL View.

SELECT Excel_Output_Stock.Date, Excel_Output_Stock.StockCode, Excel_Output_Stock.`Actual Production`, Excel_Output_Stock.`Pre cal Production`, Excel_Output_Stock.`Added V`
FROM `I:\ACCESS FEEDBACK\FeedBack`.Excel_Output_Stock Excel_Output_Stock
WHERE (Excel_Output_Stock.Date Between ? And ?) AND (Excel_Output_Stock.StockCode=?)
ORDER BY Excel_Output_Stock.Date

This works fine all of the records come through. But it they come through roughly 3 records per day. Obviously I just want All of Jan,Feb Mar etc as one record totalled by month.

Thanks in advance Kind Regards

Kelley Lewis

 

[MarcLodge]

Kelley,
I'm going to assume that you want to total the 'Actual Production', the 'Pre Production' and the 'Added V', and that you want it by Month'. If that is the case then try:

SELECT MONTH(Excel_Output_Stock.Date),
Excel_Output_Stock.StockCode,
SUM(Excel_Output_Stock.`Actual Production`),
SUM(Excel_Output_Stock.`Pre cal Production`),
SUM(Excel_Output_Stock.`Added V`)
FROM `I:\ACCESS FEEDBACK\FeedBack`.Excel_Output_Stock Excel_Output_Stock
WHERE (Excel_Output_Stock.Date Between ? And ?) AND (Excel_Output_Stock.StockCode=?)
ORDER BY Excel_Output_Stock.Date
GROUP BY Excel_Output_Stock.Date,
Excel_Output_Stock.StockCode

I haven't had the opportunity to test this, so you may need to fiddle with it to get it 100%
Marc

 

[KLewisBPM]

Marc,

I keep getting an error that says.

"Parameters are not allowed in queries that cant be displayed graphically"

I've tried tweaking but cant seem to get anywhere.

Cheers Mate Kind Regards

Kelley Lewis

 

[MarcLodge]

Kelley,
The only thing that looks slightly odd is the use of quotes all over the place, but I guess that as the query works before my/our fiddling, then it must be OK. One question, where does the data come from, because looking at the SQL generated, it appears to list Excel_Output_Stock twice?

I'm going home shortly, and so won't get the chance to have another look for a couple of hours or so. If you come up with anything else (or anybody else does), I'll check back later.

Marc

 

[MarcLodge]

Kelley,
I'm at home now, if you're still having problems, it might be best to email me the spreadsheet (So long as it contains no confidential info). Let me know what you think.
Marc

 

[KLewisBPM]

Marc,

Excel_Output_Stock is the query in Access, where the data originates.

Cheers Kind Regards

Kelley Lewis

 

[xlbo]

Kelley - Have you tried putting the data straight into a pivot table as I suggested. When you set up a query thru MSQuery, one of the last options in the wizard is how you want to return the data.One of the options here is thru a pivot table. This will enable you to group your data without having to group it in the query.... I know it's a work around but it should work Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[MarcLodge]

Hi again Kelley,
Geoff's answer is an option that I'd try.
The only other thing I can think of is to change you MS Query back to how it was, and change the Access query to sum the data and group it, before it gets to Excel. This way you could tweak the query in Access to make sure you are getting the data you require.
hth
Marc

 

[KLewisBPM]

Marc - Geoff

Thanks for your imput. I have finally figured it out. I created a new access query to sum the fields and group by month and then used MSQuery to specify the individual Stock code.

I have achieved exactly what I set out to do and that was to create a spreadsheet that on refresh automatically produced a bar graph containing efficiency by stock code every month starting Jan 01 to Dec 03 this gives a comparisson of how efficient we ran a product on a monthly basis.

Thankyou both for your help in achieving this. A star will now be awarded to you both

Kind Regards

Kelley Lewis

 

[MarcLodge]

Thanks for the star Kelley, delighted to have been of assistance!