convert lookup column from access to sql server express

[icccapital]

I created a database and front end in access 2007. I am trying to migrate to
sql server express 2005 with the same access front end. There is a lookup
column in access 2007 database that I am going to replace with a lookup table
and a relation table between the main table and the lookup table to hold the
multiple links between them.

I have a form that at the moment works well with the lookup column for
obvious reasons (you get the list of available values along with the selected
values for this particular record) I need to try to recreate this behavior
somehow with the SQL Server back end.

1) Is it possible to create a list box that looks up possible values in one
table and selected values from another?

2) is it possible to have a list box with check boxes as the select function
(it is easier for the users to understand)

thanks

 

[KARL DEWEY]

1) Is it possible to create a list box that looks up possible values in one
table and selected values from another?
Yes. Use a query for the list box source with the two tables joined like
this --
SELECT Table1.Data1, Table2.Data2
FROM Table1 INNER JOIN Table2 ON Table1.Data_X = Table2.Data_Y;

2) is it possible to have a list box with check boxes as the select function
(it is easier for the users to understand)
Yes. An Option Group is what you need to do this.

 

[icccapital]

Thanks for the reply.

However I am not positive I understand what is being done with regards to
the query and the list box source. I understand the query will return the
two columns of data, but how does that work with a list and then the
selected's within that list?

Maybe I should clarify my issue in case there was some confusion. I have a
lookup table with possible values (Matt, Jen, Steve...) and for each account
there can 0 or more of these people on an account. So what I would like to
have happen is that I have a list box with the list of all possible people
(ie from people table maybe) and then there is a table between the account
account table and the people table that contains the multiple connections.
So this is where the "selected" values should come from.

Can you clarify if your solution solves this problem or have a different
solution?

Thanks.

 

[KARL DEWEY]

You are on the right track. Post your table structure with field names and
datatype. Post sample data from the tables.

Do you want to pick a name and have it plug in the account number assoicated
with that person?

 

[icccapital]

Client Table
ClientCode (nchar(8))
....
DATAT
10thl ....
45ghi ...
78jku ...

ClientServiceToClient
ClientCode(nchar(8))
ClientServicePerson(nvarchar(255))
DATA
10thl Matt
10thl Steve
45ghi Steve
78jku Laurie
78jku Steve

ClientServiceList
ClientServicePerson(nvarChar(255))
DATA
Matt
Steve
Laurie

The form is based on the clientcode, which is the primary key for the
information. On the form I would like to have a control that would list out
Matt, Steve and Laurie and so for clientcode 10thl both Matt and Steve would
be selected and Laurie would be unselected. For 45ghi only steve would be
selected. etc. But in this way the user can unselect steve and select
laurie or unselect everyone or select all possible.

Does that help?

 

[KARL DEWEY]

You lost me.
You have a code (10thl) that represents two people (Matt & Steve).
Then you have three codes that represents Steve (10thl, 45ghi, and 78jku).

When you make the selection where is that selection stored and what are you
storing?

The problem is that if you store a name then there are multiple codes for
that person. If you store a code then there are multiple names for that code.

 

[icccapital]

Sorry if that was confusing. Let me try again to explain better. We have a
client table. A client is represented by a code (ie 10thl, 45ghi, 78jku).
We then have 1 or more client service people who service those clients. So
Steve could service both 10thl, 45ghi and 78jku whereas Laurie only services
78jku.

So my form is built off of a client. So a client will have an address, a
phone number etc. But there would also be a control for the person who
services that client. In access I had represented that as a lookup column in
the client table that allowed you to see all of the possible client service
people and select multiple ones.

To represent that same functionality in sql server express I would have the
schema below, with the ClientServiceToClient table I have all of the client
service people that work with that particular client. So the key in that
table is both clientcode and clientserviceperson. Now the idea is to have
all of the possible client service people (maybe pulled from the table
ClientServiceList) and the selected ones would come from those client service
people that are on that client. So the the select for that would be: SELECT
CS.ClientServicePerson FROM ClientServiceToClient CS WHERE CS.ClientCode =
"10thl";

Hopefully that makes more sense. Please let me know if not.

Client Table

 

[icccapital]

Sorry for the confusion, let me try to explain better. So we have clients
that are contained in the client table and the primary key is clientcode. In
the client table is a lot of information like address, etc. In access I had
a lookup column that contained the client service people that service this
client. Since that doesn't exist in normal sql world, the way I have created
it in SQL Express is to have a ClientServiceToClient table which contains the
clientcode and the clientserviceperson. Both of these make up the primary
key. So in this way a client can have multiple people who service it.

So in our example client 10thl is serviced by both Matt and Steve and we
also see that Laurie only services one account 78jku.

So on my form that is based on the client (represented by clientcode) I want
a control that is a drop down list of all client service people (maybe filled
from the clientservicelist table) and the selected client service people
would be those that work on that clients account i.e SELECT
ClientServicePerson FROM ClientServiceToClient WHERE ClientCode = "10thl";

Hopefully that makes more sense. Thanks for sticking with me on this.

 

[KARL DEWEY]

I think I understand now. Your combo query to pull list of all service
personnel the possibly service the client shown in the main form.

Try this --
SELECT ClientServicePerson
FROM ClientServiceToClient
WHERE [ClientServiceToClient].[ClientCode] =
[Forms]![YourFormName]![ClientCode];