Need to create small macro that calculates values of a column using simple excel formulas!

[hacerz]

What do you mean by 'previous' row - the one immediately above and adjacent to current row or could be any row above the current row?

In Access there is no 'cell reference' - obtaining data from another record of the same table requires nested subquery - the referenced link has example - or domain aggregate functions.

The previous row is the next older record. eg: today i got the data file with one new row for number 1002, so the calculation will be only for that 1002 number and it will be between the today's row with the yesterday's row(if there was any) or else it may be day before yesterday's untill you find the older record. I mean you can easly see the previous record for that number 1002 just by sorting the number column with that 1002 number.

Hope you get this. Please let me know if nay questions.

fingers crossed. I got a deadline for this which is like a week or so

[ssanfu]

You didn't answer my question...

Open your "Template.xlsx" file
Move down to row 66
If the entry in cell B67 is 1006, what do the formulas look like?

See attached template. Please fill in the formulas.

[hacerz]

Does this mean that the date (Cax Time) will be increasing? If the last row date was 12/31/2014, the next day new rows are added that will be 1/1/2015 and not 11/12/2014? Or can the dates for the new rows be any date?

In Data.xlsx, there are 65 rows for "Number" = 1002. The (first) column "Segment Data" has 100 entered, then there are formulas.
When "Number" = 1006 (54 records), what happens to the (first) column "Segment Data"? Is 100 entered again or do the formulas continue?

Can you provide a "Template.xlsx" example with "Number" = 1002 and 1006 rows so I can see how the formulas work when "Number" column changes?

Hope you got my reply to your question.

Thanks

[ssanfu]

I saw your post #14, but it didn't answer my questions.

See my post #17. Please fill out the formulas for the two "numbers" 1003 & 1006. (See attachment post #17)


At this point, I'm stuck. I don't know what to do when the "Number" changes (ex: 1002 to 1006). I need to see how you enter/copy the formulas down the worksheet.

[hacerz]

You didn't answer my question...

Open your "Template.xlsx" file
Move down to row 66
If the entry in cell B67 is 1006, what do the formulas look like?

See attached template. Please fill in the formulas.

Sorry about that. For some reason I am able to see your reply now and was trying to refresh the page but couldn't see your reply on feb-17. Not sure may be I have to sign out and sign back in.

I see the file has 1002 sorted by date in ascending order from the top, as the first date in the first row for 1002 is the oldest. Hence the formula starts giving us the value from the second row.
Now coming to the 1006 number. I see the row you mentioned B67 is the latest(new) record for the number 1006 because that date is the latest and the oldest date is at the very last row which is Row '80' for number 1006.

so to summarize , I am not sure if I sorted out the number 1002 in ascending from top to bottom and in descending for 1006 from top(Row 67) to bottom (Row 80). since both numbers are sorted in reverse order to each other, the formulas will be reversed.
General rule of thumb is the calculations always starts from the oldest records. so the first calculation for number 1006 will start from row 79 and row 80. for eg: the first formula will be for the first calculation of number 1006 will be
(i) =IF(B79=B80,E79-E80,"") -- value for "X Delta" column

if you notice, the calculation is between new record and its previous record. so its almost like doing a calculation like 10-5 and not 5-10 where 10 is the new record and 5 is its previous record. so when you start with row 79-80 then you move to 78-79 then 77-78 and soon.

Now, its your choice how you want to sort out the excel file based on the date. either desc or Asc at your convenience. if you reverse the current order for 1006 then the formula will be
=IF(B68=B67,E68-E67,"") because the first oldest row for 1006 will be Row 67 and its latest record will be Row 68.

Hope I answered your question.

Thanks

[ssanfu]

OK, I was hoping you would do what you do when you add new records. Do the sort, add the formulas....... Then I would have a data set to try and match.

So, referring to "TemplateSS2.xlsx", are the formulas for the ORANGE shaded cells correct??

[hacerz]

OK, I was hoping you would do what you do when you add new records. Do the sort, add the formulas....... Then I would have a data set to try and match.

So, referring to "TemplateSS2.xlsx", are the formulas for the ORANGE shaded cells correct??

