Table structure vs forms stack

[Rolls]

frmMain is set up to enter data contained on a supplier's invoice to a
purchaser. The contact information on one invoice consists of 1) a company
name, 2) a physical address with several lines, and 3) an email address for
correspondence. Centralized sellers may have several affiliates located at
a common physical address. There can be several different email addresses
associated with the supplier at one physical address. Decentralized
suppliers typically have several addresses all using the same company name
and a different email address at each physical location. There can be
combinations of supplier + location + email for each invoice, but there can
be only one email address per supplier + location pair. As each invoice is
being entered there is other information in addition to the contact
information.

Q: frmMain is bound to tblInvoice. Each of the three contact items should
be a lookup. The supplier is first. Second the supplier's address should
be a lookup that is limited to the selected supplier. Once the pair is
determined, the email address should also be a lookup For each step if a
choice does not exist it should be capable of being added. It if does exist
it should be able to be edited.

What is the best way to setup tables/forms to accomplish this? Can they be
bound forms or is the situation too complex? It seems that what I want is
to be able to build a resolver table containing the supplierID + locationID
+ emailID pair PK and then insert the key into one FK field in tblInvoice.
Is there a better way?

 

[Klatuu]

Here is a good candidate for cascading combos. That is, you would look up
the company name in an unbound combo. Then the combo for the address should
be filtered on the company name combo so it only returns addresses for the
selected company. And the email combo would filter on the address combo. As
each combo is selected, use a FindFirst to locate the selected record.

Also, I am curious about your design. You mention one table tblInvoice. In
most Invoicing, Billing, Sales Order, and Purchase Order applications, you
use two tables.
tblInvHeader and tblInvDetail. The Header table contains all information
about the Invoice ( Invoice Number, customer, address, date, etc. The Detail
table is related to the Header table by the Invoice Number. It contains line
item info (Item Code, Item Description, Qty, UM, Unit Price, Discount, etc)

 

[Rolls]

further information -

BACKGROUND
tblInvoice began life as one table containing all info about each invoice
that was received with defective information that did not meet submission
requirements (10 required pieces of information). These incoming invoices
from suppliers were prepared by them for payment by us, the buyer. When a
required item is defective the invoice is rejected, it has to be corrected
and then resubmitted to us. This database records the problems noted on
each defective/rejected invoice (can include 1 up to all 10 problems) and a
memo field for miscellaneous comments. One click creates a paper cover
letter (rptCoverLetter) with check-box reasons for rejection plus the
optional comment memo field if it contains text. The actual line item
detail itself from the invoice is not logged. Only the items needed to
identify the invoice being returned are recorded. These include invoice
date, invoice number, invoice gross amount, vendor name, physical address.
Other fields are generated by us from lookup fields such as business
segment, name of person rejecting the invoice, etc. for our internal use.
Information recorded has been used for trending to see which suppliers were
creating the most problems by not following instructions (which help get
them paid faster if followed correctly) and generating crosstabs containing
supplier/rejection reason/counts over time to see a hoped for decline in
volume of corrections needed and a trend of reductions in the mix of reasons
for rejection (error mix).

It was not obvious at first that we had relationships in the data such as
supplier name and correspondence address. After 11,000 invoices had been
logged we analyzed the data and found that more than half the errors were
produced by large suppliers having either centralized (several
affiliate/vendor names with a shared physical correspondence address OR were
decentralized with one parent and multiple physical locations. So there is
a many-to-many relationship between suppliers & their correspondence
address. tblVendor and tblAddress exist separately with their PK linked to
FK in each tblInvoice record. At this point we decided to add an
enhancement that would create an e-mail notification option in addition to a
USPS notification. We have a potential for 1 email address TO: text field
(it might contain two addressees) linked to the Supplier + Vendor fields.
After this upgrade, current business will have the email address entered
currently. Other email addresses will have to be added so that the next
time a supplier + physical address pair comes up we will not have to search
for the email address. Of course the data should be normalized so that if a
contact changes the history will reflect the current email address. We
won't be tracking changed email address information applicable to historical
time periods.

In order to make the design better and accomplish the seamless data entry
edit and preserve the relationships as discussed, I believe that I need to
add a resolver table:

tblVendor-Address
Vendor-AddressID (autonumber)
VendorID PK
AddressID PK
EmailID PK

and add

tblEmail
EmailID (autonumber)
EmailAddress (text)

to the existing tblVendor and tblAddress

which means I will need to relink history via the resolver table.

My problem is forms design. tblMain is the form used to enter invoice
information. So I have to (as you said) lookup a vendor (add or edit),
lookup an address (add or edit) then given this pair, lookup an email
address (add or edit). Up to three of the underlying tables could be edited
or appended to. Then their ID's need to be appended to a unique record in
the resolver table if applicable (I.E. if for the current invoice there is a
new vendor or a new physical address combination plus the associated email
address that did not already exist). If so this resolver table record needs
to be saved in order that the PK generated by the autonumber field can then
be added on frmMain to tblInvoice.

I am getting in trouble using bound forms and wizards, am not in sequence
logically, and don't know if I am having a logic problem or I passed the
limitations of bound forms and need to use unbound forms instead and if so
how to use a DAO 3.6 recordset (Access 2000). I have used DAO recordset
for not-in-list programming of combo boxes in other databases, not this one.
If I need to use recordsets/DAO to maintain the 5 tables seamlessly
(tblVendor, tblAddress, tblEmail, tblVendor-Address, tblInvoice) this is
were I am getting confused. Appreciate the help.

Thanks