Replies

I don’t think dateserial is a MS SQL 2008 function. If so, this would have been a great solution. It looks like there have been some new functions added in SQL 2012, but we’re not there yet.

Xircal, Thanks for your help. However, I followed the steps above, but all the settings were as they should be – no changes to save. On the Indexing Service, it said that indexing was started (and the start button was grayed out). However, when I have the Windows Search application open, I’m still getting the yellow bar that Windows Search isn’t running….

The data table didn’t translate well when I posted the response – below is the data referred to above:

Rev Due Date Function_Names Running Total
2011 06 Power Production 1
2011 07 Power Production 2
2011 08 Power Production 3
2011 09 Power Production 12
2011 10 Power Production 14
2011 10 Power Production 15
2012 06 Power Production 16
2013 06 Power Production 17
2014 06 Power Production 18

I am getting close to the solution – the next issue (and I hope last issue) is grouping the data by months. Below is the code and output where I stand now. I need to be able to group the data by month so the output shows 1 record per month.

q Counts Running TotalRev Due DateFunction_NamesRunning Total
Customer Service

Power Production

Transmission & Distribution
2011 06Power Production12011 07Power Production22011 08Power Production32011 09Power Production122011 10Power Production142011 10Power Production152012 06Power Production162013 06Power Production172014 06Power Production18

As you can see, there are two records for 10/2011. I need to group this into one record

CODE for above results:

SELECT [q counts].[Rev Due Date], [q counts].Function_Names, Sum((select Sum([Number of Milestones]) from [q counts] nn where [nn].[rev due date] <= [q counts].[rev due date] and [nn].[Function_Names] = [q counts].[Function_Names])) AS [Running Total]
FROM [q counts]
GROUP BY [q counts].[Rev Due Date], [q counts].Function_Names;

As a bonus question, I would also like to ultimately create a table that has the data in a table with all months for charting purposes. I would appreciate any suggestions to accomplish that.

Thanks, David

I think the other problem I am having is that I am trying to do two counts (monthly and running total) in the same query – I seem to recall that a query can only have one count field at a time – is that right? If so, I may have to break this out into multiple queries. Is that a correct assumption – anyone?

Amazing what a quick search will do – I actually (for once!) found and implemented a solution. I still appreciate the help everyone has provided in the past (and probably in the future!)

John, sorry for the late reply – I got off on some other projects for a bit. That is a great idea – I should be able to do that without too much effort (I’m still a novice at Access, but a dangerous one at that!). Thanks for the suggestion

Thanks for your quick response. I haven’t worked with macros much or with VBA at all, but there are a couple people I work with that looked at the responses and are helping me work with my live data.

I appreciate your help!

I’ve tried that. The problem is that the XP machine does not have the Vista print drivers for the HP4L (and HP doesn’t provide the drivers for download).

Hans, Thank you. I have not used modules before (I am a dangerous novice). A couple of questions about this: It is saving the data to a table. Is there a way to do this and have the results in a query? The reason being that the base table is updated monthly (completely replaced from an external source). Also, can the input into the module be a query? The base data has dates YYYYMMDD, but I create a year field based upon different criteria (it may or may not be the same year). I have a query that provides the base year, but not a table.

While I don’t understand everything in the module, I can see enough as to how it works and how I can use it. I appreciate your help!

One last question, I am using Access 2000 – Will this work with older versions of access?

David

Thanks, I joined a while back and have searched the forum to solve many problems. However, this is one I haven’t been able to resolve yet.

Not being a database “Expert”, I’ll use an example..

The base table lists ID, date aquired, description and cost:

B0342, 2008, Honda, 23000

I need to create a query that adds an annual record:

ID, Date Aquired, Year, Cost, Annual Expenses
B0342, 2008, 2008, Honda, 23000, 1400
B0342, 2008, 2009, Honda, 23000, 1450
B0342, 2008, 2010, Honda, 23000, 1500
B0342, 2008, 2011, Honda, 23000, 1300
B0342, 2008, 2012, Honda, 23000, 1350

The Annual Expenses for past years will be pulled from another table that has ID, Year, Annual Expenses. The costs for future years will be calculated based on a % of the cost (I can do that part with iif statements).

Does that help explain the question?