how to create new table columns in access?

[beginner2012]

Hi there,

This may be a easy question, but I am an Access beginner and not very good at vba.

right now I have the data in the following format:

[U][B]ASSET   HOLDING  STATUS  AMOT[/B][/U]
asset1      1      EGL     5
asset1      1      UGL     6
asset1      1      FVA     7
asset1      4      EGL     8
asset1      4      UGL     9
asset1      4      FVA    10
asset2      5      EGL    11
asset2      5      UGL    12
asset2      5      FVA    13

I want to split up the Status into 3 columns

[U][B]ASSET   HOLDING  EGL  UGL  FVA[/B][/U]
asset1      1     5    6    7
asset1      4     8    9   10
asset2      5    11   12   13

I very much appreciate whoever can give me some advice. Thank you very much

[Rabbit]

Use a crosstab query for that, don't modify the table.

[beginner2012]

the problem is that this is my middle step. I need this table in new format to keep going on other steps

[NeoPa]

That's not good thinking. Trust Rabbit on this one. You can always use a query pretty well anywhere you're thinking about using a table.

[ChrisPadgham]

If you must do it, then create a crosstab query, save it, then create a make table query based on the saved crosstab query.

[Rabbit]

But the thing is there's not really a need for a make table. Anything you need to do with a table, you can probably do with a query.

[NeoPa]

Rabbit:
Anything you need to do with a table, you can probably do with a query.

That being the fundamental point.

[ChrisPadgham]

except of course if you want to update the data, crosstab queries are not updateable

[NeoPa]

That's true Chris, but we're working with an OP who isn't making the details very clear (probably because they don't appreciate what they need to do - no criticism), so we have to make intelligent guesses. It's very rare (though not entirely unheard of, of course) that people want to update data that is a direct result of processing other data.

Using the concepts of normalisation, one would suggest that any such changes should be made in the original data, rather than any derivative intermediate table, and re-derived. This (Normalisation theory) is also strong reasoning for suggesting that, except in extraordinary circumstances, the data is manipulated at source via queries rather than by duplicating the data (in a MakeTable query) for processing.

PS. I've noticed some helpful posts from you recently (today/yesterday) and want to welcome you to Bytes. Clearly you have something to offer the community, and I like your attitude already (Finding so many threads to contribute to in such a short space of time).