collate numbers from several fields into one field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stumac - 24 Jul 2006 14:21 GMT

Hi I currently have a table with 10 seperate numeric fields, for each record
there can be between 1 and 10 of thiese fields populated (by unique numbers
i.e no number should be replicated twice throughout the whole table). Is it
possible to get all these numbers into one field and ensure no duplicates
have been entered, I usually do this via an export to excel and manual
minipulation however this is becoming increasingly time consuming as the
amount of allocated numbers is growing.

Thanks in advance,

Stu

Reply to this Message

Duane Hookom - 24 Jul 2006 15:24 GMT

Do you have some sample records and desired "get all these numbers into one
field" values?
Can you provide at least a little background on why you are doing this?

Signature

Duane Hookom
MS Access MVP

> Hi I currently have a table with 10 seperate numeric fields, for each
> record
[quoted text clipped - 10 lines]
>
> Stu

Reply to this Message

stumac - 24 Jul 2006 15:46 GMT

Hi, sorry for not being clearer with this I wasn't sure of what would be
required. The database has been set up in order to manage a staff lottery
where each member is allocated unique numbers (between 1 and 10 numbers each
depending on there preference)

The numbers used so far are all betweeen 1 and 5000 (they are allocated
randomly not in order), I do not requirer any other detail to be carried into
the one field other than a list of all the numbers allocated to date.

The fields in the table are as follows

Name, employee number, number1, number2, number3, number4...........

each of the number fields will either have a unique value or be null.
i.e.
Name    Employee number        number1    number2    number3
J Smith    3555            3502    261    88
D Walker    4555            982    606
D Webb    5555            66
K Peters    2222            2    101

and i require something like:

Allocated numbers
3502
261
88
982
606
66
2
101

Hope this is clearer.

> Do you have some sample records and desired "get all these numbers into one
> field" values?
[quoted text clipped - 14 lines]
> >
> > Stu

Reply to this Message

Duane Hookom - 24 Jul 2006 16:02 GMT

Can you change your table structure to

EmployeeNumber
TheNumber

If an employee has 10 numbers, this would create 10 records. You can set a
unique index on TheNumber so there can't be any duplicates.

Signature

Duane Hookom
MS Access MVP

> Hi, sorry for not being clearer with this I wasn't sure of what would be
> required. The database has been set up in order to manage a staff lottery
[quoted text clipped - 55 lines]
>> >
>> > Stu

Reply to this Message

stumac - 24 Jul 2006 18:03 GMT

Unfortunatley someone else created this database and has been used for a few
months now therefore all query's, forms, reports and macros are based on this
system and would take some time to redesign. Also there is around 2000
numbers already allocated so it would take a while to try and convert that to
this method.

Any Ideas?

> Can you change your table structure to
>
[quoted text clipped - 63 lines]
> >> >
> >> > Stu

Reply to this Message

Duane Hookom - 24 Jul 2006 18:31 GMT

To get a unique list of numbers, you can normalize with a union query;

SELECT number1 as Number
FROM tblNoName
WHERE number1 is not null
UNION ALL
SELECT number2
FROM tblNoName
WHERE number2 is not null
UNION ALL
SELECT number3
FROM tblNoName
WHERE number3 is not null
UNION ALL
SELECT number4
FROM tblNoName
WHERE number4 is not null
UNION ALL
SELECT number5
FROM tblNoName
WHERE number5 is not null
UNION ALL
SELECT number6
FROM tblNoName
WHERE number6 is not null
UNION ALL
SELECT number7
FROM tblNoName
WHERE number7 is not null
UNION ALL
SELECT number8
FROM tblNoName
WHERE number8 is not null
UNION ALL
SELECT number9
FROM tblNoName
WHERE number9 is not null
UNION ALL
SELECT number10
FROM tblNoName
WHERE number10 is not null;

Signature

Duane Hookom
MS Access MVP

> Unfortunatley someone else created this database and has been used for a
> few
[quoted text clipped - 83 lines]
>> >> >
>> >> > Stu

Reply to this Message

stumac - 25 Jul 2006 09:20 GMT

Thanks "Duane Hookom" thats great, exactly what I needed, this has also
helped solve a few problems I have had with other databases!

Stu

> To get a unique list of numbers, you can normalize with a union query;
>
[quoted text clipped - 125 lines]
> >> >> >
> >> >> > Stu

Reply to this Message