Thank you for reading! I want to share this with everyone for a programmers set of eyes that is not tearing like mine!....I have a database of contacts that was created from scratch in '03, and it's been converted to Access 2007.
Each contact has 2 related tables. Registration and Status.....one-to-many relationships are from the contact ID(1) to the registration contact name, and status contact name(many). I'm not quite sure how I accomplished this with different data types- all of my queries have to be forced joins to account name (ID numbers never match). These 3 tables/queries are working fine this way.
Data is collected through an add main form (contacts) with 2 subforms (registration and status). Edits or deletion of contact name are locked. Edits or deletion of a registration entry or status entry are locked. When a change is made to a registration or status, user selects the contact name and goes into another version of the add subform where they enter a new registration and status. The new becomes the current. Contact name is the same, with a new ID#.
When a new registration and status is entered, the tables automatically fill the contact name - so everything is relating fine. How Access does it boggles me...cannot seem to understand that.
Now I need to expand on Registration.......I want to be able to make notes and issues (2 different tables)against the current registration. I want to use the registration ID to relate to these notes and issues. And the note and issue needs to stay there if the registration for this contact changes.
I have tables that I cannot get to work.
1.) User level - chooses contact from a combo box list, that is max date registration (which is the current) opens a main form/subform. Subform is data entry to enter a new issue.
2.) table doesn't fill in the registration ID to relate to the issue
I thought I could get the subform to write the registration ID to the field with VBA but keep getting errors.
The form won't save because it can't find the related field.
Why is this different than my contacts table to registration and status realtionships?
How would you approach this table/query/form structure????
(The Contacts template has similair structure with a Calls table - relates on ID#'s.......but i can't figure out how to build mine since I am putting my "calls" on a many side of a pre-existing relationship.)
Hi Teddy - I've been away a couple of days......thanks so much for your response I hope to answer exactly what you need to analyze
The contacts are entered when submitted by a sales rep as a lead.
Registration is who(sales rep) is currently working the account, which begins as the rep that submitted it.
Registration can change to different sales reps (even multiple times) before an account (status) becomes active.
Registration is how we watch our sales leads.
It feeds different reports based on what sales rep has it and the amount of time a sales rep has had it.
Status is it's sales status (default is inactive)
Status is more stable, whereas when an account becomes active - it remains active for the most part under the sames sales rep(registration). On the other hand, some are worked for months, so they stay inactive along time.
Ok, can we get a relationship diagram of all the tables involved?
Without knowing how your data is physically stored short of having a few id's, it sounds like you want to create a notes table and relate it to registration in roughly the same manner as you have related contacts to registrations. That is, keep a history of what happened when, using the most recent as "current" for the given contact. Sounds about right?
We'll need some table structures to give more insight...
YES - this relationships diagram has the notes and requests tables that I cannot get to work on the right. tblSLMNOTES, tblRQT and tblRQTRESPONSE. Not all relationships are showing....I have worked this so much I have done and un-done couple of times.
Registration max date/most recent is current.
This is where the notes (tblSLMNOTES) and requests (tblRQT) will take place.
A note and a request can happen to registration ID 2 on a contact, and then the contact can change to registration ID 3.
I want the note and the request to relate to registration ID 2.
The tblRQTRESPONSE will relate to the tblRQT not registration...it's an approve or reject