Table Design - stuck!

[Philip Herlihy]

I'm the treasurer of a Badminton Club. I want to create a database
which will record which members monthly subscriptions are due, who has
paid, and which will also keep track of what the club's bank account
balance should be.

I've ended up thinking I should have a single table of transactions.
However, some transactions are payments (a cheque from a member, or a
payment to our coach) and some are "charges", e.g. a batch of appended
records to indicate that the November monthly subscription is due for
each member.

A screenshot of my relationships diagram is available here:
http://bit.ly/2uvhxn
(the latest in a series of structures).

It can't be right! When I generate forms nothing quite works!

To explain the thinking behind it:

All Transactions are made with Contacts. Most of my Contacts are
Members (for whom I store additional details) but a few are not: the
Bank, our Coach, for example. So I thought I'd try and create a
one-to-one relationship between Contact and Member - if the Contact
isn't a Member then the foreign key in the Contacts table is null. I've
indexed the foreign key MemberID in the Contacts table so that
duplicates are not allowed but Nulls are ignored. (Yes, I do wonder if
an "optional" one-to-one relationship isn't a tautology!)

On the left-hand side of the diagram, I've tried to show that
Transactions can be a Payment or a Charge (respectively, an incoming
cheque, or a batch of appendsto make the November subscription payable).
There's a "Transaction Type" field in the Transactions table to
distinguish the two types.

You won't be surprised to know that I haven't tried working with this
type of relationship before (I can anticipate some mirth at my
attempts). It's probably crackers, but the attempt is prompted by a
history of programming in Object-oriented languages, and I was trying to
factor out the general (all these objects are Contacts) and the more
specific (some Contacts are Members). Similarly, a Transaction might be
a Payment or a Charge.

If this is possible, and I can get it right, I'd hope to be able to
process a cheque from a member into the system (tap details into a
single form) and use different queries to list what each member
currently owes, to predict what the club's bank balance should be, and
even to list the number and total value of cheques I haven't paid in yet
(so I can fill in the payment slip).

I'll be grateful for any advice, both in the specifics of what I'm
trying to do, and those principles which I clearly haven't grasped yet!

Phil, London

 

[Philip Herlihy]

Thanks, Ken - I'm very grateful for the advice of someone I'm certain I
can trust. I'm very glad to know that I'm not too far off - if I can
get this right I'll save myself hours of error-prone fiddling with a
spreadsheet by automating things!

I'd figured out the autonumber issue at the very end of a long session
where I was staring at the relationships diagram and "willing" the right
answer to be obvious. I think it's true that I've _always_ used an
autonumber in the past, and I've never needed to represent a
Type-SubType ("inheritance") relation before in this context. I see
that I have to copy the key value to the new Members record if I
"extend" a Contact with an additonal Member record - that should be easy
enough to automate with a button on a form.

I hadn't spotted that the foreign-key relationship was the wrong way
round, although I did wake with a start at 1:30am wondering just that!
I've adjusted the tables and relationships accordingly: see:
http://bit.ly/2dVrzt

One thing I'm still not clear about - whether I should designate the
(three) Type-SubType relationships to be Outer Joins (as I have done, as
things stand). I figure that the more you can tell Access about the
relationship at this level the more Access will help you, and this seems
a way of asserting that the lack of an associated Member record should
not prevent a Contact from appearing in the output of a query.

Maybe the answer isn't the same in each case: Not all Contacts need
have an associated Member record, but every Transaction should have
either a Charge or Payment record.

Much appreciated,
Phil

 

[Philip Herlihy]

Having some trouble with my form as well!

I've tried setting up a form/subform arrangement for Contact and Member
respectively, but as most Contacts will be Members I'd like the data
entry to be as seamless as possible, and I'd need to set up some way of
tabbing directly into the subform to do that.

So I also tried creating a query which joined the two tables. The query
is updateable, but the form based on it isn't (although I've now run out
of time to experiment further).

When I can come back to this, which is the better way to go?

Phil

 

[Philip Herlihy]

Thanks Ken!

Your explanation of the implications of setting the join type makes
perfect sense. Now obvious (but only obvious once explained!)

I think yesterday Access was having some problems on my machine as today
things do work as I'd expected. The form and subform tab sequence works
as you describe, and the "flat" form based on a query joining the
Contact and Member tables is indeed updateable. This works well, but
I'll explore the subform approach you recommend.