Ya, the formulas looks OK. the only thing is for the first row of 1006 , you entered the formulas comparison with the 1002 last record . I would suggest leave the first row's for all the numbers , let it be empty. Thats something I will get find out what initial values to start with but from what I know first rows of each and every number can be empty , the second row will have the calculated values. so in this instance, lets remove the formulas for first row of each numbers. I know I have entered the formulas for first row of 1002 and I did it because I sorted the excel sheet based on the date and the lowest number so I thought lets start with the first column as there is no zeroth column.

Thanks.

[ssanfu]

You DO understand that the formulas are for Excel only?

In Access, there will be UDFs to do the calcs OR the results of the calculations (formulas) will be stored in a table.
UDfs used in a query will (might) take a lot of time, while storing the values on the table means you will (might) have to run the calcs every time you do an update.

[rpeare]

I do not see a reason to involve excel at all (as others have mentioned) Here's a sample of doing what you want to do with queries only:

hacerz.zip

I didn't do all the formulas but nothing in there seemed that complex. The real trick is getting the previous date to the current one within the same group (column b).

[hacerz]

You DO understand that the formulas are for Excel only?

In Access, there will be UDFs to do the calcs OR the results of the calculations (formulas) will be stored in a table.
UDfs used in a query will (might) take a lot of time, while storing the values on the table means you will (might) have to run the calcs every time you do an update.

I am guessing UDF is user-defined functions. so if I got your point is there are two ways one with UDF used in a query (which takes lot of time)and other I am didn't get that. If any solution which makes the task easy , i would go with that and not with the one which is taking much time.

Please let me know if I didn't get you point.

Thanks

[hacerz]

I do not see a reason to involve excel at all (as others have mentioned) Here's a sample of doing what you want to do with queries only:

hacerz.zip

I didn't do all the formulas but nothing in there seemed that complex. The real trick is getting the previous date to the current one within the same group (column b).

I saw the db file and I tried to add a row manually to the tblimport table at the last column for 1006 number. i typed in and then save the row and went back to the qryFinal results and found the new row entered with only 3 column results and I manually calculated there values and looks ok to me. Will go home and check with the excel formulas.

Obviously access is not my thing and Queries . The actual data I need is segment data 1 and 2 along with other columns.

1) Can you please give me the queries for the other columns(7) when u get a chance. I am sure it would be easy for you.
2) Also the data file will be in excel and i get them other people when they enter new rows. So how what would I do to bring those new rows in to the access db. Do you want me to import the whole file each day or have it linked to the db with the built-in feature. so everytime I open the db i see the new rows and hopefully the new calculated values should be popped up. Please let me know.

I want to thank you for your effort. Really appreciate it. Was struggling with this for long time. tried many ways but couldn't get to know the cell referencing in access.

Another thing I want to mention is,
Note: The dates can be same, for instance, today there might be 3 new rows entered for the number 1006 with different values for the other columns but the date will be same because it was entered today. so is this db gonna find out the last entered row and the corresponding old row which is on the same day? please let me know.

Thanks

[rpeare]

1. You can follow the example of the first four columns I gave you, if you have trouble figuring out a formula copy parts of the formulas I gave you to a new field within the query and see what the result gives you. For a majority of your data you are checking the 'ok'/'discontinuous' field first which is what I do as well. The running sums you will have to do in a report, running sums are not really what access is built for and it will be very memory intensive and slow depending on how large your dataset is if you try to do it in a query.
2. How you get your data into your database is up to you. You can import the file every day which is the way I would do it, particularly if it's a cumulative file, if it's not a cumulative file you may want to consider just importing new records.

Your dates actually have a time stamp on them as well and unless you can have three entries in the same second (or even 2 entries in the same second) for any given day there is no real risk of butchering the order.

[hacerz]

1. You can follow the example of the first four columns I gave you, if you have trouble figuring out a formula copy parts of the formulas I gave you to a new field within the query and see what the result gives you. For a majority of your data you are checking the 'ok'/'discontinuous' field first which is what I do as well. The running sums you will have to do in a report, running sums are not really what access is built for and it will be very memory intensive and slow depending on how large your dataset is if you try to do it in a query.
2. How you get your data into your database is up to you. You can import the file every day which is the way I would do it, particularly if it's a cumulative file, if it's not a cumulative file you may want to consider just importing new records.

