Multiple values in one field?

[Sarah Doe]

Hi All,

I am new to Access and found this forum great help during one of my last issues.

I built a small Access database for a Team at work which collects the following information.

Name
serial Number
Date entered
Lead name

I have also made this table searchable by serial number. So a user enters a serial number into a field and any entry with that serial number is returned. The req changed from one serial number to up to five per entry. I just had 5 text boxes available to submit each serial number into a Table INTO FIVE DIFFERENT ROWS. However, as of this morning i have been asked to cater for 20 Serial numbers per entry. It does not make sense for me to have twenty new rows in my table, it does not seem efficient or correct.

I have been googling and discovered that maybe i can use a split? I want the user to enter multiple values like this

Serial Number: SN1,SN2,SN3,SN4 ,SN5,SN6,SN7,SN 8

and I want to be able to search an entry for a specific serial number. If the entry holds SN1 the whole entry should be returned.

So i want to use a comma as a delimiter. Will these then be stored in the database as one field or do i need to make changes?

Can anyone help me get started here? Just explain it to me as all the code examples online are confusing me

[Stewart Ross]

Hi. What is a serial number used for? I would have thought that for any one item of some kind there is only one serial number - in other words there can only be one unique identifier for one separate row in your table.

It is a first principal of relational database design not to have repeating groups within a single row - and what you've mentioned about serial numbers undoubtedly constitutes a repeating group.

The principal arises in part for the very reason you mention - how do you know how many fields to provide? How would you distinguish between them at all?

Could you advise what it is you are trying to achieve here, as it is quite unclear at present? If it is a search on a range of serial numbers for matching rows that is quite a different matter from the absolute no-no of storing multiple serial numbers in one field.

-Stewart

[Sarah Doe]

Hi Stewart,

I work on a sales Team. We have a Team dedicated to assigning Leads to Reps. A lead is a contract that is about to expire, so the rep can jump in and renew with the company.
I have been asked to build a small simple database that will allow the admin to insert all serial numbers associated with a Lead. This way they know if a certain serial number has already been associated with a Lead or does a lead need to be created. They may not need this info again for another 6 months to a year so its important they can come back and check this information.

At the moment I have just one Table.


Name
Date
Serial Number1
Serial Number2
Serial Number3
Serial Number4
Serial Number5
Serial Number6


so when the admin searches by serial number it checks all the rows before bringing back the relevant information.

I was wondering do I have to set up 20 rows for all 20 serial numbers? which involves 20 text boxes!

one lead number can have many serial numbers
one serial number can have many leads

[Stewart Ross]

Hi Sarah. I can't pretend to understand all the ins and outs of how you deal with your leads, but what you descibe has many more tables than you are currently trying to implement if you want to do it using a relational database.

I see at least four tables in your case:

Lead - which I think is some kind of synonym for company
Contract - which is where your serial number comes from, and which is linked 1-to-many to Lead (each Lead can have many contracts, each contract has just one Lead)
Rep - which contains your rep details, their name and so on
Rep Contract - Current link table which resolves the many-to-many relationship between Reps and Leads (really Contracts according to what you've told us).

I would recommend that you read our introductory article on Database Normalisation and Table Structures which explains in detail why you need to change the way you are trying to implement your tables.

If you wish to continue with a single-table approach I'd recommend that you do not do so using Access - it would completely waste the power of the relational database engine and involve you in complex design of user forms which will simply not work out for you. I'd suggest implementing something in Excel instead, whose flat-file approach may well be all you need in these circumstances if relational approaches are not being taken.

-Stewart