Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37

    Hi accesstos,
    What is this function? it is like : if ([Multiplier] = "ClassFactor") is true then -1 else 0
    I tested it for 2 elements & is working fine but I've to multiply by (-1) because the results are in negative.
    I'll complete the equation & see.
    Thank you

  2. #17
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks Minty, I'll try to upload a sample of my database

  3. #18
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by ashraf View Post
    Hi accesstos,
    What is this function? it is like : if ([Multiplier] = "ClassFactor") is true then -1 else 0
    I tested it for 2 elements & is working fine but I've to multiply by (-1) because the results are in negative.
    I'll complete the equation & see.
    Thank you
    That's right ashraf! This expression (not function) multiplies the logical condition ([Multiplier] = "Class Factor"), which result can be 0 or -1, with the value of the corresponding field ([Class Factor]). Only one of all conditions can be TRUE. And yes, I just failed to state that the TRUE condition equivalent to -1.
    Therefor, the finally expression can be:
    Code:
    Result: [Qty]*-[SelFactor]
    Or
    Code:
    Result: [Qty]*ABS([SelFactor])
    Good continuing!
    John

  4. #19
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Thanks accesstos,
    It's working but slow, with iif takes 3 seconds to move from 1st column to the last one (by using "End" Keyboard button) & with this expression it takes about 12 seconds.
    I think iif will not calculate the rest on expression if True but this expression must check all condition on each record.

  5. #20
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    Minty
    Below image is a sample of the DB structure it may explain my design.
    Attachment 37132
    Attached Thumbnails Attached Thumbnails Capture.jpg  

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Interesting picture, but can you provide a copy of your database with a few records so we can actually see the implementation.
    It would also be helpful if you provided some instructions to highlight any issues (eg speed etc).
    Last edited by orange; 01-26-2019 at 09:13 AM. Reason: spelling

  7. #22
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    ashraf,

    You may split the nested IIFs in two fields and add them in a third, but, obviously, this is a spreadsheet-way approach.
    In my opinion, you have to transpose the columns of factors to rows (one factor per record). Perhaps that increase the rows of the datasheet but, in Access, that would be a much more efficient and normalised data structure.

    Regards,
    john

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to multiply by FactorX yet FactorX is a calculated value - how are the factor values derived?

    This is making even less sense after providing image.

    It appears each factor can associate with multiple GroupMultiplier but each GroupMultiplier has only 1 factor. Why isn't there a field in GroupsMasterList for Factor?
    Last edited by June7; 01-26-2019 at 05:05 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #24
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    June7,
    Factors are expressions depend on the values of Item(record) specifications like material, Unit, .. etc
    e.g. if the unit is in meter(mt) and the material is SS the factor03 will be the weight per linear meter of the Stainless Steal (SS) item (value from Density table) and "actual quantity" will be the Qty*Factor03. if the unit is in DI(Dia Inch for pipes) then Factor04 will be the pipe weight per linear meter from pipe standard table.
    So Factor value is depend on the item specification and I can't use it as a Group(multiplier)

  10. #25
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    OK Orange, I'll try to put it in a sample database in access

  11. #26
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    John,
    As I explained to June7 about the factors, it depends on the item specifications and I can't transpose it to item rows because groups(multiplier) also contains sub items but to simplify the example I din't mention.
    Thank you.

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the factor used for each GroupMultiplier depends on specification inputs material and unit? Sounds like need a VBA custom function.

    Not following why you need 10 Factor fields calculated. Input parameters to a function and spit back the appropriate factor.

    Now I don't understand the relevance of Group identifier.

    And now you mention a 'pipe standard table'.

    Again, if you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #28
    ashraf is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    37
    accesstos, I forgot to mention that, I tested your idea for separating the iif statement into tow fields & it is working fine.
    Even it is a spreadsheet approach, it is fast in execution.
    Thank you.

    Quote Originally Posted by accesstos View Post
    ashraf,

    You may split the nested IIFs in two fields and add them in a third, but, obviously, this is a spreadsheet-way approach.
    In my opinion, you have to transpose the columns of factors to rows (one factor per record). Perhaps that increase the rows of the datasheet but, in Access, that would be a much more efficient and normalised data structure.

    Regards,
    john

  14. #29
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by ashraf View Post
    accesstos, I forgot to mention that, I tested your idea for separating the iif statement into tow fields & it is working fine.
    Even it is a spreadsheet approach, it is fast in execution.
    Thank you.
    You are welcome!

    Good luck with your project!

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

Similar Threads

  1. Find Function and Return Value
    By Whughes98 in forum Queries
    Replies: 5
    Last Post: 10-09-2018, 04:17 PM
  2. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  3. VBA Return to the selected record
    By devcon in forum Forms
    Replies: 7
    Last Post: 02-17-2012, 09:17 AM
  4. Function to return a set of records?
    By vicrauch in forum Access
    Replies: 2
    Last Post: 07-12-2011, 08:27 AM
  5. Replies: 1
    Last Post: 06-23-2010, 09:45 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