Multiple Values in one field - DB due asap

[Madison123]

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,SN8

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

 

[hk1]

I suspect you mean fields or columns when you say "FIVE DIFFERENT ROWS".

Storing the serial numbers in a separate table that's linked to your items table is the best way to do this. It isn't that difficult to implement either. However, if you prefer to go the simple router you can just make one text field and allow the users to store the serial numers in that field. Depending what you plan to do with these serial numbers later, you'll want to make sure the serial numbers are separated by a comma. Do realize that using one field will get messy very quickly. What if you want to be able to mark a given serial number as sold? Or link other records to a particular serial number? What if you want to perform validation on the serial number entered to make sure it fits a given criteria?

You will be better off storing the serial numbers in a separate text field. You'll have to create a subform for serial number management. Search will also be a little more complicated since you'll have to do join queries but welcome to the world of databases. If you want power, flexibility, and scalability you'll have to become accustomed to increased complexity.

 

[boblarson]

Read up on normalization if you can. You do not store multiple data in the same field in the same record. It isn't something to do. For one, getting the data back out is going to be a pain in the butt later if you do it that way.

So you use a separate table as HK1 said. It would be something like this:

MyMainTable
MyID - Autonumber (PK)
SomeField
AnotherField
' etc.

MySerialNumbers Table
SerialNoID - Autonumber (PK)
MyID - Long Integer (FK from MyMainTable)
SerialNum


And then you would have the main form be for MyMainTable and then a subform for MySerialNumbers table which then is linked (master/child links) by MyID and then you can add as many or as few serial numbers as you need for each item. And it becomes very easy then to report out on the data and not go jumping through hoops to try to get it.