Data Problem - Excel or Access?

keith16

Hope someone can help me here. Seems like it should be an easy fix but is driving me mad.

So on the attached excel there are two tabs which sum up the issue I am having.

Essentially, I have customer table with a number of duplicate records for each unique contract number I have.

I want to normalise my data by eliminating the duplicates for each customer name / ID and have one field that lists each contract they have and the customer will only be listed once.

Not sure what the best approach is, maybe create a second table and join them? Or is there a nifty way I can do it in excel?

Any suggestions much appreciated.

Sup08

DId you try a pivot table?

Sup08

Excel 2007/2010
Select data/Cell range range
Data Tab
select Subtotal and then Customer ID use function average.
The can then be filtered by custom ID

Otherwise access would be better with a subtable/subform

keith16

Sup08 wrote: »

DId you try a pivot table?

Hi Sup08,

Yes, I have looked at the data in a pivot table and it is close to what I want.

But what I really want is a table that collates all the distinct contract numbers in one field for each unique customer.

So a pivot will give me;

1 Tom 50
51
52

When what i need is;

1 Tom 50, 51, 52

Sup08

Well then you need a crosstab query in Access. (I think)

keith16

Sup08 wrote: »

Well then you need a crosstab query in Access. (I think)

Thanks Sup08, that subtotal suggestion wasn't bad, it is giving me distinct customer rows, but I can only get it to average, rather than list each contract number in the subtotal row.

Will give the cross tab in access a go. Been a while since I did one!

keith16

Actually a cross tab won't do it. I'm probably not being clear enough.

So I can get rid of the duplicates and list out each of my customers individually. The duplicates exist because each customer has a number of different contracts.

So when I have my query that returns each unique customer record, can I create one calculated field that contains each contract per unique customer?

Sup08

keith16 wrote: »

So when I have my query that returns each unique customer record, can I create one calculated field that contains each contract per unique customer?

The problem is that the only unique field is the contract, because the customer has several contracts, their field is not unique, thus the problem.

It can be done in a report in access like this.


report.PNG

Sup08

Here is a pivot table in Excel with the totals removed, see if this way works.
The only thing is you will have to run the pivot table each time data is added.

The access report is probably the best as it will autmatically update each time it is run.

keith16

Sup08 wrote: »

Here is a pivot table in Excel with the totals removed, see if this way works.
The only thing is you will have to run the pivot table each time data is added.

The access report is probably the best as it will autmatically update each time it is run.

Hi Sup08,

That works in theory but my dataset is HUGE. May have to split it up.

Thanks for all your input so far.