New records created in one table, but not another

[Nick Mirro]

I have three tables.

primary key type

tblVisits visitID autonumber
tblBillingInfo visitID foreign key
tblPayments visitID foreign key


tblPayments and tblBillingInfo are 1:1 with tblVisits. When a new visit
record for tblVisits is created, I then open a form which uses
tblBillingInfo, and after adding data, a new record is automatically
generated in tblBillingInfo.

Both tblBillingInfo and tblPayments have referential integrity and cascade
update and delete. They are both joined to include all records from
tblVisits and only those within that match.

I cannot get a new record to be created in tblPayments when I open a form
that uses it. I get an error that says there must be a matching record in
tblVisits. When I look at the tables, there is one in tblVisits, a new one
in tblBillingInfo and none in tblPayments.


Nick

 

[John Vinson]

tblPayments and tblBillingInfo are 1:1 with tblVisits.

One to one relationships are QUITE rare, and (though I don't know your
business model) probably incorrect in this case. Are you saying that a
visit will generate either zero or one, but never more, bills? and
that you will accept zero or one, but never more, payments?

 

[Nick Mirro]

Well its based on the patient visit, and only one bill can be sent per
visit. For non-pays, we send the same bill again as a request for
reconsideration. Billing omissions are resubmitted as corrections.

Multiple payments are possible, but as yet, not more than 3 per bill. I
track up to six payments per visit in a non-normalized fashion on the same
form as full history to be visible in one presentation, with all detail.

Our treatment and billing software has quite a bit of 1:1 since much is tied
to the patient visit, but needs to be broken up into manageable chunks.


(here are the relationships)

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.pdf

Nick