Your dates actually have a time stamp on them as well and unless you can have three entries in the same second (or even 2 entries in the same second) for any given day there is no real risk of butchering the order.

Ok. I will try the other columns based on the queries you already written. I would have to see where is the cell referencing made to calculate it based on the previous row because the SUM is also based on the previous row. I know the fact that you are using the other query to generate the previous rows. will see the column names and put in the queries for other columns and "SEGMENT Data" column( Main columns that I needed).

I will try linking the excel file meaning it will read the excel file whenever we open the DB.

Question:
(I) Another thing, if there are three rows in one day then they will enter it as three row
1) today 7am
2) today 8am
today 8.01am or may be 8Am ( didn't come across that part like having the same timestamp) - I actually not sure if they are entering the timestamp manually or using system generated time formula in excel. But anyways the time stamp should be different even if there are multiple new rows for the same number in one day.
3)today 9AM etc

(II) I see there is another extra column "colC" which just has the dates. so are you using just the date without timestamp in colc to get the previous rows? because if thats the case then the above scenario for multiple rows in one day for one number will not work, meaning we wont be able to find out the latest and previous record since there is no timestamp. Please let me know how colC is being used.

(III) You said SUM can be generated using the reports....can't I write a query for sum including the cell referencing technique you have used to get the SUM? because this is not normal sum , this again uses the previous row concept if you see the excel formula for "segment data" column.

Thanks in advance.

[rpeare]

Item I and II:

I'm using a formatting property of colc to get just the date with no timestamp. If you go to the query design and click the COLC row for the 'current' set of data then click properties in your query design window you can see it says 'short date' you can remove that formatting property and it will show the full date/time. The data is sorted by the full date/time not just the date part of the field.

Item III

Running sums and sums are different things, running sums in queries is generally a bad idea, you can do it using domain functions (DSUM in this case) but it's a very bad idea. Let's say your dataset has 1000 rows, every time you use a domain function your query is evaluating the result based on all 1000 rows, in this case it's the worst possible scenario in that you are showing each row as a unique record and if you had 1 dsum calculation for each row you would effectively square the number of records the database has to process to give you your results (1000 initial records x 1000 records processed in a domain function for each row or 1,000,000 records to process). Your spreadsheet has 2 running sum columns which would double this to 2,000,000 possible calculations.

However, if you perform your running sums on a report, the reports are built for the running sum calculation and will be a hell of a lot quicker.

[hacerz]

Item I and II:

I'm using a formatting property of colc to get just the date with no timestamp. If you go to the query design and click the COLC row for the 'current' set of data then click properties in your query design window you can see it says 'short date' you can remove that formatting property and it will show the full date/time. The data is sorted by the full date/time not just the date part of the field.

Item III

Running sums and sums are different things, running sums in queries is generally a bad idea, you can do it using domain functions (DSUM in this case) but it's a very bad idea. Let's say your dataset has 1000 rows, every time you use a domain function your query is evaluating the result based on all 1000 rows, in this case it's the worst possible scenario in that you are showing each row as a unique record and if you had 1 dsum calculation for each row you would effectively square the number of records the database has to process to give you your results (1000 initial records x 1000 records processed in a domain function for each row or 1,000,000 records to process). Your spreadsheet has 2 running sum columns which would double this to 2,000,000 possible calculations.

However, if you perform your running sums on a report, the reports are built for the running sum calculation and will be a hell of a lot quicker.

1) So basically you are not using the COlC in any calculations or queries its just for the view purpose you did that correct?

2) I would rather go with the efficient way to achieve my results. How would I do the SUM though the reports along with the cell referencing?which is done not just for that row, if u see the formula for "segment data" column, you see sum of a value from another column and segment data's previous value. So can we acheive this using the reports you mentioned? Please let me know

Thanks in advance

Note: Download my Game Apps on Google Play Android, Apple(IOS) and Amazon Android Store. These are my apps. I am good in programming but not much in database
1)Save the Crazy Balloon (very interesting and challenging kind of like flappy bird) - improves your concentration
2) Save the Crazy Witch - A 3D game to save your witch.

Just search for them in your app stores with exact spelling.

thanks