Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2015
    Posts
    7

    Question Answered: Query - Divide Rows.

    I'm really newbie on access and I don't know nothing about VBA or code lines.

    I need to divide some rows. For example:


    Field 1 Field 2
    50
    10
    30


    I want to divide 50 per 10, and the result appear on the Field 2. By the way, the first row of Field 2 must be blank (because we have nothing to divide).

    People would say, better do this in excel. I totally agree. But the point is: I need to do this every month, and every new month has a new value. And to finalize: I must take an average about last 24 months.

    What is the better way to do this?



    PS: It's my first post on the forum. If something is wrong I'm sorry.

  2. Best Answer
    Posted by healdem

    "the database 'way' of doing this sort of thing is different to the spreadsheet way. to compound this Access is not really a database, its a front end tool.

    unlike a spreadsheet data is stored in discreet parcels (call 'em rows if you prefer), in a spreadsheet all the data is visible frotk eh same file. in a db only the data you specify is visible.

    so what it comes down to is how you define what data should be visible at anyone time, and thats down to queries / views and so on. having defined what data is visible then you cna define how you want that data presenting (be it a report or form or mix).

    but there is not enough information to give a solution to your problem. what I suspect is that you want to run queries based on period ends, comparing last month with this month and so on. thats dooable in Access / db, and you wouldn't store the data you'd regenerate it as required. one fo the cardinal rules in the db world is not to store derived data. there are times when you can ignore that rule (eg if you have a lot of data and the response time is badly affected or if the data is static and wqont change and you don't want to pay the performance penalty of regenerating static data.

    bear in mind that in the db world there is no concept of order or sequence UNLESS you specify it. so you need to deisn in that precendence if its significiant. store dtaes or datetime values in a datetime datatype. and you cna use the inherent date time functions

    without knowing more of what you are trying to acheive Im expecting your query to JOIN to the same table
    extract the values required, perfom some basic maths.

    the JOIN will be soemthign like

    Code:
    select CM.aValue, PM.aValue, CM.aValue / PM.aValue  as Ratio from mytable as CM
    left join mytable as PM 
    on TM.adatecolumn....
    the prcise join condition would depend on the detail of your table design, which we don't know

    the as CM / as PM bit is a way of aliasing a table
    I used CM for the currentmonth and PM for the PreviosuMonth. in this context CM refers to the month you are currentl;y processingn
    the reason for the left join is that sometime you are going to get no previous months data, and in that event you will get a NULL value for the previous month(s) and Ratio

    of course what you could do is pump this data out to excel if yopu and your users are that used to using spreadsheets"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    No VBA needed, put this in a query:

    select [field1], [field1]/50 as Field3 from table

  4. #3
    Join Date
    Jul 2015
    Posts
    7
    Click image for larger version. 

Name:	Capture.PNG 
Views:	4 
Size:	11.9 KB 
ID:	16426


    For example: 0/1600 and the result on the Field Result at the second row.
    after, 0/0 and the result on the field Result at third row.
    and, 800/0, and the result on the field result at fourth row.
    and going, row by row...

    get it ?

  5. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    That's not how access works.
    It works with everything in 1 row. other rows are ambiguous.

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the database 'way' of doing this sort of thing is different to the spreadsheet way. to compound this Access is not really a database, its a front end tool.

    unlike a spreadsheet data is stored in discreet parcels (call 'em rows if you prefer), in a spreadsheet all the data is visible frotk eh same file. in a db only the data you specify is visible.

    so what it comes down to is how you define what data should be visible at anyone time, and thats down to queries / views and so on. having defined what data is visible then you cna define how you want that data presenting (be it a report or form or mix).

    but there is not enough information to give a solution to your problem. what I suspect is that you want to run queries based on period ends, comparing last month with this month and so on. thats dooable in Access / db, and you wouldn't store the data you'd regenerate it as required. one fo the cardinal rules in the db world is not to store derived data. there are times when you can ignore that rule (eg if you have a lot of data and the response time is badly affected or if the data is static and wqont change and you don't want to pay the performance penalty of regenerating static data.

    bear in mind that in the db world there is no concept of order or sequence UNLESS you specify it. so you need to deisn in that precendence if its significiant. store dtaes or datetime values in a datetime datatype. and you cna use the inherent date time functions

    without knowing more of what you are trying to acheive Im expecting your query to JOIN to the same table
    extract the values required, perfom some basic maths.

    the JOIN will be soemthign like

    Code:
    select CM.aValue, PM.aValue, CM.aValue / PM.aValue  as Ratio from mytable as CM
    left join mytable as PM 
    on TM.adatecolumn....
    the prcise join condition would depend on the detail of your table design, which we don't know

    the as CM / as PM bit is a way of aliasing a table
    I used CM for the currentmonth and PM for the PreviosuMonth. in this context CM refers to the month you are currentl;y processingn
    the reason for the left join is that sometime you are going to get no previous months data, and in that event you will get a NULL value for the previous month(s) and Ratio

    of course what you could do is pump this data out to excel if yopu and your users are that used to using spreadsheets
    Last edited by healdem; 07-07-15 at 07:24.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Jul 2015
    Posts
    7
    Thanks for everyone who answered me. I decided to solve this at Excel. Anyway, thanks !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •