Finding a unique day's records in a month 1

[hansretallick]

I have an Access database which records data relating to company branches on a weekly basis. Some of this data has to be presented on a monthly basis, and I have extracted the 'month' integer from the date to faciltate this. My problem is with a couple of fields where I have to present the values at the last record in any month.

What I need is as follows:

Branch Day Month Year Pipeline PipelineValue

1 27 2 2006 54 36500
2 27 2 2006 76 76000
3 27 2 2006 34 22000

Where the day is the highest recording date in that particular month


The query which I have produced using Max of Day just produces a result with every weekly date listed. How can I just list the results for the last recorded day of the month without the other days appearing.

This is the query

SELECT qryActivitySelectMonth.Branch, Max(qryActivitySelectMonth.Day) AS MaxOfDay, qryActivitySelectMonth.Month, qryActivitySelectMonth.Year, qryActivitySelectMonth.Pipeline, qryActivitySelectMonth.PipelineValue
FROM qryActivitySelectMonth
GROUP BY qryActivitySelectMonth.Branch, qryActivitySelectMonth.Month, qryActivitySelectMonth.Year, qryActivitySelectMonth.Pipeline, qryActivitySelectMonth.PipelineValue, qryActivitySelectMonth.Day
ORDER BY qryActivitySelectMonth.Month, qryActivitySelectMonth.Year;

I would really appreciate any help with this

Hans

 

[lespaul]

remove the field DAY from the GROUP BY clause, maybe?

SELECT qryActivitySelectMonth.Branch, Max(qryActivitySelectMonth.Day) AS MaxOfDay, qryActivitySelectMonth.Month, qryActivitySelectMonth.Year, qryActivitySelectMonth.Pipeline, qryActivitySelectMonth.PipelineValue
FROM qryActivitySelectMonth
GROUP BY qryActivitySelectMonth.Branch, qryActivitySelectMonth.Month, qryActivitySelectMonth.Year, qryActivitySelectMonth.Pipeline, qryActivitySelectMonth.PipelineValue
ORDER BY qryActivitySelectMonth.Month, qryActivitySelectMonth.Year;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[hansretallick]

Sorry Leslie, but it still returns all the days in the month, so it's not quite right yet.

Thanks
Hans

 

[lespaul]

So you want the max day for each month for each branch?

leslie

 

[hansretallick]

Yes, that's correct

 

[lespaul]

whats the SQL for the original query? you really don't need to break out the month in order to get the information you need. it may be easier to get what you want if you describe the TABLES that contain the information, some sample data and your expected results from the table information.

thanks,
leslie

 

[PHV]

You wanted something like this ?
SELECT A.Branch, B.MaxOfDay, A.Month, A.Year, A.Pipeline, A.PipelineValue
FROM qryActivitySelectMonth AS INNER JOIN (
SELECT Branch, Max([Day]) AS MaxOfDay, [Month], [Year] FROM qryActivitySelectMonth GROUP BY Branch, [Month], [Year]
) AS B ON A.Branch = B.Branch AND A.Day = B.MaxOfDay AND A.Month = B.Month AND A.Year = B.Year
ORDER BY A.Month, A.Year

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hansretallick]

Thanks PH, but this SQL just comes up with an error message 'syntax error in FROM clause'

Leslie. The tables are Activity and Branch. The reason that I extracted the month part of the date is that there is other data in the Activity table, and most of the rest of the data is the SUM of the four (or five) records for any given month. The pipeline data has to be treated separately as it is just the last date figures we need in any given month. These two data streams are then combined again for the report.

The first query has the following SQL:

SELECT tblActivity.ID, DatePart("d",[DateRef]) AS [Day], DatePart("m",[DateRef]) AS [Month], DatePart("yyyy",[DateRef]) AS [Year], tblActivity.Branch, tblBranch.BranchName, tblActivity.Valuation, tblActivity.SoleListing, tblActivity.Value£SL, tblActivity.ValuePSL, tblActivity.MultiListing, tblActivity.Value£ML, tblActivity.ValuePML, tblActivity.GrossSales, tblActivity.NetSales, tblActivity.PriceReduction, tblActivity.ATV, tblActivity.Withdrawal, tblActivity.Stock, tblActivity.Exchange, tblActivity.ExchangeValue, tblActivity.Pipeline, tblActivity.PipelineValue, tblActivity.FSRefer, tblActivity.FSSignUp, tblActivity.AppReg, tblActivity.Notes
FROM tblBranch INNER JOIN tblActivity ON tblBranch.ID = tblActivity.Branch;

The query I am having problems with then reads the first one (above) to extract and (hopefully) give the pipeline figures for the last record in the month.

I would like the results to show for any given month the month, year, branch and the latest pipeline figures (ie for the last record in the given month).

I'm sorry this is so complicated - I initially used the Maxof to get the pipeline figure, and that worked perfectly, but unfortunately the maximum figure isn't necccessarily the last figure in the month, so that was no good.

Thanks for all of your efforts

Hans

 

[PHV]

Sorry for the typo:
FROM qryActivitySelectMonth AS [!]A [/!]INNER JOIN (

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hansretallick]

WOnderful, PH. It works perfectly now. Many thanks for your help.

Hans