picking the correct column

[jturn00]

I have want to update a table or query "dynamically"

I have the following information.

ID #, bucket #, and then amount.


My table or query would be defined something like this.

id # Bucket 1 Bucket 2 Bucket 3 etc......

I want to figure out in an update that if I have bucket 1 then the update would put the correct amount into bucket 1. (if there is an amount there already then I would have to add this amount to it.) The id # tells me which row I should choose to put the bucket into.

Any ideas on how to set this up?

Jeff

 

[Andrew Fergus]

Hi Jeff
I'm not following you 100%. When you say 'I have the following information : ID #, bucket #, and then amount'. does this mean that this data is already in a table in your database?
Andrew

 

[Norie]

Jeff

Why do you have multiple fields for the buckets?

 

[jturn00]

andrew93:

The data is in different tables in my database and I am pulling it together into one table. The common key is the ID#.

Norie:

The information has to be static and exported into a statistical program. It needs the buckets lined up. The best way I thought that I could do this create a table with 120 columns.

Although, I guess I can make a table where each row is a bucket #, ID # combination? (unique index). But this would create a lot of rows in the table for each bucket /ID combination. So say if there are 10 buckets and 1000 ids then there would be 10,000 rows and so on.

Any ideas?

 

[Norie]

I would suggest the 2nd method - Access can easily handle 10,000 rows.

If you need to get it into the other format for export you could use a crosstab query.

 

[Andrew Fergus]

I agree with Norie that would be the better approach and the crosstab query would be the best method for getting the data into the format you described. Is it possible that one id# could have more than value for one bucket number? i.e. could id# 1 have two or more buckets also numbered 1?
Andrew
Late Edit : is there anything preventing you from doing the crosstab query using your existing tables?

 

[jturn00]

andrew93 and Norie,

Thanks for the help.
Each id # can have only one bucket 1 and one bucket 2 so they are unique.

In my early attempts in using cross tab queries, I had problems in getting it to work and I am more familar with SQL and tables approach. But I think with the help you have given me I can work out a solution.

Note: My boss gave me another project with higher urgency so I won't get to try out solutions until near the end of next week. Thus I won't be able to give you feedback until then on how it is going.

Thanks for all the help.

Jeff