Results 1 to 9 of 9

Thread: VBA Query

  1. #1
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi I am working on a table with loads of lines so not able to do this in Excel - can you help me do this in a VBA module? (Total beginner but trying to learn!)

    Table A consists of two fields - BU and Numbers

    Table B consists of several lines of data coming in from various sources. The headers relate to all of the BU's found in Table A, and Line 1 of the table should consist of the sum of the field "Numbers" for each BU. To explain

    Example Table A - BU/Count

    MAN/2
    MAN/4
    WOMAN/3
    WOMAN/2
    GIRL/2

    would relate to Table B

    MAN WOMAN GIRL
    Line 1 6 5 2

    Given that this is such a large table a module would need to loop through the entire table, find each BU and sum the total of the "Number" field - how's it best to do this, given that I want to do it in a module and not in a query?

    Much obliged!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See if the function TotalField2 in the Module1 if the attached DB do what you want.

    [attachment=86924:db7.zip]
    Attached Files Attached Files
    Francois

  3. #3
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Francoise, it almost does, thanks - but it doesn't allow me to differentiate that I want the Data to go into the first Line (Line1)

    How would I have to write that?? It would be helpful to know as I've got similar data coming in from other tables, which are appended to different lines.

    Thanks for your help

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The line

    rst2.MoveFirst

    ensures that the data go into the first record.

    If you'd want the data to go into the 5th record (which should already exist!), you could add a line

    rst.Move 4

    (i.e. move 4 records forwards from the current position, the 1st record)

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In all honesty, I think you have an unworkable table design. Relational databases are by design positionally independent, and referring to the first record in a table doesn't work. To enforce that sort of stuff you have to always sort by some sort of a key. In addition, the counts your are storing will in all likelyhood change each time you run the process. One of the rules of designing databases is to not store information that you can calculate. How about if we visit in more detail about what your database is supposed to do functionally?
    Wendell

  6. #6
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell, yes its not the ideal use of Access but it is for a very specific purpose. Basically I have multiple data sources coming in, many of them exceed the 65000 lines easily dealt with in Excel. I tend not to work in Excel for a number of reasons, one of which believe it or not is actually medical (related to visual scrolling and vertigo). I find that working in Access is much more comfortable for me as I can avoid the need to scroll large tables by chunking what I need to view, and that I can achieve most things I need to do with it. So that is why I am kinda stuck using Access (mind you I love it and am really keen to learn all I can!).

    The common denominator in each of the incoming sources is data relating to various Business Units (BU). The MI I am requested to provide essentially means formulating a fixed grid table with the BU's as headers, a Line number, and a Line description (See table E attached as an example, although the tables all answer on different data and variables). For most of the "lines", a loop through the data on simple parameters and a count of the relevant records is all that is needed, and Hans has already helped me enormously with this and its working extremely well and extremely quickly (cheers Hans).

    However, for some of the "lines" I need to do something a bit different, for example sum the values in the TestScope field (DryRun.xls attached) and put them against the correct BU, or calculate them as a % of another line. This is what I'm struggling to do achieve in VBA - I've bought several books and to be honest, I just can't find similar code examples anywhere in them or on the web.

    So in the example I've given for Line 1 of table E, I'm putting in the sum of the TestScope Field in the DryRun.xls table against the relevant BU.

    Hope that makes sense.

    Best.
    Attached Files Attached Files

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I didn't mean to imply that Access is the wrong tool to be using - in fact looking at your sample data, it seems to be to be a better match than Excel. My point was that your data isn't normalized, which makes it very difficult to do the sort of things you describe. For example, I would create a table for business units, another table for specific milestones for each activity, a table of projects, etc. With that structure in place you can run queries and very quickly perform updates on data, and also create reports such as the second attached file appears to be.
    Wendell

  8. #8
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for delay in reply - have been off work for a few days. Yes, I agree with you, but there are a number of reasons why I cannot normalise the data or do not want to (I hear indrawn breath from the real database developers :-) ) - for compliance reasons I am not allowed to change any data that comes into this small application, normalising would require me to alter data within the tables, or to write more queries generating duplicate tables to then normalise them - generally just having to do complex work-arounds for something which would probably be fairly simple in a module, if only I knew how :-). Also the data is coming from multiple sources, codes can and do change weekly without advice to myself - I am not technically good enough yet to find new codes and apply them to the relevant normalised tables etc. I really was hoping to be able to work this in VBA modules - even as a learning experience.

    I also found it difficult to do the work required in query format due to the fact that it is really cross-tab information and crosstabs don't allow you to update. I was keen to avoid writing perhaps 10 queries to update one line of one table, when I have hundreds of such lines - this on the data I've got without some sort of looping mechanism I'm reckoning the queries would go into the thousands. I've tried to simplify what I need to do when explaining it to yourselves, but as always the data is more complex and has more variables than I've discussed with you for the normal reasons of data security etc., and I really can't see my way to doing all this by query.

    Thanks anyway.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I would not propose to modify the data that comes in - that you have not control over. But you should be able to create normalized data from that data, and then you would be able to use a cross-tab query to build your final results. The sort of VBA you are attempting is quite convoluted, and unless you put some sort of key on the data to make sure it stays in the right sequence, you will find it unreliable.
    Wendell

Posting Permissions

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