VBA Query

Topic

New Reply

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!

Reply | Quote

Replies

See if the function TotalField2 in the Module1 if the attached DB do what you want.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote