What queried should I use???

[Lee]

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!

 

[John Spencer]

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]

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

Thanks again!

 

[John W. Vinson]

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]

 

[John W. Vinson]

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]

 

[Lee]

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]

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]

 

[Lee]

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]

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]

 

[Lee]

It's an access database.

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]

 

[John W. Vinson]

It's an access database.

Eeesh.

With just the one table, with account numbers as fieldnames, and no
way to control or know the fieldnames ahead of time!? Somebody goofed,
bigtime.

Could you post a *real* example of one of these tables, in the format

Tablename
Fieldname <datatype>
Fieldname <datatype>
Fieldname <datatype>

and give some indication of the range of possible values for the
column headers (I can't even bring myself to call them fieldnames,
since they are just SO spreadsheet)?

John W. Vinson [MVP]

 

[Lee]

I know. Sure. Please see below:

Tablename = Account
FieldName = Account number range from 100000 thru 999999 (in currency).

Let me know if you need anything else. Thanks for your big help again!

 

[John W. Vinson]

I know. Sure. Please see below:

Tablename = Account
FieldName = Account number range from 100000 thru 999999 (in currency).

Let me know if you need anything else. Thanks for your big help again!

I'm sorry, Lee, that leaves me completely in the dark.

I know full well that you do not have a table with 899999 fields,
because Access couldn't possibly handle that.

That's all I know.

When you get a new set of data... *what do you get*?

A new .mdb file?

What's in it?

How is it being created - and why is it being created in this
hideously denormalized manner?

Could whoever is sending it be persuaded to use Access as if it were a
database, instead of a glorified spreadsheet???

John W. Vinson [MVP]

 

[John W. Vinson]

I know. Sure. Please see below:

Tablename = Account
FieldName = Account number range from 100000 thru 999999 (in currency).

Let me know if you need anything else. Thanks for your big help again!

ok... thought about it for a while and came up with a possible VBA
solution.

Assuming that you have a table [Accounts] with fields [Vendor] and an
arbitrary number of fields, where each field is an account number; and
a target table NewAccounts with fields Vendor, Account and Amount, try
this:

Private Sub MigrateAccount()
Dim db As DAO.Database
Dim tdf As DAO.Tabledef
Dim fld As Field
Dim qd As DAO.Querydef
Dim strSQL As String
Dim strFld As String

On Error GoTo Proc_Err

Set db = CurrentDb
Set tdf = db.TableDefs("Accounts")

For Each fld In tdf.Fields
strFld = fld.Name
If strFld <> "Vendor" Then
strSQL = "INSERT INTO NewAccounts([Vendor], [Account], [Amount])" _
& " SELECT [Vendor], " & Chr(34) & strFld & Chr(34) _
& "[" & strFld & "] FROM Accounts WHERE [" & strFld & "]" _
& " IS NOT NULL;"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
End If
Next fld

Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Number & " in MigrateAccount" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

This will loop through all the fields in Accounts, and generate a SQL
string
INSERT INTO NewAccounts([Vendor], [Account], [Amount]) SELECT
[Vendor], "003125", [003125] FROM Accounts WHERE [003125] IS NOT NULL;

(assuming that's one of the fields). It will then execute that query
and loop on to the next field.

John W. Vinson [MVP]