Jeane:
I'm having a little difficulty visualising just how you want this to work,
so I think it might be as well to briefly go over how a relational database
works.
Firstly lets get the terminology right. In a relational database data is
stored in tables (a file in Access can contain many tables, along with forms,
reports, queries etc.). Each table represents an entity type, e.g. in your
case Salespeople. The columns (also called, somewhat incorrectly, fields)
represent the attribute types of the entity type, e.g. FirstName, LastName
etc. Its important that a table only has columns which represent attribute
types specific to the entity type, so FirstName and LastName are fine for a
Salespeople table, but say for the stores you have a column City as part of
its address then that should not be in the Salespeople table as its an
attribute of a separate Stores entity type, not of Salespeople, so it should
be in a separate Stores table related to the Salespeople table. This ensures
that there is no 'redundancy', each 'fact' being stored once and once only,
thus protecting against 'update anomalies' which can leave the data in an
inconsistent state.
Just how Salespeople and Stores are related depends on the type of
relationship between them. If each store is related to only one salesperson,
with each salesperson being related to more than one store then the
relationship between Salespeople and Stores is one-to-many, so there simply
needs to be a foreign key column in Stores. This would usually be a numeric
SalespersonID (but not an autonumber) referencing the primary key of
Salespeople, which can be an autonumber.
If, on the other hand, each store can be related to more than one
salesperson, as well as each salesperson being related to more than one
store, then the relationship type is many-to-many. This is represented by
having a third table StoresSalespeople say, which has two foreign key columns
(not autonumbers) SalesPersonID and StoreID referencing the primary keys of
the other two tables. You'll see that the many-to-many relationship has thus
been resolved into two one-to-many relationships:
SalesPeople----<StoresSalespeople>----Stores
The StoresSalespeople table, while representing the relationship type, still
represents an entity type because relationship types are just a special kind
of entity type. From your description, however, I suspect the relationship
is a simple one-to-many one so you just need the two tables.
In a form the usual way of representing tables related like this would be to
have a Salespeople form in single form view and within it a Stores subform in
continuous form view, the parent and subform being linked on the
SalesPersonID columns. As you navigate to each salesperson in the main
parent form the subform would list all the stores for that salesperson, each
as a separate row (also called, again somewhat incorrectly, a record). I'm
not quite sure what you have in mind by "put a message next to it" but this
might simply require an extra column in the subform's underlying Stores
table. You'd then simply have to scroll down the subform and enter data into
the relevant column in the form.
If you want to enter and store more than one "message" per store then you'd
need another related table for this StoreMessages say, with a foreign key
StoreID column and columns such as MessageDate, MessageText (the latter could
be a memo field if you want to store more than 255 characters per message).
These could be in a separate 'correlated' subform on the main Salespeople
form if you wish, or you could open a separate form from a button on the
subform to show/add/edit the messages for the selected store.
Let me know if you think this is the sort of thing you have in mind. I can
walk you through the details of setting it up if necessary. I know it might
sound a little scary if you are new to Access, but its not difficult to
implement. The key thing is getting the table structures right so they model
the real world situation accurately.
What, incidentally, do you mean by "The file is updated and saved everyday"?
Is this a separate file from your Access database? In an Access database
the data is saved as you enter/edit it; its not like a Word document or an
Excel workbook where you have to save the file to retain any changes you've
made.
Ken Sheridan
Stafford, England
Jeane said:
Hi Ken,
Well, this might be a little more involved than I thought. The file consist
of first/last/address/etc... the autonumber is their ID number so I don't
need anything for that. The last column is the one I would like the drop down
in. You know, choose it and then scroll down to the number you want so you
can put a message next to it The file is updated and saved everyday so I
wont be losing anything by doing this. The reason I want to use Access is
because everyone wants the exact information in a different way, but I would
like to make notes (temp changes) on the stores when needed.
Hope this is better, and hope the answer won't be nearly as scary to me!
thanks ken
Ken Sheridan said:
Jeane:
Lets say you have tables SalesPeople, with a primary key column
SalespersonID and a Stores table, with a foreign key SalesPersonID
referencing the primary key of SalesPeople, i.e. the tables are related
on-to-many.
You don't say how you want the store information displayed, so lets assume
you want to use a dialogue form in which you can select a salesperson from a
combo box's list, then select a store from another combo box's list which
shows the selected salesperson's stores. First create an unbound form and
add a combo box to it. Set up its properties something like this so it lists
the people by name:
Name: cboSalesPerson
RowSource:
SELECT SalesPersonID, FirstName & " " & LastName AS FullName
FROM SalesPeople ORDER BY LastName, FirstName;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
(or rough equivalent in inches, but the first dimension must be zero to hide
the first column).
Add another combo box to the form, with its properties like this:
Name: cboStore
RowSource:
SELECT StoreNumber, StoreName
FROM Stores ORDER BY StoreName
WHERE SalesPersonID = Form!cboSalesperson;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
(if you want it to show both columns in the list then change the first
dimension to something other than zero – experiment to get the best fit – and
set the ListWidth property to the sum of the two dimensions, so for a
ColumnWidths property of 3cm;6cm the ListWidth should be 9cm. Only the first
column will show in the control after you make a selection, though)
To get the cboStore combo box to show only the store names for the selected
salesperson you need to requery it in the AfterUpdate event procedure of the
cboSalesperson combo box with the following line of code:
Me.cboStore.Requery
To open a form, frmStores say, based on the Stores table at the selected
store you put some code in the AfterUpdate event procedure of the cboStore
combo box:
Dim strCriteria As String
strCriteria = "StoreNumber = " & Me.cboStore
DoCmd.OpenForm "frmStores", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
This assumes the StoreNumber column is a number data type. If it were text
data type you'd wrap the value in quotes characters like so:
strCriteria = "StoreNumber = """ & Me.cboStore & """"
When you select a store from the list the frmStores form should open
filtered to that store. By opening it in dialogue mode with the acDialog
setting of the WindowMode argument it will have to be closed again (or
hidden) before you van return to the unbound dialogue form to select another
salesperson and/or store.
Remember that any table, column, control names etc which include spaces or
other special characters should be wrapped in brackets [like this].
Ken Sheridan
Stafford, England
Jeane said:
I have 20 salespeople and each one has many stores (one has 23) and I would
like to be able to go to the Store Number Column and have the list of store
numbers for that person so that I can choose the one I am looking for. I have
very little experiance with Access so I apologize for the Beginners question,
but please help!!
jeane
