473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Multiple values in one field?

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
Jan 6 '11 #1
3 2033
Stewart Ross
2,545 Expert Mod 2GB
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
Jan 6 '11 #2
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
Jan 6 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
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
Jan 6 '11 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Emmett Power | last post by:
Hi, I have a form on a web page with a number of radio buttons bound to the same field. Is it possible to set up the form so that users can select more than one radio button to submit multiple...
2
by: kgould | last post by:
I'm trying to use XMLStarlet to pull a value out of Microsoft's mssecure.xml file for patch availability. I'm an XML newb, so I'm struggling here. I want to pull the @Summary field value- I'm...
2
by: Jen F. | last post by:
I have inherited a medical database in which there are multiple values stored in a single field (ie. "Current Conditions" field might contain 1-20 different conditions, separated by comma (ie....
8
by: Chris A via AccessMonster.com | last post by:
I have an interesting problem that I have yet to come accross that I can't change data structure on because it is an export from filemaker I am reformatting for another dept. anyway. I have a table...
5
by: mctime | last post by:
Hello, I am attempting to split a raw data table into a new table that has split out a specific field in the raw data and created a new record for each split but I have come to an impasse due to...
4
by: sufian | last post by:
Below is the field where user enters his/her email address and the AJAX post request is sent to the server and the user sees the message: echo("<div id=\"message\" class=\"success\">Thank you! You...
7
by: narpet | last post by:
Hello all... I have a list box that is pulling it's data from a sql table. I have its selection mode set to multiextended. I have the display member for each item set to the Name field from the...
1
by: wendy184 | last post by:
I'm used to using 2007 which allows multiple values in the lookup wizard, this helps hugely with my queries as the database i'm building has information on one parent who may have up to 5 kids. ...
1
by: saagardn | last post by:
I am using Access 2007 on an XP machine. Is it possible to update one field with multiple values using a single SQL statement? I have tired to do this with the query builder, but keep getting a...
2
by: MicaK | last post by:
Good Morning, I am new to this forum, and extremely new to VBA, so there may be a very simple explanation to this. I also apologize if I am giving you and excessive amount of detail. I have a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.