You mention two subforms. I can see this could be a solution where
there are two alternate subtypes (as in my Charges and Payments subtypes
of the Transaction entity) but I can't see why I'd need two subforms in
the Contacts form, as there is only one (optional) subtype: Member.
Were you thinking of the Transactions side of the diagram?
http://bit.ly/2dVrzt

I'm currently thinking that I'd use an event handler to copy the
ContactID into the MemberID as I tab into the first editable field of
the subform. On the Transactions side, I'd use event handlers to
hide/show the relevant subform depending on which Transaction type I chose.

Now that I'm reassured that my attempt to model an "inheritance" type of
relationship isn't inappropriate I'm pleased with this table design -
it's much simpler than the alternative design where Payments and Charges
were separate tables, and I can see immediately how to write simple
queries on which to base the forms and reports I need. It should repay
the time spent puzzling over it in day-to-day use, and it's very
rewarding to learn techniques which are certainly new to me.

I'm grateful for your help!

Phil

Phil:

Setting the join type in the relationships dialogue merely makes it the
default if you join the tables in a query in design view; it doesn't make any
difference to the relationship per se. I never bother with it myself as I
write most queries in SQL, and if I do use design view to get started I'll
just change it in the SQL if necessary.

As regards the subform don't you just need to put the members subform control
next in the tab order of the parent form's detail section after whatever is
the last control in the parent form in which a value would normally be
entered?

I don't know why your form should not be updatable if the query is, unless
the form's RecordsetType property is 'Snapshot'. In this sort of situation
I'd use a Contacts parent form with two subforms myself.

Ken Sheridan
Stafford, England

Having some trouble with my form as well!

I've tried setting up a form/subform arrangement for Contact and Member
respectively, but as most Contacts will be Members I'd like the data
entry to be as seamless as possible, and I'd need to set up some way of
tabbing directly into the subform to do that.

So I also tried creating a query which joined the two tables. The query
is updateable, but the form based on it isn't (although I've now run out
of time to experiment further).

When I can come back to this, which is the better way to go?

Phil

Thanks, Ken - I'm very grateful for the advice of someone I'm certain I
can trust. I'm very glad to know that I'm not too far off - if I can

[quoted text clipped - 109 lines]

Phil, London

 

[Philip Herlihy]

Yes, I see that - really neat. Access does make it easy for you once
you get the tables right.

Owe you a pint...

Phil

Phil:

Yes, I was thinking about the charges and payments when I mentioned the two
subforms, but when I re-read my post I can see that I'd obviously gone to
sleep and it came over as a complete non sequitur. For members your form
based on a query which joins contacts and members should be enough.

If you do use a subform, however, I don't think you'd need any code; just
setting the subform controls LinkMasterFields and LinkChildFields properties
would automatically insert the value into the subform's table's key once you
insert a row in the subform. This would only happen when you insert data
other than the key of course as the subform is not Dirty until then. The
same goes for the transactions subforms of course.

Ken Sheridan
Stafford, England

Thanks Ken!

Your explanation of the implications of setting the join type makes
perfect sense. Now obvious (but only obvious once explained!)

I think yesterday Access was having some problems on my machine as today
things do work as I'd expected. The form and subform tab sequence works
as you describe, and the "flat" form based on a query joining the
Contact and Member tables is indeed updateable. This works well, but
I'll explore the subform approach you recommend.

You mention two subforms. I can see this could be a solution where
there are two alternate subtypes (as in my Charges and Payments subtypes
of the Transaction entity) but I can't see why I'd need two subforms in
the Contacts form, as there is only one (optional) subtype: Member.
Were you thinking of the Transactions side of the diagram?
http://bit.ly/2dVrzt

I'm currently thinking that I'd use an event handler to copy the
ContactID into the MemberID as I tab into the first editable field of
the subform. On the Transactions side, I'd use event handlers to
hide/show the relevant subform depending on which Transaction type I chose.

Now that I'm reassured that my attempt to model an "inheritance" type of
relationship isn't inappropriate I'm pleased with this table design -
it's much simpler than the alternative design where Payments and Charges
were separate tables, and I can see immediately how to write simple
queries on which to base the forms and reports I need. It should repay
the time spent puzzling over it in day-to-day use, and it's very
rewarding to learn techniques which are certainly new to me.

I'm grateful for your help!

Phil

Phil:

[quoted text clipped - 35 lines]

[quoted text clipped - 109 lines]
Phil, London