Unless there's a REALLY good reason, then you should separate the data into
two tables. Try to think of the contents of a table as being a list of
similar entities. So Companies would be in a separate table from People,
because they are dissimilar.
If you are using Access create the tables you need, then go the
relationships (icon on toolbar) to indicate how the tables are related.
Once Access "knows" how the tables are related, you can go into the Query
Wizard and create a query (or use design view)
View the query to ensure the results are what you expect.
Switch to SQL view of the query, and you'll see the SQL statement needed to
select the data.
When you are adding people to database, you want to add multiple companies.
I assume therefore, that you'll have three tables (many to many)
Present the form with fields for the Person information, and checkboxes for
the available companies. To make life easier, give each checkbox the same
name, but a different value. For example:
<input type=checkbox name=Companies value="3">McDon alds
<input type=checkbox name=Companies value="4">Burge r King
Where 3 and 4 are the UniqueIDs of the two companies.
On the page the form is posted to, "Requesting " the Companies will return a
comma separated list of the selected companies. So if both of the values
were selected in the example above, then Request.Form("C ompanies") would
return "3, 4"
You will then have to insert the data in three steps.
1) insert the person's information
2)get the new person's ID
3) insert the companies
<%
'Step One
Dim sSQL
Dim RS
Dim PersonID
Dim arrCompanies
Dim iLoop
sSQL="INSERT INTO People (FirstName, LastName) VALUES('Joe',
'Blow')" 'Obviously you would use the values passed in
CN.Execute sSQL 'I'm assuming CN is a properly opened connection
object
'Step Two
Set RS=CN.Execute(" SELECT @@IDENTITY") 'returns the most recently
created Identity on this connection
PersonID=RS.Fie lds(0).Value
Set RS=nothing
'Step Three
arrCompanies=Sp lit(Request.For m("Companies"), ",") 'split takes a
string and "splits" it into an array based on the delimiter (in this case a
comma)
For iLoop = 0 to UBound(arrCompa nies)
sSQL="INSERT INTO CompaniesPeople Join (PersonID, CompanyID)
VALUES(" & PersonID & ", " & Trim(arrCompani es(iLoop)) & ")"
CN.Execute sSQL
Next
%>
Bear in mind, that if any of the inserts fail the previous ones would have
succeeded so you would have only some of the values in.
A better solution would use a transaction, and the best solution would use a
Stored Procedure (Sql Server)
"Christophe r Brandsdal" <ch***********@ c2i.net> wrote in message
news:ev******** ******@TK2MSFTN GP12.phx.gbl...
Ok. Thanks I understand.
But the the problem is that both companies and persons are in the same
table...
Should I change it to 2 different tables???
and how will the code look like with the join command???
When I add a person to the database I have to be able to select multiple
companies for that person...
(the companies are added in advance)
"TomB" <sh*****@hotmai l.com> skrev i melding
news:O1******** ******@TK2MSFTN GP12.phx.gbl... If you have two tables in your database, one called Companies and one called People, you have to make a decision....
If you just want to track a person's current company, and you are
assuming that the people only work for one company at a time, then you add a
CompanyID foreign key field to the People table. This is a one-to-many
relationship with the company being on the "one" side and the People
being the "many", thus one company has many people working for it.
If you want to track a person's employment history, or allow for
multiple companies then you need to create a many-to-many relationship-- meaning
one company has many people working for it, but also one person has worked
(or is working) for many companies.
To create the many-to-many relationship you create a "join" table, which
contains foreign keys to both the Company table and the People table.
Usually, the join table's primary key is the combination of the Foreign
Keys. This prevents duplicate entries. There are times, however; when
a duplicate entry would be acceptable. For example: if you are tracking
an employment history, it could be possible for a person to work at one
company, leave it, then come back to it. If you'd used the Foreign keys
as Primary key, then you wouldn't be able to add the return to the same
company.
Your idea to store the information in a single field (comma separated)
is a problematic solution. Besides being difficult to maintain and
parse....how would you decide how much data the field could hold?
"Christophe r Brandsdal" <ch***********@ c2i.net> wrote in message
news:eB******** ******@TK2MSFTN GP12.phx.gbl... Hi!
I have a delicatg problem....
I have made a registration form for adding my friends information in a
database. The problem is that I want to connect the persons with
companies in the same database. I wonder how i could connect a person to several
other id's in a table.
I wonder if I can make a string in the database that holds all the
id's separated with , for example....?
the string would then look like this:
1,4,6,22,56
for example...
But how can I then open this string and separate the numbers from the
, ???
Hmm..
This may be difficult to ynderstand, but I have tried my best to
explain ;)
Christopher Brandsdal
