Multiple options for a single record

[dprocket]

Hello. I am a newbie to databases, and I've run into a bit of a problem.
Here is my situation. I have a table that has a list of my business
partners. I have separate tables that have order information, contact info,
etc.

I really want to include something that shows where each (state or region)
one business partners has service in.

For instance:
XYZ Company may have service in NY, MA and PA
ABC Company may have service in NY, NJ
123 Company may have service in MN
789 Company may have service in all 50 states.

Ultimately, I'd like to get to a solution where I can search my database to
find every business partner that has service in a particular state. For
instance:
"Find all partners with service in NY."

I have not figured out a way to build a table that allows my to have
multiple areas of service for a single record.

The best thing I've come up with is to create several tables:
StateListTable (contains all 50 states)
PartnersTable (contains all partners)

And then I would create a table that has records like this:
XYZ Company | NY
XYZ Company | MA
XYZ Company | PA
ABC Company | NY
ABC Company | NJ
123 Company | MN
789 Company |AL
789 Company |AK
....
....

The problem with this design is that it seems awfully bulky and I am not
sure how I would go about adding records to it or being able to search.

Can someone help me out?

 

[Tim Ferguson]

And then I would create a table that has records like this:
XYZ Company | NY
XYZ Company | MA
XYZ Company | PA
ABC Company | NY
ABC Company | NJ
123 Company | MN
789 Company |AL
789 Company |AK
...
...

Quite right. When you come to implement it, of course, you use the PK
values from the Companies table and from the States table (which probably
is the two-letter abbrev anyway, isn't it?). Enforce the relationships with
the two tables and you are away.

The problem with this design is that it seems awfully bulky

Stuff bulkiness -- if you run out of room you get a bigger HDD[2]. In
actual fact, you would take about eight bytes per record[1], so you can get
a lot of contacts into 2GB.

and I am not
sure how I would go about adding records to it

Depends on your UI needs, processes and so on. At simplest it's a subform
control, at hardest it's one line of SQL.

or being able to search.

That is what queries are for. Join the Companies table to the HasContactsIn
table and filter on HasContactsIn.StateAbbrev - easy!

[1] A long int is four bytes and a two-char string is two bytes and a
integer length.

[2] Yes, I know an Access database is limited to 2GB..

Hope that helps


Tim F

 

[dprocket]

Thanks. I'm happy to hear confirmation that my thoughts are confirmed,
since I'm quite an amatuer. I'm not to the point yet of setting up any forms
or queries, so it's quite possible I will be back with more questions.

 

[dprocket]

I'm still working on the same project, and am faced with another scenario.

What I would like to do is create a way to easily enter the states serviced
by my partners using a checkbox, or some other mechanism.

So, what I envision is something that allows me to pull up a single partner,
and then click a check box next to each state that shows that the area is
serviced.

The problem that I see with this is that my "StatesServiced" table provides
a single record for each state that each partner is servicing. Example:

XYZ Company | AR
XYZ Comapny | NY
XYZ Company | CA
ABC Company | CA
ABC Company | NY

So, what I am faced with is a way for me to easily create MULTIPLE records
in the "StatesServiced" table. Make sense? Is that possible?

 

[John Vinson]

I'm still working on the same project, and am faced with another scenario.

What I would like to do is create a way to easily enter the states serviced
by my partners using a checkbox, or some other mechanism.

So, what I envision is something that allows me to pull up a single partner,
and then click a check box next to each state that shows that the area is
serviced.

The problem that I see with this is that my "StatesServiced" table provides
a single record for each state that each partner is servicing. Example:

XYZ Company | AR
XYZ Comapny | NY
XYZ Company | CA
ABC Company | CA
ABC Company | NY

So, what I am faced with is a way for me to easily create MULTIPLE records
in the "StatesServiced" table. Make sense? Is that possible?

One convenient way would be to have a multiselect Listbox showing all
of the states. You could go down this list, selecting each relevant
state.

You'll need a bit of VBA code to move the selected records into your
Table. Here's a sample from one of my databases: adapt field and
controlnames to your app and it should do the job.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
' selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
' newly cleared rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
' currently in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub




John W. Vinson[MVP]