Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Break out Amounts Query

Status
Not open for further replies.

jeffsal

Technical User
Oct 29, 2005
24
US
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

 
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
 
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
 
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)
Code:
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
 
Thanks for the code. Unfortunately I found out my odbc driver does not support union queries. Any other options?
Thanks

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

Code:
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:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top