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

    Question Planing a Report

    Ladies and Gentlemen,



    I am fairly new to access. Mostly, I have used it to combine values and it was way too long ago.

    I am working on a report, that deals with at least 12k to 15k lines, which I would like to have your opinion about.

    So basically, this report contains following excel files:

    - Raw Data Sheet
    - Raw Data Sheet for Exceptional Rules
    - Template with bunch of IF Rules

    So what I have before is that, I have manually run everything, vlookuped it, combined data and somehow, I came to my report.

    Now, with more and more lines of data getting added, its just getting unbearable. So now, I am thinking, if Access could help me with that.

    Following questions are raised:


    • Can I integrate those "IF" rules in Access?
    • Can I make sequences to combine values and datas in Access to produce a finished report?


    This are the examples of "IF" rules I am using in my Excel Template:

    =IF(A2="T024";"SNP";IF(A2="T047";"SNP";IF(A2="T059 ";"SNP";IF(A2="T060";"SNP";IF(A2="T064";"SNP";IF(A 2="P004";"SNP";IF(A2="P008";"SNP";IF(A2="P009";"SN P";IF(A2="P017";"SNP";IF(A2="P019";"SNP";IF(A2="P0 40";"SNP";IF(A2="P042";"SNP";IF(A2="T086";"CSDM";I F(A2="T087";"CSDM";IF(A2="P021";"CSDM";IF(A2="P022 ";"CSDM";IF(A2="P025";"CSDM";IF(A2="P030";"CSDM";I F(A2="P031";"CSDM";IF(A2="P034";"CSDM";IF(A2="P035 ";"CSDM";IF(A2="P036";"CSDM";"RULE"))))))))))))))) )))))))

    and

    =IF(A2="T023"; IF(H2="MFG";"PPDS";IF(H2="Toll";"PPDS";"SNP")))

    Every help and hints are very much appreciated.

    Kind regards

    Timothy

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Planing a Report

    Can I integrate ifs?

    There's an iif command in access but given the way they are all if a2 ="****" then "****" maybe a lookup table of to24 | snp.


    If you're up for a challenge you could right a function for this too.

    You "call" the a2 value and the function "returns" the "snp"



    Not totally clear what you mean by the sequences point. Any example?


    Sent from my iPhone using Tapatalk

  3. #3
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Quote Originally Posted by andy49 View Post
    Can I integrate ifs?

    There's an iif command in access but given the way they are all if a2 ="****" then "****" maybe a lookup table of to24 | snp.

    Not totally clear what you mean by the sequences point. Any example?


    Sent from my iPhone using Tapatalk

    Hey Andy,

    thanks for reply,

    What i meant with sequence is that,

    1st, Combine Data
    2nd, VLookup
    3rd Apply IF rules
    4th Produce Report


    I mean something like that

    Is it doable with access?

  4. #4
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Hey Andy,

    thanks for reply,

    What i meant with sequence is that,

    1st, Combine Data
    2nd, VLookup
    3rd Apply IF rules
    4th Produce Report


    I mean something like that

    Is it doable with access?

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    With a little bit of work in vba (similar to the macro system in excel) I should say fairly simple.

    I assume your data is coming from excel? How many tables will you need? What fieldnames (column headings in excel)


    Sent from my iPhone using Tapatalk

  6. #6
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Hey Andy,

    the word "Fairly simple" makes me already a bit happy

    The report it self populates column A - w in Excel,

    Rows are variable. Around 12k i would say.

    The headings are pre defined. More or less, there will be a column, that would be the key for combining the values.

    I hope I could answer your question

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Well the key here is to set up the database so it can run the report you want. Can we see a small section of data on here or even a previous report (cut down if you like).


    Sent from my iPhone using Tapatalk

  8. #8
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Hey Andy,

    I dont know, if i can upload it here, hence, it contains very delicate company data.

    I could make an example of the report.

    I have a bit of VBA experience. I would just like to have a bit of guidance on how it should be done or arranged.

    Thank you very much in advance.
    Attached Files Attached Files

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do you have an Access database you can post? With maybe some test data like the spreadsheet?

    Are you importing the data from the Excel spreadsheets or are you linking to the spreadsheets?

  10. #10
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Hey Steve,

    thank you for your answer.

    Thats the issue, I havent started building the Access Database. I am trying to figure out, what would be the steps to be done.

    I probably gonna import the excel spreadsheets. Just for the simplify. I would write a Work instruction for it as well, as soon as I have established everything.


    Thank you very much

    Kind regards

    Timothy

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I probably gonna import the excel spreadsheets.
    Are all of the spreadsheets formatted the same? All columns are the same??

  12. #12
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    By personal experience Tim, steves your man. My db is brilliant and it was his help setting it up that got me sorted


    Sent from my iPhone using Tapatalk

  13. #13
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Hey Steve,

    yea,

    2 ways i am thinking. Either do the IF logic inside the excel and build them all together in Access or if applicable, just import all the raw spreadsheets in access and build them there.

    What would be the most sustainable and easiest way to do? Assuming, that if I go on holiday, that someone with less-er knowledge than me of Excel and Access would take over ?

    Thank you very much

    Timothy

  14. #14
    Ildestino is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    8
    Hey Andy,

    yea, I hope he could help me out here

    I probably just need a beginning of the DB that I can tailor with the right files

    Thanks as well for your help and if you have any ideas as well, it is more than appreciated.

    Kind regards

    Timothy

  15. #15
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Database4.zip

    Hi Tim

    Just my initial thoughts

    can you have a look and see if the data is coming out correctly

    the querytmpQuery is the one which you can open

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

Tags for this Thread

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