Beginner's DB Design Question

[Stephen]

Okay, using Access 2007, I have a beginner's DB design question...


Standard stuff - for a financial management DB, in the Register, I need a
way for Payees and Transactions to be memorized so that if you type in an
existing Payee, it autofills the data from the last transaction. But I also
need it to be possible to bring up a list of all memorized payees and remove
one so that it won't be autofilled in the future (unless a new transaction
is entered, in which case, they will be eligible to be autofilled after
that).

The question is what is the best way to design for this?


As far as the Payees are concerned, while the general rule of thumb is to
not store the same data more than once, if you create a table for Payees,
and use it to lookup the Payee field in the Transactions table, then you
don't have an easy way to type in a new Payee. I want the user to be able
to type in the payee, and have the Payee field autofill the best match as
they type - but if they end up typing a completely new payee, it should
accept it as soon as they move to a new field. My understanding is that a
lookup field can't be setup to have new records added this way.

And in any case, I figure that since we're looking to memorize the other
details of a transaction anyway, that perhaps it would be best for the Payee
field to be a regular Text field (in which case, there will be a lot of
duplicate info here - but maybe that's not a big deal) and to simply include
a Yes/No field in the Transactions table for "Memorized?" to indicate if it
is marked to be memorized. But whenever a transaction is entered for a
previously entered and memorized payee, the "Memorized" field will have to
be unchecked for the prior transaction, and checked for the active one so
that the latest transaction is what will be up to be autofilled in the
future.


Somehow, it seems like I'm not seeing the best way to do this. Can anyone
supply some feedback on how I'm approaching this, and whether or not there
is a better way to design for this?

 

[Klatuu]

You are correct in that you should not store repeating data. You should have
a Payee table that contains the Payee Name, an autonumber ID field to be used
as a foreign key, and since you want to be able to restrict using a Payee, a
Yes/No field that determines whether the Payee should show in the list.

You do not use the Payee name in the transaction table. You use the
autonumber ID field.

Now, once that is done, use two controls to handle this. One will be a text
box that is bound to the ID field in the transaction table. It should be
hidden so the user does not see it. The other would be a combo box with a
row source of a query based on the Payee table and filtered on the Yes/No
field to show only the Payees you want shown.

You will have to do some coding to keep the two controls in sync. You can
make the combo box show the Payee for new and existing records by setting the
value of the combo to the value of the Payee ID field using the Form Current
event. When yo make a change in the combo value, you can use the combo's
After Update event to populate the hidden text box.

 

[Stephen]

You are correct in that you should not store repeating data. You should
have
a Payee table that contains the Payee Name, an autonumber ID field to be
used
as a foreign key, and since you want to be able to restrict using a Payee,
a
Yes/No field that determines whether the Payee should show in the list.

You do not use the Payee name in the transaction table. You use the
autonumber ID field.

Now, once that is done, use two controls to handle this. One will be a
text
box that is bound to the ID field in the transaction table. It should be
hidden so the user does not see it. The other would be a combo box with a
row source of a query based on the Payee table and filtered on the Yes/No
field to show only the Payees you want shown.

You will have to do some coding to keep the two controls in sync. You can
make the combo box show the Payee for new and existing records by setting
the
value of the combo to the value of the Payee ID field using the Form
Current
event. When yo make a change in the combo value, you can use the combo's
After Update event to populate the hidden text box.

As someone without extensive experience in this, I am not quite
following. I don't fully understand how your proposal works, and how it
would achieve the UI features I specified in my original post that I was
looking for. Among other things, there is nothing that I see which clearly
shows how the details of the most recent transaction would be autofilled in
the rest of the fields, nor do I see how new payees being typed in would
automatically have a new record created for them in the Payees table.

Forgive my ignorance, please.

 

[KenSheridan via AccessMonster.com]

Firstly don't use the so called 'lookup wizard' for a columns data type when
designing a table. For reasons why not see:

http://www.mvps.org/access/lookupfields.htm

Store the payee's name and other details such as address data etc in a Payees
table which has a numeric PayeeID as its primary key (this can be an
autonumber), and only have a Payee foreign key column (which should not be an
autonumber, but a straightforward long integer number data type) in the
Transactions table. Names can be legitimately duplicated so are unsuitable
as keys.

In the transactions form use a combo box as the control for the PayeeID
column, setting it up along these lines:

ControlSource: PayeeID

RowSource: SELECT PayeeID, Payee FROM Payees ORDER BY Payee;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
LimitToList: Yes
AutoExpand: Yes

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

As a users enters a payee name into the control it will progressively move to
the nearest match. To insert a new row into the Payees table if a new payee
is entered put code in the control's NotInList event procedure. The
following is an example for adding a new city in this way:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

This opens a form' frmCities to enter the other data for the new city and
when this is close the new city s added to the combo box's list. The
following code goes in frmCities' Open event procedure:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

If there are no columns in Payees other than the PayeeID and Payee columns
you don't need to open the other form to add additional data , in which case
the code (again for adding a city in this example) would be:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub


To 'memorize' which payees should have their last transaction brought forward
to a new transaction include a Boolean (Yes/No) memorized column in the
Payees table; you can create a simple continuous view form bound to Payees to
edit this.

In the AfterUpdate event of the PayeeID control on the transactions form
include code like this:

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String

' get last transaction for payee
' include whatever columns you want carried
' forward in SELECT clause
strSQL = _
"SELECT TOP 1 TransactionDescription, Amount" & _
" FROM Transactions" & _
" WHERE PayeeID = " & Me.PayeeID
" AND Memorized = TRUE" & _
" ORDER BY TransactionDate DESC"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly

' assign values from last transaction to
' controls in form
With rst
Me.TransactionDescription = .Fields("TransactionDescription")
Me.Amount = .Fields("Amount")
' include more controls here as appropriate
.Close
End With

In the form's AfterInsert event procedure include code to update Payees table:


Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' update Memorized column in Payees
' to TRUE for current payee
strSQL = "UPDATE Payees " & _
"SET Memorized = TRUE " & _
"WHERE PayeeID = " & Me.PayeeID

cmd.CommandText = strSQL
cmd.Execute

Ken Sheridan
Stafford, England

 

[Stephen]

Firstly don't use the so called 'lookup wizard' for a columns data type
when
designing a table. For reasons why not see:

http://www.mvps.org/access/lookupfields.htm

Store the payee's name and other details such as address data etc in a
Payees
table which has a numeric PayeeID as its primary key (this can be an
autonumber), and only have a Payee foreign key column (which should not be
an
autonumber, but a straightforward long integer number data type) in the
Transactions table. Names can be legitimately duplicated so are
unsuitable
as keys.

Wow - thank you Ken. I really appreciate all the effort you went
through to help me with this. This issue seemed like a relatively simple
one - I didn't realize how much of this would rely on coding to make it
work.

I will do what I can to integrate your suggestions and see if I can make
this work.

Incidentally, I looked at the link you provided on the reasons why not
to use a lookup field. Some of them have merit, and I will definitely take
them into consideration in the future, but I was wondering if you could
elaborate on a couple of points:


1. Even if I didn't use lookup fields, I would still need to setup the
appropriate relationships, would I not? I would think so, but number 3 on
that list seems to suggest that this shouldn't be the case.

2. Do the suggestions you made require that there be no lookup fields in
place? Or would that not make a difference in implementing your
suggestions?




Stephen

 

[KenSheridan via AccessMonster.com]

1. Yes, enforced relationships are an integral part of the database
relational model and are crucial to protecting the integrity of the data, and
is something a developer would do as a mater of course when setting up a
database. The point being made on the MVPS site is that using the lookup
field wizard is that it creates superfluous relationships and indexes behind
the scenes. We are talking her about actual relationship objects, not
conceptual relationships between entity types.

2. No, you can still implement the design and the functionality as I
described it while having used the lookup field wizard. The database will
work and the day-to-day user will not see any difference provided the
developer has designed it in such a way that the user interfaces with the
data via customized forms and reports, and is not given access to tables
(including the result tables of queries) in raw datasheet view, but the
drawbacks explained on the MVPS site will be present.

Ken Sheridan
Stafford, England

Firstly don't use the so called 'lookup wizard' for a columns data type
when

[quoted text clipped - 11 lines]

unsuitable
as keys.

Wow - thank you Ken. I really appreciate all the effort you went
through to help me with this. This issue seemed like a relatively simple
one - I didn't realize how much of this would rely on coding to make it
work.

I will do what I can to integrate your suggestions and see if I can make
this work.

Incidentally, I looked at the link you provided on the reasons why not
to use a lookup field. Some of them have merit, and I will definitely take
them into consideration in the future, but I was wondering if you could
elaborate on a couple of points:

1. Even if I didn't use lookup fields, I would still need to setup the
appropriate relationships, would I not? I would think so, but number 3 on
that list seems to suggest that this shouldn't be the case.

2. Do the suggestions you made require that there be no lookup fields in
place? Or would that not make a difference in implementing your
suggestions?

Stephen

 

[Stephen]

I understand. Thank you so very much for your help and for your
extensive and clear answers!



Stephen

1. Yes, enforced relationships are an integral part of the database
relational model and are crucial to protecting the integrity of the data,
and
is something a developer would do as a mater of course when setting up a
database. The point being made on the MVPS site is that using the lookup
field wizard is that it creates superfluous relationships and indexes
behind
the scenes. We are talking her about actual relationship objects, not
conceptual relationships between entity types.

2. No, you can still implement the design and the functionality as I
described it while having used the lookup field wizard. The database will
work and the day-to-day user will not see any difference provided the
developer has designed it in such a way that the user interfaces with the
data via customized forms and reports, and is not given access to tables
(including the result tables of queries) in raw datasheet view, but the
drawbacks explained on the MVPS site will be present.

Ken Sheridan
Stafford, England

Firstly don't use the so called 'lookup wizard' for a columns data type
when

[quoted text clipped - 11 lines]

unsuitable
as keys.

Wow - thank you Ken. I really appreciate all the effort you went
through to help me with this. This issue seemed like a relatively simple
one - I didn't realize how much of this would rely on coding to make it
work.

I will do what I can to integrate your suggestions and see if I can
make
this work.

Incidentally, I looked at the link you provided on the reasons why not
to use a lookup field. Some of them have merit, and I will definitely
take
them into consideration in the future, but I was wondering if you could
elaborate on a couple of points:

1. Even if I didn't use lookup fields, I would still need to setup the
appropriate relationships, would I not? I would think so, but number 3 on
that list seems to suggest that this shouldn't be the case.

2. Do the suggestions you made require that there be no lookup fields in
place? Or would that not make a difference in implementing your
suggestions?

Stephen

 

[Klatuu]

I guess I didn't fully answer your question.
As to bringing in info from the previous transaction for a payee, this is
not an elementry task. It involves doing a lookup on the payee and returning
the payee's previous transaction and populating textboxes with the data you
want to replicate.

The first task is to define how you know which was the last task for the
payee. Once you have determined that, then you need a query that will return
the desired record into a recordset. Once the user selects the payee,
probably in the After Update event of the combo where you select the payee,
you would find the payee's record, then populate the controls from the
query's recordset.

This does require some VBA skills and the ability to write a query. If you
want to try to work through this, let me know.