What queried should I use???

Hello!
As you can see, I have vendors listed as below in vertical format (vendor 1,
vendor 2,vendor 3). And I also have accounts across (100000, 100001,
100002, 10003) where I have the $ amounts associate with each vendor. So I
would like to know how do I set up a query to change the format in figure 1
to figure 2 where I can have both vendor and accounts going down (vertically).

Figure 1:
Vendor 100000 100001 100002 100003
1 $5 $10 $5 $10
2 $5 $10 $5 $10
3 $5 $10 $5 $10

Figure 2:
Vendor Acct Amount
1 100000 $5
1 100001 $10
1 100002 $5
1 100003 $10
2 100000 $5
2 100001 $10
2 100002 $5
2 100003 $10
3 100000 $5
3 100001 $10
3 100002 $5
3 100003 $10

Your help is really appreciated!

You would need to use a UNION All query. Union queries can only be built in
the SQL window and not in the design window.

The SQL for the Union query would look like

SELECT Vendor, [100000] as Amount, "100000" as Acct
FROM YourTable
UNION ALL
SELECT Vendor, [100001] as Amount, "100001" as Acct
FROM YourTable
UNION ALL
SELECT Vendor, [100002] as Amount, "100002" as Acct
FROM YourTable
UNION ALL
SELECT Vendor, [100003] as Amount, "100003" as Acct
FROM YourTable

This will work to display the data. Eventually, it will fail if you have a
large number of accounts.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Lee" wrote in message
...
Hello!
As you can see, I have vendors listed as below in vertical format (vendor
1,
vendor 2,vendor 3). And I also have accounts across (100000, 100001,
100002, 10003) where I have the $ amounts associate with each vendor. So I
would like to know how do I set up a query to change the format in figure
1
to figure 2 where I can have both vendor and accounts going down
(vertically).

Figure 1:
Vendor 100000 100001 100002 100003
1 $5 $10 $5 $10
2 $5 $10 $5 $10
3 $5 $10 $5 $10

Figure 2:
Vendor Acct Amount
1 100000 $5
1 100001 $10
1 100002 $5
1 100003 $10
2 100000 $5
2 100001 $10
2 100002 $5
2 100003 $10
3 100000 $5
3 100001 $10
3 100002 $5
3 100003 $10

Your help is really appreciated!

hi John, thanks for responding. Do you know how large number of accounts?
100? 200?

Thanks again!

"Lee" wrote:

Hello!
As you can see, I have vendors listed as below in vertical format (vendor 1,
vendor 2,vendor 3). And I also have accounts across (100000, 100001,
100002, 10003) where I have the $ amounts associate with each vendor. So I
would like to know how do I set up a query to change the format in figure 1
to figure 2 where I can have both vendor and accounts going down (vertically).

Figure 1:
Vendor 100000 100001 100002 100003
1 $5 $10 $5 $10
2 $5 $10 $5 $10
3 $5 $10 $5 $10

Figure 2:
Vendor Acct Amount
1 100000 $5
1 100001 $10
1 100002 $5
1 100003 $10
2 100000 $5
2 100001 $10
2 100002 $5
2 100003 $10
3 100000 $5
3 100001 $10
3 100002 $5
3 100003 $10

Your help is really appreciated!

On Tue, 20 Feb 2007 12:15:15 -0800, Lee
wrote:

Hello!
As you can see, I have vendors listed as below in vertical format (vendor 1,
vendor 2,vendor 3). And I also have accounts across (100000, 100001,
100002, 10003) where I have the $ amounts associate with each vendor. So I
would like to know how do I set up a query to change the format in figure 1
to figure 2 where I can have both vendor and accounts going down (vertically).

A VERY good thing to do... and actually a pretty common one,
especially if you need to move data from spreadsheet to a normalized
database!

You can use what's called a "Normalizing Union Query". You need to
create a new query, and - without adding any tables - go into SQL
view. The window will contain a singularly unhelpful

SELECT;

Edit this to

SELECT Vendor, "100000" AS Acct, [100000] AS Amount
WHERE [100000] IS NOT NULL
UNION ALL
SELECT Vendor, "100001" AS Acct, [100001] AS Amount
WHERE [100001] IS NOT NULL
UNION ALL
SELECT Vendor, "100002" AS Acct, [100002] AS Amount
WHERE [100002] IS NOT NULL
UNION ALL
SELECT Vendor, "100003" AS Acct, [100003] AS Amount
WHERE [100003] IS NOT NULL

