Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7

    Excel To Access calculations

    Sorry for the rookie question.
    Two calculations issues.
    1. In Excel I'm able to do a uniformity calculation with (Smax-Smin)/Ave(S1-S9)*100, works great. In Access I've built a query and can get the Smax and Smin values of the data set but for the life of me I'm unable to figure out the rest to get the final uniformity in my Access table.


    2. In Excel I have a lookup function, Lookup (Temp,{16, 17, 18, 19},{0.84,0.863,0.888,0.913}). The results of this lookup ("C") is used in another calculation, SNR=(Sm/Nav)*C. Any thoughts on how I can put this one together in an Access table?
    Thanks

  2. #2
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    1. You can do calculations, just need to reference the field names, not the cell names from excel. Explain the calculations you need.2. Lookup in Access is something like:
    DLookup("[YourField]", "YourTable", "[CriteriaFieldinTable] = " & Forms!YourFormName!YourFieldName)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    data is stored differently in access compared to excel - I'm quite sure access can provide the same answer, but it is unlikely to be a case of simply swapping an access function for an excel one.

    1. you need to clarify what S1-S9 is
    2. It may be that Bulzies suggestion will do the trick however that dlookup returns on an exact match whilst the excel lookup vector you have provided will return against exact match or highest value below. So the answer depends on whether you are working to an exact match or not. You could also just as easily use a nested iif for non exact matches or choose function if it is an exact match and the data is fixed in both cases.

  4. #4
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7
    Calc 1. (S1-S9) (field names) are 9 different measurement values of signal intensity of a Fast-Fourier transformed image. The calculation subtracts the minimum value from the maximum, divides the result by the average of the 9 values and multiplys that result by 100 for a uniformity percentage. The S1-S9 values are entered into a form and a query provides the Smin and Smax values. It's getting theses (Smin, Smax) values back into the main table/form for inclusion in a simple calculation that keeps blowing up.
    Calc 2. In the Lookup, the first grouping of numbers {16. 17. 18,,,} represents the temperature of a CuSo4 phantom in Celcius. The second grouping { 0.84, 0.863,,,,} are conversion factors based on the temperature. In the user form the temperature of the phantom is entered and the conversion factor is plugged into a simple calculation producing the Signal to Noise ratio of the test phantom ((SIGmeans/NOISEstd-dev)*C=SNR.

    I've not done any scripting in Access (haven't even looked into it), not to shabby at it in Perl or TCL. If they're the same, I reckon I have to look into that for the Lookup.
    Thanks for the responses and any suggestions/cheats you can provide.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    (S1-S9) (field names) are 9 different measurement values of signal intensity of a Fast-Fourier transformed image
    not really explained with the techno babble. OK so there are 9 fields called S1, S2 etc - in Access these would normally be stored vertically one 'S' field per record (almost certainly in a separate table) and you would use a groupby query to average them. Sounds like this is not the case, so you would need to use

    (S1+S2+S3....)/9

    Remember Access is not a glorified Excel, it is a different animal completely - excel stores and displays data horizontally, Access stores data vertically and uses forms and reports for display purposes.

    I understand what Lookup is and my question was more about the expected result. I probably didn't phrase my question well enough. I was asking if the Temp value is always going to be 16,17,18 or 19 or could it be 17.8 or 18.2? because if it can, then dlookup won't work because you require 17.something to return 0.863 18.something to return 0.888 etc. whereas dlookup will only return an exact match on 17, 18 whatever. But there are ways round it, just need to know what it is you actually require.

  6. #6
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7
    Ajax, Bulzie asked "Explain the calculations you need." As a medical equipment engineer, that's the only way I know how to "explain".
    Calc 1. I have the values for Smax, Smin, and Ave. Smax and Smin from a query containing 11 fields. First 9 are S1, S2,,,S9. The Smin is field 10 and entered as "Minimum Value: Minimum([Sig1],[Sig2],[Sig3],[Sig4],[Sig5],[Sig6],[Sig7],[Sig8],[Sig9])". Smax is field 11, the same as 10 but "Maximum Value: Maximum" of course and all are associated to a proper table. My problem is getting those values (Smax and Smin) into a simple calculation contained in the parent table.

    Calc 2. The temp values are absolute whole numbers. The English translation for the temp conversion would be "If the phantom temperature is 16 degrees Celsius then the conversion factor is 0.84."

    I really appreciate y'all sticking with me on this. This whole Access thing has been one heck of a learning experience. I'd really rather stick with the Nuclear Physics.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Bulzie asked "Explain the calculations you need."
    Sorry I thought you were answering my question

    Calc1 - the suggestion I made

    (S1+S2+S3....)/9

    for the avg part of your calc should do

    and the dlookup will work for calc2

    not sure what you mean by simple calculation contained in the parent table. although tables can store some basic calculations, the normal advice is to do them in queries. Do you mean store the calculated value?

    As said before, the way you have your data stored is the excel way, not the access way.

    if you had a simple table

    tblReadings
    ID autonumber
    Item text
    Reading number

    the calculation would be

    SELECT Item, (max(Reading)-min(reading))/avg(reading)*100 as uniformity
    FROM tblReadings
    GROUP BY Item

  8. #8
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7
    Ajax, thank you. I will try to wrap my head around your suggestions. I'm sure you are trying to simplify your instruction enough for me to understand it and for that I am grateful.
    I guess I can now understand how a fisherman feels when I try to explain the reaction of a hydrogen proton, in a magnetic field, when excited with a RF frequency. "HUH"
    I will work on your suggestions and let you know.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how a fisherman feels when I try to explain the reaction of a hydrogen proton, in a magnetic field, when excited with a RF frequency
    - but put it in the context of the proton is a fish, the lake is the field and frequency the waves on the surface generated by the wind and his reaction might be "AH"

    in the db world that mystery is called 'normalisation'. In simple terms it means a) store data only once, b) don't store calculated values and c) don't build data into the structure

    Using your example in your original post you appear to have broken rule c, are trying to break rule b and probably also breaking rule a

    Per the quotation above your table might look something like

    tblReactions
    ReactionPK autonumber - to uniquely identify the record
    ExperimentPK long - link back to experiment table for experiment profile
    MagneticFieldFK long - link to back to a magnetic field table to profile the field
    RFFrequency number - or might link back to a frequency table to profile the frequency
    ProtonID long - to identify or enumerate a specific proton, unlikely to link back to a proton table since I'm guessing they have a 'one time use'. Might in this instance to be able to use the primary key instead.
    Reaction number - the result

    Why normalise? It makes calculations easier and the design more flexible. Simple example is your S1-S9 (which breaks rule c). What if in another experiment, you only need 5 readings or 15? you need to add or remove columns and change all the formulae. With normalised data, you don't need to make any changes at all - just add fewer or more records for the different amount of data.

    Although data is stored vertically, it can be displayed horizontally - for example a cross tab query will present the data much the same way as you see it in a spreadsheet, or you can use pivot tables/graphs and there are a number of other methods in forms and reports.

    Also, with the data stored vertically in this table, you can easily combine or compare results from different experiments, same magnetic field or same magnetic field, different frequencies etc.

  10. #10
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7
    Now I'm the fisherman!
    Just some insight as to what I'm trying to accomplish. I've had an Excel spreadsheet for years that my engineers have used to record the results of imaging coil QAs taken during site installations. Simply for historic record keeping at the time of installation.
    Now comes the government. Due to (leaked) pending FDA requirements, this spreadsheet concept has to be ported over to Access so that there is historical record keeping through the end of life of the product. Also anticipated are the reporting capabilities of Access. The company brain trust anticipates the need for reports based on site record/all coils, coil record/all sites, date ranges, you get the picture. Individual database copies will be maintained by each engineer so overall size won't be an issue.
    I currently have the 25 unique tables created, data entry forms for each and am now in the process of, you guessed it, doing the calculations. All this will be driven by a switchboard with reporting requirements being selectable by querys.

    Now that I've bored the snot out of you.
    Thanks to you, your patience and suggestions, I can see the light at the end of the tunnel and it's not some idiot with a candle in a cave.
    I believe I can accomplish my needs through "make query", relationships and all the printouts I've made of your suggestions.
    To that end, I will say Thank You again. Now on to the lookup calc. Wish me luck.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by dnixon View Post
    ...
    I guess I can now understand how a fisherman feels when I try to explain the reaction of a hydrogen proton, in a magnetic field, when excited with a RF frequency...
    I would be interested in hearing that story. It's intriguing.

  12. #12
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7
    Peruse the following for some light reading.

    http://www.drcmr.dk/mriinbrief

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Nice, I always wondered why the word resonance is used in MRI. It seems a piano tuner and an A/C service repairman are not going to be qualified to get an MRI machine performing in tip top shape.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Wish me luck.
    Good luck

    Now that I've bored the snot out of you.
    Not at all - I like a challenge. I'm an accountant - now that's really boring. Although I do have a friend who is a lion tamer (if you know Monty Python) - he trains animals appearing in film/ads/etc.

  15. #15
    dnixon is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    7
    Jumping into Mr. Peabody's WABAC machine, it use to be know as NMR - Nuclear Magnetic Resonance.
    Problem with that was patients thought they were going to be bombarded with atomic radiation.
    I believe it was Leon Kaufman Ph.D that, while working at UCSF-RIL, coined the phrase Magnetic Resonance Imaging. But then again, he use to claim a lot of things.
    I worked up at RIL for a few years, early to mid 80s. Talk about a bunch of pocket protectors!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 30
    Last Post: 09-30-2015, 10:58 AM
  2. Replies: 2
    Last Post: 06-06-2013, 08:34 AM
  3. Access Calculations
    By Rogue in forum Access
    Replies: 5
    Last Post: 03-17-2013, 11:18 AM
  4. complex calculations like in Excel
    By jacko311 in forum Database Design
    Replies: 2
    Last Post: 11-11-2009, 05:51 PM
  5. Calculations in Access
    By dominick in forum Access
    Replies: 0
    Last Post: 07-28-2009, 07:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums