Many Records to one 1

[timhans]

Hello, not sure if this is the right forum for this questions. I have a crystal report which I imported into Access, the table is like so

ID Insurance Name

ID1 Ins1 Name1
ID1 Ins2 Name1
ID1 Ins3 Name1
ID2 Ins1 Name2
ID3 Ins1 Name3
ID3 Ins2 Name3
ID3 Ins3 Name3
ID3 Ins4 Name3
ect

But I need
ID Insurance1 Insurance2 insurance3 insurance4 Name

ID1 Ins1 Ins2 Ins3 Name1
ID2 Ins1 Ins2 Name2
ID3 Ins1 Ins2 Ins3 Ins4 Name3
ect
where ins1 ins2 ect can all be different for different names. There are about 20,000 records, if I can get it in to the desired structure there will be about 8,000 records. Thanks for any help you can offer

 

[bronc]

Hello

Is it possible to run code in one form eg Sub Form1Code() by calling it from another? Something like this:

Sub Form2Code()
Form1Code
End Sub

 

[dhookom]

I doubt your samples mirror reality as "Ins1" won't repeat for each insured name. I expect these will vary widely.

First create a totals/ranking query [qgrpInsRanking]:

SELECT tblInsurance.ID, tblInsurance.Insurance, tblInsurance.InsuredName, Count([tblInsurance]![ID]) AS ColHead
FROM tblInsurance INNER JOIN tblInsurance AS tblInsurance_1 ON (tblInsurance.InsuredName = tblInsurance_1.InsuredName) AND (tblInsurance.ID = tblInsurance_1.ID)
WHERE (((tblInsurance.Insurance)>=[tblInsurance_1]![Insurance]))
GROUP BY tblInsurance.ID, tblInsurance.Insurance, tblInsurance.InsuredName;

Then create a crosstab query from your ranking query:

TRANSFORM First(qgrpInsRanking.Insurance) AS FirstOfInsurance
SELECT qgrpInsRanking.ID, qgrpInsRanking.InsuredName
FROM qgrpInsRanking
GROUP BY qgrpInsRanking.ID, qgrpInsRanking.InsuredName
PIVOT "Ins" & [ColHead];

Duane
Hook'D on Access
MS Access MVP

 

[timhans]

Hello Duane, thank you for the code, I can kind of follow what you wrote but could not have gotten close to writing it. I have tweaked every thing and gotten it to run. Is there a way to move the Insurance codes for any record forward i.e any person has at most 4 insurance providers and the code produces about 50 fields for insurance codes, can the insurance codes be moved forward so that only the 1st 2nd 3rd or 4th fields and populated depending on the person and the number of Ins providers they have. Thank you again for the help you have already provided. Tim

 

[dhookom]

I didn't think my solution produced more than 4 columns if no one person had more than 4 insurances. What is the range of numbers in the ColHead column of the first query?

YOu may need to remove the join between InsuredName fields in the first query. Your data suggested the InsuredName field was always dependent on the ID field. If this is not the case you need to remove a join. Then you would have to tell us which name field you want to display.

Duane
Hook'D on Access
MS Access MVP

 

[timhans]

Hello, I removed the join in the first query and this helped some, I know have only 18 insurance fields, the range in the ColHead column is 1 to 30, it was 1 to 220 and every thing dose depend on the ID field, sorry if I was confusing.

 

[timhans]

Also if this matter's there are 308 unique insurance company's

 

[timhans]

I did a count on the repetitions of any ID and it turns out some are repeated up to 18 times i.e some people have up up 18 insurance entries, when I looked, all were repetitions of the same entry. So some one might have two distinct insurance entries with one repeated 16 times. So I think the query is working as there are 18 insurance fields. Is it possible then to get for any record(now unique)to be so that the insurance fields with data are juxtaposed starting with field 1, now say if a ID has 3 insurance value's they are something like field 1,field 5, field 11. Thanks

 

[dhookom]

I am lost regarding your request. I don't understand what you mean by "juxtaposed". It would help if you provided some samples.

Duane
Hook'D on Access
MS Access MVP

 

[timhans]

Hello Duane,Thanks for your help I finally got it to work, this is my first Crystal report and I was a little confused by it. Tim

 

[dhookom]

I thought this was an Access forum and Access report, not Crystal.

Duane
Hook'D on Access
MS Access MVP

 

[timhans]

Yes it is, a crystal report is where I got the data for my Access tables, it came with fields that were related to the to the fields I was interested in and could not just be deleted and I found them confusing and difficult to work with, thank you again for your help, it was valuable to my progress. Tim