One service# linked to choice of tables

[Cat Winslow]

Hi, pease bear with me, I don't do this often, but I'm trying...

I have a db to track home system inspections. There are about 100 things
that have to be checked on the InitialInspection form, and then routinely
afterward about 75 things that are checked on UpdateInspections.

I havea main form, frmCustomers, with a subform sfrmDatesAndServices. On
this subform I have a ServiceNumber (primary key), dates, service done, etc.
If this is the Initial inspection, I want to be able to have the
ServiceNumber link to my InitialInspection form (I have a button that opens
the form). If it is a subsequent inspection, I want the number to show up
when the user click the button for that form. Is this possible? If it is a
new house, the user will click the form and enter data for the initial
inspection, but might later want to go back and just view it (not create a
second 'initial inspection'). As time goes by, the user will add records and
that ServiceNumber will need to open a new form, but they may want to go back
and view the old one already in the system too.

I thought this was an easy setup, but when one thing stuck, it screwed up
some others, so I thought I'd ask for heeeeeelllllp Thanks in advance.

Cat

 

[strive4peace]

Hi Cat,

Don't start with the form, start with data structure
(tables). Your subject line suggests that you have multiple
tables with the same type of information; you should not do
this.

For structure ideas, read this post on setting up data for
faculty and classes.

first post:
news://msnews.microsoft.com:119/[email protected]

post with structure information:
news://msnews.microsoft.com:119/[email protected]

Hopefully, you can draw analogies to your data. Let us help
you get your data structures right before talking about the
forms.


Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Cat Winslow]

Hi, Sorry, I guess I need to give you a little more information.

Tables are:
CustomerInfo (persoanl stuff about current owner, CustID PK);
SystemInfo (the address, lot#, builder, installer, etc, 1-to-1 relationship
w Customers, CustomerID PK, );
DatesAndServices (ServiceNumber PK, CustomerID FK). The 1-to-1 seems odd,
but is accurate; they only need to know who lives there now, don't care who
it was before, and won't care later; really just a way of contacting someone.
Our basic entity is really the lot the system sits on, there can never be
more than one system there at a time, and only one customer to contact about
it.
InitialInspectionInfo (all the details of system setup);
Inspections (details for intermittent inspections of most types);
DripInspections (details of 2 specific types that are mostly different from
the other regular systems).

The problem is that there is so much detail to be tracked about each
individual system and it comes from 3 different places, 3 forms the
inspectors fill out for the system. One is the initial form, the actual setup
of the system (how large, manuf, etc) which does not change, goes into
InitialInspectionInfo. Then the intermittent inspections, which track the
details of how well the system is working for most systems, and a second one
like this that is for 2 specific types of systems. The customer info, basic
info and initial inspection info really could all be in one really huge
table. Should it be? I started out that way, but 200 fields seemed a bad idea.

So here I am with multiple tables that are all 1-to-1 relationships, bc
there are so many fields, all dealing with just one system. The only real
one-to-many here is the system has multiple DatesAndServices, when those
intermittent forms get filled out and put into the Inspections or
DripInspections.

Just in writing this, I am seeing things a little differently bc I am having
to explain it, and see some things I can/should? change, but I'll wait for
your input since you have baked this cake many times more than I

 

[strive4peace]

Hi Cat,

It would be much easier to help you if I could visualize
your structure. Can you email me a relationship diagram?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day