etc. for all the accounts. Save this query as uniAllAccounts; then
create your new empty table with the desired field names, datatypes
and field sizes (a MakeTable will make your text fields 255 bytes,
which you don't need!). Then create an Append query based on
uniAllAccounts appending to the new table and run it.

John W. Vinson [MVP]

On Tue, 20 Feb 2007 14:00:08 -0800, Lee
wrote:

hi John, thanks for responding. Do you know how large number of accounts?
100? 200?


How many accounts you can include in the query?

There's a limit, and it may well be under 100: you'll know when you
get the "Query Too Complex" error when you try to open the query. The
limit is 64KBytes in the *compiled* query; this depends on the lengths
of your fieldnames, the number of fields you choose, and other arcane
and undocumented features.

BUT... you can create two or three UNION queries, each with a subset
of the fields, and append the separately to your target table.

John W. Vinson [MVP]

Hi John,
Thanks for answering my question. Is there a better way to write the SQL
since I have different kinds of range of account (they are not in sequence)?
I have 200 so accounts far. And it's very tedious to write each of the
account number in the Union query every time.

Thanks again!

"John W. Vinson" wrote:

On Tue, 20 Feb 2007 14:00:08 -0800, Lee
wrote:

hi John, thanks for responding. Do you know how large number of accounts?
100? 200?


How many accounts you can include in the query?

There's a limit, and it may well be under 100: you'll know when you
get the "Query Too Complex" error when you try to open the query. The
limit is 64KBytes in the *compiled* query; this depends on the lengths
of your fieldnames, the number of fields you choose, and other arcane
and undocumented features.

BUT... you can create two or three UNION queries, each with a subset
of the fields, and append the separately to your target table.

John W. Vinson [MVP]

On Wed, 21 Feb 2007 07:59:31 -0800, Lee
wrote:

Hi John,
Thanks for answering my question. Is there a better way to write the SQL
since I have different kinds of range of account (they are not in sequence)?
I have 200 so accounts far. And it's very tedious to write each of the
account number in the Union query every time.

Where is this wide-flat data coming from? Surely you're not
INTENTIONALLY storing a table with account numbers as fieldnames?

What's the context? If we can figure out why you need to do this in
the first place, maybe we can find way to automate the process of
normalizing it.

John W. Vinson [MVP]

Well the data is given to me as it is. I can't change it and have to way to
trace the source. Do you think there're any other solutions?

"John W. Vinson" wrote:

On Wed, 21 Feb 2007 07:59:31 -0800, Lee
wrote:

Hi John,
Thanks for answering my question. Is there a better way to write the SQL
since I have different kinds of range of account (they are not in sequence)?
I have 200 so accounts far. And it's very tedious to write each of the
account number in the Union query every time.

Where is this wide-flat data coming from? Surely you're not
INTENTIONALLY storing a table with account numbers as fieldnames?

What's the context? If we can figure out why you need to do this in
the first place, maybe we can find way to automate the process of
normalizing it.

John W. Vinson [MVP]

On Wed, 21 Feb 2007 11:12:10 -0800, Lee
wrote:

Well the data is given to me as it is. I can't change it and have to way to
trace the source. Do you think there're any other solutions?

And you are getting it... how? a Text file? An Excel spreadsheet? A
dBase file? An Access database?

You'll somehow need to parse the fieldnames and generate the SQL code,
using VBA to do so. I'd have to play around with the syntax a bit, but
it will be different depending on the nature of the file containing
the data.

John W. Vinson [MVP]

It's an access database.

"John W. Vinson" wrote:

On Wed, 21 Feb 2007 11:12:10 -0800, Lee
wrote:

Well the data is given to me as it is. I can't change it and have to way to
trace the source. Do you think there're any other solutions?

And you are getting it... how? a Text file? An Excel spreadsheet? A
dBase file? An Access database?

You'll somehow need to parse the fieldnames and generate the SQL code,
using VBA to do so. I'd have to play around with the syntax a bit, but
it will be different depending on the nature of the file containing
the data.

John W. Vinson [MVP]