Multiple entries per id

[pspencer]

I need help with a spreadsheet.

The table has retirees listed and their beneficiaries. So this is how it is set up:

Retiree ID Bene Name
123 wife
123 child1
123 child2
456 wife
456 mother
456 child1
456 child2

I need to have one line item per retiree like this:

Retiree ID Bene 1 Bene2 Bene2 Bene3
123
456

How can i do this? I tired pivot tables but that didn't work.

Thanks in advance.

Trice

 

[fneily]

You mean you need help with a table, not spreadsheet. This is Access, not Excel. Or did you post in the wrong forum?
If an Access table, see:
faq701-3499

 

[dhookom]

There is a similar but more generic function at faq701-4233.

If you actually need separate columns, you can use a crosstab query that numbers columns using DCount(). I just replied to someone in a queries thread on how to do this thread701-1423344

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pspencer]

Hi fneily,

Actually I meant to change the wording. I have it in both excel and access.

Here are the fields I am working with:
Retiree Id
Related ID
Relative Name
Beneficiary Type
Relative Relationship
Relative Birth Date
Relative Gender
Beneficiary Percentage
Plan
Payment Status

There are up to 7 related id's and info for each retiree. How can I related the code in the example that you mentioned? i know about putting the code in a module but where do I go from there?

Thanks in advance for your help.

Trice

 

[dhookom]

I pointed you in a couple different directions. They varied in that one solution created a single string value with all related values in it. The other solution create a separate column for each related value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]