query from 2 columns into multiple columns 3

[anthony777]

ok my fault I have a column with all the bill codes in them lets say 665 and then the billing rate which is 1.21 is right next to it good the problem is I need to extract from the same two colums 4 more codes lets say I need to also extract 334 and the bill codes assoctiated with it into separate columns in the same query so it would look something like this

column 1 2 3 4
665 1.21 334 1.74

this all comes out of the one column that has all the codes and the other single column that has the rates so it looks like

column 1 column 2
665 1.21
334 1.74

thanks

 

[fneily]

my fault I have a column with all the bill codes in them lets say 665 and then the billing rate which is 1.21 is right next" That's the way it's supposed to be.
See:
faq701-3499

 

[PHV]

faq701-4233


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[anthony777]

yes that is right the problem is I have to separate each bill code into its own column and the rate for that code next to it for example

bill code rate
334 2.52
660 1.70
220 15.30

so the query has to produce
columns
1 2 3 4 5 6
334 2.52 660 1.70 220 15.30
see bill code separate colums their rate next column problem is the download does not transpose or separate this from the same column. so if I have 1 download with 7 billcodes and rates next to it my query has to produce 14 coulmns bill code and then matching rates

 

[dhookom]

You can't do this without providing a value/field that identifies the order across. Why does 660 come before 220?

I expect the multi-value crosstab solution in the FAQs might be part of the solution.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

As dhookom points out, you're not telling us the relationship between codes. So, I'm assuming you just want to dump the data horizontally, So the code referenced can be modified to put as many billcode/rates across the page as you want.

 

[dhookom]

You could create a new table named tblBillCodeRate with a single field nmed BCR and two records with values "BillCode" and "Rate". Then assuming your original table has the structure:
tblBillCodes
=============
ID [blue]Unique ID that will determine display order[/blue]
BillCode
Rate

Your query would have SQL like:

TRANSFORM First(IIf([BCR]="BillCode",[BillCode],[Rate])) AS Expr1
SELECT "Record" AS RowHead
FROM tblBillCodes, tblBillCodeRate
GROUP BY "Record"
PIVOT [ID] & [BCR];

[tt][blue]
RowHead 3BillCode 3Rate 4BillCode 4Rate 5BillCode 5Rate
[/blue]Record 334 2.52 660 1.7 220 15.3[/tt]

Duane
Hook'D on Access
MS Access MVP