Break out Amounts Query

[jeffsal]

I dont know if this is possible but I Can not figure it out. The following are two columns from a table. The letters in the allocation column are user initials and the number following is a percentage due them from the amount. Can not change the table format.

Amount Allocation
15,000 TMS 80.00KPS 20.00
10,000 KPS 100.00
6,000 LVS 20.00JWS 50.00KPS 30.00

I'm trying to get a query to show as follows breaking out the percentages of the amount for each user.

Amount USER
12,000 TMS
3,000 KPS
10,000 KPS
1,200 LVS
3,000 JWS
1,800 KPS

Hope it's clear enough. Thanks for any help.

Jeffsal

 

[lespaul]

is the structure of the Allocation field always:

three characters space two digits decimal two digits three characters space two digits decimal two digits



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[jeffsal]

Except when there is only one user and the percentage is 100.00 as in the second row of the table. Thanks for replying.

Jeffsal

 

[lespaul]

Is there a maximum number of user initials/percentages? Above you show one that has 3, could there be one that has 10? If you have a maximum, a SQL solution would work, but if today you have a max of 3 and tomorrow it could be 10, you really wouldn't want to use this permanently.

I'm sure someone here could write a function that would do it easily, but I'm not that someone!

Here's the SQL solution (you would have to add more to it to get more than 3 results)

SELECT Left(Left(Allocation, 9), 3) As User, Mid(Left(Allocation, 9), 5, 5) As Percent, "FirstUser" FROM TableName
UNION SELECT Left(Mid(Allocation, 10, 9), 3), Mid(Mid(Allocation, 10, 9), 5, 5), "SecondUser" FROM TableName
UNION SELECT Left(Right(Allocation, 9), 3), Mid(Right(Allocation, 9), 5, 5), "ThirdUser" FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[jeffsal]

Thanks for the code. Unfortunately I found out my odbc driver does not support union queries. Any other options?
Thanks

Jeffsal

 

[lespaul]

but you're using access right? then create three separate queries in access and save them:

Query1:
SELECT Left(Left(Allocation, 9), 3), Mid(Left(Allocation, 9), 5, 5), "FirstUser" FROM TableName

Query2:
SELECT Left(Mid(Allocation, 10, 9), 3), Mid(Mid(Allocation, 10, 9), 5, 5), "SecondUser" FROM TableName

Query3:
SELECT Left(Right(Allocation, 9), 3), Mid(Right(Allocation, 9), 5, 5), "ThirdUser" FROM TableName

and then run a 4th access query:

SELECT * FROM Query1
UNION
SELECT * FROM Query2
UNION
SELECT * FROM Query3

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins