Looping through columns to find a specific value

[nguyeda]

Hello,

I need to do figure out a couple options:

This query returns a running asset balance for 60 months. Each month is 1 column.

1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.

2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.

Can anyone help or point me in the right direction? I tried searching this forum for [SOLVED] and google but couldn't find anything really related. May be I was searching for the wrong thing...

 

[plog]

The right method isn't the one you described. You should always try to work with data vertically (in rows) and not horizontally (in columns).

Can you post the structure of the table that ultimately feeds this query?

 

[nguyeda]

I compeltely agree but it's the format that I had to use for our users.

There are about 7-10 tables that create the initial subquery. There are 60 subqueries to get to the one I want to analyze. I had to take the ending balance of each asset of each month, the create another query based off that balance and add in whatever deliveries this asset has in and reduce the forecasted demands on it.

There are about 25,000 asssets.

The database is a monster

You are probably right, I should reformat it so that it's verticle. I wonder how I could do that...

See the columns are labeled "CM", "CM_1", "CM_2", "CM_X" which basically means current month, current month + 1, current month + 2, current month + X through 59. Using DATESERIAL I'm about to make the results dynamic from month to month based on when we update the tables.

 

[David R]

UNION queries might help you wrangle it into a usable state. You can add a calculated field to reflect the column/Month it used to be under.

UNION queries are of course not updateable by their very nature, but it will make your analysis a ton easier.

 

[plog]

it's the format that I had to use for our users.

No, its the format you have to deliver to the users. With data you have to do manipulation on, you should always store it in the way that is easiest to work with. With an improper structure, 60 subqueries (and still not achieving the results you want) doesn't sound shocking at all.

 

[David R]

I took that to mean that he was GETTING the data in a non-normalized state from his users. Sometimes there's nothing you can do about that... but yeah, this database will become a lot less monstrous if you have the ability to restructure it. Your users don't even have to know anything has changed, if all their work is in Access forms...

 

[nguyeda]

I'm analyzing SAP data and putting it in a dynamic visual tool in excel. So, the formulas in Excel actually need the data in columns in order for the chart visualization to work.

I'm going to revisit restructuring the queries for the purposes I need here.

Thanks guys!

 

[nguyeda]

Pat,

I agree. In this case, I couldn't figure out how to make it work. There are about 7-8 tables that make the asset position go up or down. I have to keep everything in their respective months. It's a rolling monthly asset balance.

The only way I could figure it out is to create quries to get the monthly balance then create another query to take that result and add it to the next month and so on.

I wish my VBA skills were stronger.

 

[plog]

I wish my VBA skills were stronger.

You've completely missed what we are talking about. Stronger VBA skills would allow you to treat the symptoms of your issue, not your actual issue.

You need to properly normalize your data structure and learn SQL so that you can properly transform the data you need to, not use VBA to code around the poor structure you have.

If you'd like to post some sample data along with table and field names we can help you along with getting to the source of your issue.