Table Format

[awach]

If I have a table that looks like:

name partA part B part C
model1 5% 7% 88%

And I want it to look like:
name part amount
model1 A 5
model1 B 7
model1 C 88
model2 A 15
model2 B 18

I have a query that creates the first two columns of the desired table but I
can't figure out how to complete it with the amounts. Any ideas?

 

[Granny Spitz via AccessMonster.com]

awach wrote:
Your table isn't normalized, but the query you want will normalize the data
for you:

SELECT ModelName, "A" AS Part, PartA AS Amount
FROM TableA
UNION
SELECT ModelName, "B" AS Part, PartB AS Amount
FROM TableA
UNION
SELECT ModelName, "C" AS Part, PartC AS Amount
FROM TableA;

Save that query as qryUnionTblModels_Unnormalized, then run this query to
create a normalized table from your data:

SELECT * INTO tblModels
FROM qryUnionTblModels_Unnormalized;

Use this new normalized table for future queries.

 

[awach]

I'm still pretty new at this...where do I type this in?

 

[Granny Spitz via AccessMonster.com]

I'm still pretty new at this...where do I type this in?

Create a new query and put it in SQL view, Insert | Query | OK | Close, then
View | SQL View.

 

[awach]

So, I created a new query by entering the first part of the code using SQL
view. Then I saved it as qryUnionTblModels_Unnormalized. Now, what do I do
with the second part of the code (SELECT * INTO tblModels
FROM qryUnionTblModels_Unnormalized? Thanks!

 

[awach]

I ran into a little trouble. Here are my specific tables:

If I have a table that looks like:

Model Name C Tac Trad Tac Alt Tac
model1 5% 7% 88%

And I want it to look like:
name Class amount
model1 C 5
model1 Trad 7
model1 Alt 88
model2 C 15
model2 B 18

How would I alter the code you gave before for these specific tables? Thank
you so much!

 

[Granny Spitz via AccessMonster.com]

what do I do
with the second part of the code (SELECT * INTO tblModels
FROM qryUnionTblModels_Unnormalized?

Create a new query, paste it in and run it. It will create a new table,
tblModels, which you can use as the data source for your future queries so
you don't have to bend over backwards trying to get your queries to work with
unnormalized data.

 

[Granny Spitz via AccessMonster.com]

How would I alter the code you gave before for these specific tables?

Like this:

SELECT ModelName, "C" AS Class, CTac AS Amount
FROM TableA
UNION
SELECT ModelName, "Trad" AS Class, TradTac AS Amount
FROM TableA
UNION
SELECT ModelName, "AltTac" AS Class, AltTac AS Amount
FROM TableA;

 

[awach]

Does CTac have to be in quotes?

Like this:

SELECT ModelName, "C" AS Class, CTac AS Amount
FROM TableA
UNION
SELECT ModelName, "Trad" AS Class, TradTac AS Amount
FROM TableA
UNION
SELECT ModelName, "AltTac" AS Class, AltTac AS Amount
FROM TableA;

 

[awach]

One more thing, the second table, tblModels, will be updated as the original
tables are updated/modified?

 

[Granny Spitz via AccessMonster.com]

Does CTac have to be in quotes?

I didn't use CTac in quotes in my example. Anything you put in quotes will
have quotes in the query results.

 

[Granny Spitz via AccessMonster.com]

I didn't use CTac in quotes in my example. Anything you put in quotes will
have quotes in the query results.

Let me explain further. If you put quotes around a *calculated* value, the
string literal will show in the query results *without* the quotes, but if
you put quotes around the column name, even if it's a calculated column, that
name (column header) will have quotes in the query results.

 

[Granny Spitz via AccessMonster.com]

One more thing, the second table, tblModels, will be updated as the original
tables are updated/modified?

No, don't use the original table anymore. It's bad. Just use the fixed one
for queries. You'll have to alter any other queries you've written to work
with the new normalized structure though. That's why it's always better to
fix problems with tables *before* building the rest of the application.
There's a lot less rework.

 

[awach]

Instead of creating the second table(made from the second part of the code),
can I just use the query (made from the first part of the code) as the new
record source? Will it be updated as the original table is updated?

 

[Granny Spitz via AccessMonster.com]

Instead of creating the second table(made from the second part of the code),
can I just use the query (made from the first part of the code) as the new
record source?

Yes, you can but you're just avoiding the inevitable.

Will it be updated as the original table is updated?

You can requery the form (or the query) to get any fresh updates on the
original table because UNION queries aren't updateable themselves, meaning
you can't make changes to data in the table *from* the UNION query.