Transposing Data from Query

B

Brian

Hi there

I wonder if anyone can offer some advice on acheiving an end result. I have
data which comes from a database in the format:

Date AdvisorName Type1Sales Type2Sales Type3Sales
10/11/2007 Brian Thomson 5 3
2

This information is then stored into another database in the format:

Date AdvisorName SalesType Sales
10/11/2007 Brian Thomson Type1Sales 5
10/11/2007 Brian Thomson Type2Sales 3
10/11/2007 Brian Thomson Type3Sales 2

It is almost like a pivot table but I would then append this data to another
database table in the format above.

Hope this makes sense. If not let me know and I will elaborate.

Cheers
 
S

Stefan Hoffmann

hi Brian,
This information is then stored into another database in the format:
Click to expand...
This is called normalization.
It is almost like a pivot table but I would then append this data to another
database table in the format above.
Click to expand...
Which one from above? The first? Then create a new query and use the
pivot wizard.
Hope this makes sense. If not let me know and I will elaborate.
Click to expand...
Not to me. Sorry.


mfG
--> stefan <--
 
G

Gary Walter

"Brian"wrote:
I wonder if anyone can offer some advice on acheiving an end result. I
have
data which comes from a database in the format:

Date AdvisorName Type1Sales Type2Sales
Type3Sales
10/11/2007 Brian Thomson 5 3
2

This information is then stored into another database in the format:

Date AdvisorName SalesType Sales
10/11/2007 Brian Thomson Type1Sales 5
10/11/2007 Brian Thomson Type2Sales 3
10/11/2007 Brian Thomson Type3Sales 2

It is almost like a pivot table but I would then append this data to
another
database table in the format above.
Click to expand...

Hi Brian,

In a simple, known situation, you can also type
out a UNION query in SQL View

SELECT
[Date] As SalesDate,
AdvisorName,
"Type1Sales" As SalesType,
Type1Sales As Sales
FROM yurtable
UNION ALL
[Date] As SalesDate,
AdvisorName,
"Type2Sales" As SalesType,
Type2Sales As Sales
FROM yurtable
UNION ALL
[Date] As SalesDate,
AdvisorName,
"Type3Sales" As SalesType,
Type3Sales As Sales
FROM yurtable;

Save this query, then you can use it to feed a make-table
or append query.

If you don't want to include number fields that are NULL,
then filter in each UNION clause...

SELECT
[Date] As SalesDate,
AdvisorName,
"Type1Sales" As SalesType,
Type1Sales As Sales
FROM yurtable
WHERE Type1Sales IS NOT NULL
UNION ALL
[Date] As SalesDate,
AdvisorName,
"Type2Sales" As SalesType,
Type2Sales As Sales
FROM yurtable
WHERE Type2Sales IS NOT NULL
UNION ALL
[Date] As SalesDate,
AdvisorName,
"Type3Sales" As SalesType,
Type3Sales As Sales
FROM yurtable
WHERE Type1Sales IS NOT NULL;

good luck,

gary
 
G

Gary Walter

that last SQL should have been...

SELECT
[Date] As SalesDate,
AdvisorName,
"Type1Sales" As SalesType,
Type1Sales As Sales
FROM yurtable
WHERE Type1Sales IS NOT NULL
UNION ALL
[Date] As SalesDate,
AdvisorName,
"Type2Sales" As SalesType,
Type2Sales As Sales
FROM yurtable
WHERE Type2Sales IS NOT NULL
UNION ALL
[Date] As SalesDate,
AdvisorName,
"Type3Sales" As SalesType,
Type3Sales As Sales
FROM yurtable
WHERE Type3Sales IS NOT NULL;

Plus, I forgot to mention to replace "yurtable"
with actual name of your table...
 
Top