Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24

    Conditional Function in a query

    I'm new to access and have a question please.

    I have a table EZExtract with a few fields, some of which:
    ItemID
    DatePaidDate
    DateofBirth

    I want to replicate the whole table amending the ItemID fields based on the following conditions:
    If ItemID = 2
    Then
    Work out the age in Months (would be something along the lines of DateofBirth - DatePaidDate / 12)
    Then
    If Age in Months < 6 ItemID = EZ1000


    If Age in Months Between 7 and 12 ItemID =EZ1001
    If Age in Months Between 13 and 36 ItemID =EZ1002
    If Age in Months Between 37 and 132 ItemID =EZ1003
    If Age in Months >133 ItemID =EZ1004


    Any idea how I can do this please (again, i'm new to access).

    Thanks for taking the time to read my question.

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Need more info and things to think about

    This appears to be a one time effort. After all the records with ItemID = 2 are updated, there are no more records with ItemID=2 in table EZExtract (if we update EZExtract).

    Do you really need to create a new table just like EZExtract with just the ItemID fields different? This might be a design issue unless there is a valid business reason to do so. Duplicated values in different tables leads to data inconsistency over time.

    What needs to happen if new records are added to EZExtract in the future with ItemID = 2? Is this imported data or data entered via a form?

    Have you taken into consideration that in the future there might be more than one payment made, resulting in two (or more) DatePaidDate?

  3. #3
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Hi Davegri, thanks for taking the time to reply - I appreciate it.

    I don't need to create a new table as such just need the ItemID fields which are = 2 to be changed to the new ItemIDs, all ItemID's that are other numbers remain the same.

    It is a cloud based till system we have, the provider will not let me ODBC into it so I am setting up the system to send an email with all the data each night to me and then setting up Automatedemailparser software to extract the data from the email and enter it into the EZExtract table where I then need the database to convert the ItemID field into the correct ItemID's. I am then using Qlik Sense to connect to the table (or a new table created) to grab the relevant data which I can then analyse in that software.

    Each day there will probably be up to 5,000 rows of data coming in which will need to have their ItemIDs changed (only for the ItemID = 2, if other ItemID numbers then they will stay the same).

    There will be lots of data with same DatePaidDate and Time etc but all have unique TransactionIDs.

    Hope this makes sense, and once again thanks for coming back to me.

  4. #4
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Think I have done it:

    IIf([itemID]=2,(IIf((([Date Paid Date]-[Date of birth])/12)<6,1001,(IIf((([Date Paid Date]-[Date of birth])/12)<12,1002,(IIf((([Date Paid Date]-[Date of birth])/12)<36,1003,(IIf((([Date Paid Date]-[Date of birth])/12)<132,1004,1005)))))))),[itemID])

    I assume this would update the fields automatically as new data is brought into the table by the email parsing software?

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I don't think you can do arithmetic on dates like that. The DateDiff function will give you directly the number of months between two dates:

    DateDiff("m", [Date Paid Date], [Date of Birth])

    So,

    ItemID = IIf(ItemID = 2,(IIF(((DateDiff("m",[Date Paid Date], [Date of Birth]) < 6 , 1001, ...

  6. #6
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    It works fine but when I go to input more data into the table I'm getting #Invalid in the calculated field for all new rows.

    Any idea what I'm doing wrong?

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    How are you inputting new data into the table - via a form or directly into the table? If it's a form, what's the recordsource for the form?
    In what way are you using the IIf code, i.e., where is it?

  8. #8
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    I just imported the data straight into the table from an excel table (quite a few rows).

    Moving forward I intend to run an email parser to input the data straight into this table (will probably be 1,000+ rows each day) and then the plan is for the calculated fields to automatically update. I will then connect Qlik Sense BI software to this table and run calculations in Qlik Sense.

    The IIf code is in a calculated field (ItemIDCorr) within the table.

    Thanks again Davegri

  9. #9
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Quick update - with the same data in the tame (Original & imported) I created another calculated field called test with the same formula in, saved the table design and viewed the table - all of the calculated fields are displaying correctly.

    Do I need to run a script or something to get calculated fields to recalculate themselves each time data is imported?

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can't update calculated fields.
    You will need to run an update query against the unaltered imported data.
    Remove the calculated field from the table design. Make the ItemID field long integer.
    Create a new query with EZExtract as the only table.
    Drag down ItemID into the grid.
    Right click in the big space around the table and chose query type as Update.
    In the UPDATE TO, put the code that was formerly in the table calculated field, the IIF stuff.
    Run the query. It will update ItemID and you can now edit OK.

    You can save this query and run it after each data import.

  11. #11
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Thanks for this, will give it a try. What would I need to change to get the letters "EZ" infront of the text whereby the [ItemID]now becomes EZ[ItemID] E.g. 201 becomes EZ201, 1002 becomes EZ1002 etc.

    Thanks again

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    What would I need to change to get the letters "EZ" infront of the text whereby the [ItemID]now becomes EZ[ItemID] E.g. 201 becomes EZ201, 1002 becomes EZ1002 etc.
    You would need to change the data type of ItemID in the table to text.
    In the IIf code, put single quotes around the text fields
    Code:
    IIf(ItemID = 2,(IIF(((DateDiff('m',[Date Paid Date], [Date of Birth]) < 6 , 'EZ1001', ...
    Quotes within SQL statements should be single, probably around the 'm' above also..

  13. #13
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Works a treat !!

    Thank you so much for your help, would have been lost otherwise!!

    I owe you a large beer!!

  14. #14
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Sorry, spoke too soon - there are 116,509 records that seem to have been lost somehow (original table has 195,662 records and amended table only has 79153 records).

    Looking through the data it seems to be missing off random records rather than missing particular ItemID's.


  15. #15
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Are you sure the table with 195662 records had only 79153 records after running the update query? I mean the same table the query updated, not a different amended table.

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

Similar Threads

  1. Conditional Formatting - Using Eval function with wildcards
    By mlichtenstein in forum Programming
    Replies: 2
    Last Post: 07-02-2015, 08:09 AM
  2. Replies: 19
    Last Post: 09-05-2014, 06:13 AM
  3. Replies: 7
    Last Post: 10-04-2013, 11:45 AM
  4. Conditional Sum Function
    By samthomasny in forum Access
    Replies: 4
    Last Post: 11-21-2011, 10:00 AM
  5. Using conditional COUNT function
    By Laura WW in forum Reports
    Replies: 3
    Last Post: 11-08-2011, 11:59 PM

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