Search results

Hi How can I select the first record of each year working with a table like this: ID, value, date 0, 30, 01/01/2000 1, 40, 03/02/2000 2, 20, 10/03/2000 3, 10, 02/05/2001 4, 20, 09/08/2001 5, 10, 01/02/2001 I'd like to get this result from my Query: 30, 01/01/2000 10, 01/02/2001 I've been...

Thanks for taking time to have a look at my problem! I have uploaded a sample. I have selected some cases which are giving me big headaches ! In the attached file there are three cases: a, b, c ('name' field). First case (a) has values between Jan-1992 and Aug-1993. The problem here is to...

Hi, I have a table similar to this sample: ID, Name, Date, P 0, Tom, 01/01/2010, 100 1, John, 01/02/2010, 80 2, Tom, 01/03/2010, 70 3, John, 01/05/2010, 10 4, Tom, 01/06/2010, 90 5, Tom, 01/07/2010, 60 6, John, 01/08/2010, 30 What I would like to do is to run a query which returns all fields...

Hi, I have been able to use tehNellie suggestion to workout the general case of my quarter updates. The problem is that I can't figure out how to deal the zeros. Here my code: UPDATE mytable AS t1 INNER JOIN mytable AS t2 ON (t1.IPD_Ref = t2.IPD_Ref) AND (t1.Month =...

Hi, Thanks for your answer. I read that post but I suppose that even if I use that approach to set up my monthly performance I still have the same problem to calculate the quarterly performance. In few words, my general question is if you think that I should use a function with the select...

OK, I found the mistake, in the second last line I should have:evaluate_s = value_f Now my test function is working fine. I suppose my function should work fine after I have implemented the 27 combinations! Still my 'general' question stands: is this the best approach? Thanks, S.

Hello, Background context: I have had another thread about calculating a recursive formula (monthly cumulative performance) and I am using a modified version of the solution provided here (post #23): http://www.access-programmers.co.uk/forums/showpost.php?p=1374081&postcount=23 My problem...

To be honest I'm not very happy with the horizontal data but I guess that there is a way around to transform it as I like (maybe using another Query?) For the IIF statement I have solved it, it works as follows: SUM(IIF(tbltest.den=0,0,1 + tbltest.num / tbltest.den )) AS SumOfPart Although it...

Yes, you are right. The problem is the 0 in Den. If I change the initial formula in: SUM(1 + tbltest.num /( tbltest.den +1)) AS SumOfPart It works well. I mean does not create the overflow error. I was trying to work with an IIF condition but it does not look like I am allowed to use it with...

That's great but two problems: 1) with my DB if I run the first block over all records in my DB I get an overflow error. If I include a WHERE condition between a range of dates, then it works fine and creates a pivot table. 2) This is pretty bad but: how do I combine the two SQL scripts that...

it is a Date/Time in my DB.

Answers: 1) it comes not sorted 2) the date is just formatted in that way in the Excel file but if you check the row data (click inside each cell) the format is dd/mm/yyyy. Sorry I should have removed the format from the cells.

I sorted the provided sample by date for you but if that could help in solving the problem I can sort the records by date before importing them in my Access DB. At the moment I have about 120,000 records but they will grow in number in future. Thanks again! S.

Hi, Thanks for the effort you all are making in helping me. You're right. I did not formulate well my request in my previous post. I meant to ask how to do it because I can't figure it out (even conceptually !! :banghead:). I have attached an Excel file data sample which includes two...

They are numbers and represent performance values of an element in time (monthly basis). I need the ratio between them but each month performance is evaluated based on the previous month performance (it's a cumulative performance) and the base month is January or the first month of record of...

I am back but I am still not sure that a query would solve my issue (in VBA or just SQL). I am probably missing the concept behind queries .. I am wondering how I can call a calculated value in order to calculate another calculated value using a query (i.e. to implement what I have described...

The table has 5 fields as follows: ID: Primary Key Name: String Date: Date in the format dd/mm/yyyy Num: Double Den: Double It might happen that Num and Den are 0 but not null. Is there a way around SQL in Access to implement this? Do you need more information?

OK, I understand the issue. I am thinking in the wrong way. No need to store my calculated values just use a query and do whatever I want with it. I will post my new code as soon as I modify it (just for the sake of completeness of this thread) Thanks

Hi, Thanks for your answer. I will try to make it as clear as possible :) I have a table in my DB, this table has the following fields: ID, name, date, num, den What I want to do is to create a new field which has the following value: If the month is January = 100 * (1+num/den) Else...

Hi, I have written a Subroutine in VBA 7 for Access 2010 that implements a recursive formula. In Excel the formula looks something like this: =IF(MONTH(B2)=1,100*(1+C2/D2),E1*(1+C2/D2)) Where the formula is applied for cells of column E The subroutine that I have written in Access works fine...