Sue:
I'll take your points one by one:
I know this is a detailed question. Hope that's acceptable in this type of
forum . . .? Certainly it's invaluable for me if you are able to offer any
further tips.
There's no extra charge for detailed questions ):> Obviously you should try
and work things out for yourself as much as possible as that way you'll learn
a lot more than merely repeating parrot fashion whatever we give you, and
once we've explained something in relation to a particular task you should
look for where else the same principles might apply. You'll find that some
respondents here will give short replies to a large number of posts, pointing
people in the right general direction. I on the other hand prefer to go into
more detail with a limited number of selected posts. Something which helps
us help you, though, is if you tell us what you are trying to achieve in
terms of the real world scenario which the database is modelling rather than
asking how to do a particular thing in terms of the database objects itself.
If you ask something 'how do I do A, B and C by means of a list box on a
form?' then this might well be a 'road to Dublin' question to which we would
answer, and I speak as an Irishman, 'If I were you, I wouldn't start from
here'.
A general question. As per my original post, I've been charged with creating
a reasonably complicated database with what I now realise is a level of
training fairly inadequate to the task in hand (eg. I don't know anything
about code). Given the fact I have an extremely short deadline which isn't
moveable, what do you think about the idea of keeping a lot of the data in
two large tables (plus a few many to manys linking to each of these) thereby
decreasing the need for code which I am unable to write? I've read a lot on
the site about people doing this and the consensus seems to be that if it
works, and you don't have the skills to do anything else, then fine albeit
there are much better ways of storing and managing data if you know how. If I
contravene some (not all) normalisation conventions in the interests of
pragmatism will I end up regretting it: for example, will the database be
really slow when we've entered lots of data in it. Will it be more or less
likely to throw up error messages if I keep things simple in this way? Do you
need more info to answer this question.
You won't get runtime errors but your are more likely to have inconsistent
data entered if the tables are not fully normalized. Normalization
eliminates redundancy so that each 'fact' is stated once only in the
database. That way the 'fact' cannot be inconsistently entered, whereas if
the same 'fact' is entered more than once there is the risk of it being
entered in different ways each time, either incorrectly or just differently
e.g. my name could be entered as Ken Sheridan, Kenneth Sheridan or K W
Sheridan, and I did in fact find myself in three separate guises in one
database as author of technical articles in my own sphere of work,
environmental planning. As far as the database is concerned there are three
different authors involved here!
However, many databases do exhibit a degree of redundancy, and you can see
an example in the case of the sample Northwind database which comes with
Access. An example of its lack of proper normalization can be found in its
Customers table. You'll see that this has City, Region and Country columns
so we are told numerous times that São Paulo is in SP region (as is Resende)
and that SP region is in Brazil. Not only does this require repetitive data
entry, but more importantly it opens up the risk of inconsistent data, e.g.
it would be perfectly possible to put São Paulo in California in one row and
California in Ireland! Proper normalization as I described above would
prevent this as the fact that São Paulo is in SP region would be stored only
once in the database as would the fact that SP region is in Brazil and
California is in the USA.
The Customers table from Northwind nevertheless 'works', of course, but it
is open to bad data being entered, so the same would be true with your
tables. It is best to aim for a normalized design, otherwise you are
sacrificing the advantages of a relational database compared with a
spreadsheet. At the end of the day its for you to decide to what degree you
want to pursue a well normalized model.
First, re the code you suggested for the command button to open
frmJournalists, does it replace all the existing code (shown below) or just
some of it. If just some of it, what does it replace and where is it inserted?
Const conFORM = "frmJournalists"
Const conMESSAGE = "No current individual record."
Dim strCriteria As String
Dim varIDIndividual As Variant
varIDIndividual = Me.IDIndividual
If Not IsNull(varIDIndividual) Then
strCriteria = "IDIndividual = " & Me.IDIndividual
' ensure current Individuals record is saved
Me.Dirty = False
' open journalists form
DoCmd.OpenForm conFORM, _
WhereCondition:=strCriteria, _
WindowMode:=acDialog, _
OpenArgs:=varIDIndividual
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If
End Sub
Private Sub cmdOpenfrmJournalists_Click()
On Error GoTo Err_cmdOpenfrmJournalists_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJournalists"
stLinkCriteria = "[IDIndividual]=" & Me![IDIndividual]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenfrmJournalists_Click:
Exit Sub
Err_cmdOpenfrmJournalists_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmJournalists_Click
End Sub
My code replaces your original code with the exception of the first two and
last six lines, so it goes between these:
Private Sub cmdOpenfrmJournalists_Click()
On Error GoTo Err_cmdOpenfrmJournalists_Click
'<<<my code goes in here>>>'
Exit_cmdOpenfrmJournalists_Click:
Exit Sub
Err_cmdOpenfrmJournalists_Click:
MsgBox Err.Description
Resume Exit_cmdOpenfrmJournalists_Click
End Sub
Essentially mine does the same thing as the wizard generated code, but with
a few extra whistles and bells. The code I gave you for the frmJournalists
form's Open event procedure goes into new procedure in that form's module of
course as currently it won't have any code.
Re the question of banks, I am assuming that we will only record one bank
per individual to try and keep things simple. This will almost certainly be
the case. If they change their bank account, we'll just overwrite their bank
details as it won't be necessary to hold previous account details. Am I right
in assuming from your response that I will have a a linking table with
IDIndividual and IDBank as foreign keys (and in long integer number format)
and that tblBanks will contain the bank name, address details, sort code, ac
no and bank reference? I tend to give linking tables their own auto number
primary key (so they have 3 fields in total). Should I do that here? Should I
do it anywhere?
Related to setting up the relationship between individuals and banks, I've
already set up many to many tables (tblIndividuals and tblStandingOrders,
tblIndividuals and tblCAFGAYEDons) with linking tables with an autonumber PK
and FKs on the principles above. This allows a subform on the individuals
main form allowing viewing of each donation and automated subtotals. This all
works fine. Is it ok for the bank details to be in a separate many to many
relationship with tblIndividuals that the donations tables as above? Will
this cause any complications when I try to create a form to enter standing
orders using individuals's bank reference from the tblBanks to find their
record. (Don't want to use the existing subform for data entry of standing
orders, just for viewing, as too many clicks otherwise). Will a query bring
this all together for a data entry form without any complications or am I
likely to need more code if I take this approach?
Its in this part of the database where I think you are going astray. Now,
you already know that each table represents an 'entity type' and each column
in a table represents an 'attribute type' of that entity type, for instance
Sue and ken are attributes of type FirstName (i.e., a column) of entity type
People (i.e. a table). Banks is an entity type, so you correctly have a
table tblBanks. Each row represents one bank. Attributes of the entity type
are things like its name and address, so there'd be columns for these. Each
bank will have many customers, so the relationship with tblIndividuals is
one-to-many. In the jargon the table on the 'one' side is known as the
'referenced table' and that on the 'many' side as the 'referencing' table.
Sometimes folksy terms like 'parent' and 'child' are used instead, but I
prefer the more formal terms because they tell you exactly how the
relationship works, the tblIndividuals table 'refernces' the tblBanks table,
which it does by having a foreign key column BankID in tblIndividuals which
refernces the primary key column BankID in tblBanks. You have it the other
way around, which as you've discovered means that a bank can have only one
customer. Now this would cut down the queues at the teller's window but
would not be good business.
Now you could simply put a BankID column in tblIndividuals to act as a
foreign key, along with other columns like AccountNumber etc., but even
though this is a one-to-many relationship type, as you only want to record
one set of bank details per person, I'd model it in the same way as if it
were a many-to-many relationship type by having a 'linking' table (again
while folksy terms like this are OK, try and think of these sort of tables as
what they really are, models of a relationship type). Although a one-to-many
relationship is not normally modelled by a table, but by a foreign key column
in the referencing table, there is absolutely no reason why they should not
be, and their use in this way is recommended by Chris date, one of the
greatest of the great and the good of the relational database world, as a
means of avoiding Null foreign keys (in your case if an individual has no
bank account).
The relationship type between individuals and banks is really 'Accounts' as
its through their account that a person is related to a bank, so the table
could be called BankAccounts say. Once we understand that a person's account
is the relationship with the bank its clear where things like the sort code,
account number etc go. These are of course attributes of the account so they
are columns in the BankAccounts table along with the two foreign key columns
IDIndividual and BankID. If you definitely only want to record one bank
account per individual then index the IDIndividual column in BankAccounts
uniquely. Neither tblIndividuals nor tblBanks will have a foreign key
referencing the other of course as the relationship is via the BankAccounts
table.
When it comes to things like standing orders etc I can't be absolutely
categoric about how these should be handled on the basis of the information
you've so far provided. I'm Irish remember, and you are asking me which way
is the road to Dublin! Might there be multiple standing order per
individual? Do you need to record that they've been paid and on which dates?
The same sort of questions apply to other financial instruments. I'd need
to know in a lot more detail how such things fit in to the real world
business model in which the database will operate. From what you say,
however, I think you've probably got it right. Should you decide to record
more than one bank account per person, though, you'd need to add a column for
the account number to each of the tables which model the relationships
between standing orders and individuals. In fact the relationship is really
with BankAccounts rather than tblIndividuals as an individual can only set
up a standing order if they have an account to draw on. So if you do decide
to record more than one account per person the relationship would be on both
the IDIndividual and AccountNumber columns, rather than solely on the
IDIndividual column as at present.
Ken Sheridan
Stafford, England
