collate numbers from several fields into one field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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.
 
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.
 
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?
 
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;
 
Thanks "Duane Hookom" thats great, exactly what I needed, this has also
helped solve a few problems I have had with other databases!

Stu
 
Back
Top