T
tjr
Mr. Viescas does great job in Inside Out Access 2003 but I am still undecided
how is best to set up all data necessary and establish relationships between
tables. I have the following business model to follow. I have clients who
purchase an item which is financed by one of many lending institutions with
many possible contacts and various payment schedules. I need to track the
bank and contact info in conjunction with a particular payment schedule. I
also need to track phases in completion of the finished product being
purchased by individual clients to know when I can move to the next payment
request according to the given payment schedule. This requires tracking not
only the phase of the project completion but also tracking the adherence to
the lending institutions payment schedule requirements. I would also like to
track contacts that I have with the bank contact regarding each account and
the particular payment request phase that the project is in. I have the
following tables:
HomeOwnerInformation:HOAcctNo(pk), HOLastName, HOFirstName, JobSiteStreet,
JobSiteCity, JobSiteZip (plus fields for residence info and other identifying
info); BankInfo:HOAcctNo, BankID(pk) (plus other fields identifying info
about bank); DrawInfo
rawID(pk), HOAcctNo, (then individual fields for dates
requested and received along with fields for docs requested and Y/N fields
for docs submitted for each draw, there are a maximum of 7 payment (draw)
requests. Also Notes-memo field. (this is large table -due to poor design?);
DrawCompletionChecklist: ID (pk), HOAcctNo, stepdescription fields with
corresponding y/n completed fields for each step that is required throughout
the course of the particular payment request or draw phase. Remember each
bank has different draw schedules each with different requirements during
each phase and some payment schedules are even % to completion...with far
more steps or fields than some of the phases schedules. DONT KNOW BEST WAY TO
DEAL W THIS;
InspectionsMilestoneDates:ID (pk) HOAcctNo, fields list various required
inspections, dates, Y/N (passed);
Q: Should I create another table that tracks lender required inspections
which are seperate from previous inspections?
Q: How do I create linking tables when I have to manage: AccountNumber,
Bank, DrawInfo, DrawCompletionChecklist, InspectionsMilestoneDates, possible
LenderInspections, and desired additional table to track the contacts that I
have with the contacts from the bank and the contacts that I have with the
account holder.
Q: How are the relationships established when 1 account holder deals with
one lender who can have multiple contacts and multiple draw (payment
schedules) that I must adhere to and each has different phases and
requirements that I must track along with contact information and follow up
dates both with the lender and specific internal people who are responsible
for tracking the actual progress of each project?
Please help, thank you.
how is best to set up all data necessary and establish relationships between
tables. I have the following business model to follow. I have clients who
purchase an item which is financed by one of many lending institutions with
many possible contacts and various payment schedules. I need to track the
bank and contact info in conjunction with a particular payment schedule. I
also need to track phases in completion of the finished product being
purchased by individual clients to know when I can move to the next payment
request according to the given payment schedule. This requires tracking not
only the phase of the project completion but also tracking the adherence to
the lending institutions payment schedule requirements. I would also like to
track contacts that I have with the bank contact regarding each account and
the particular payment request phase that the project is in. I have the
following tables:
HomeOwnerInformation:HOAcctNo(pk), HOLastName, HOFirstName, JobSiteStreet,
JobSiteCity, JobSiteZip (plus fields for residence info and other identifying
info); BankInfo:HOAcctNo, BankID(pk) (plus other fields identifying info
about bank); DrawInfo
rawID(pk), HOAcctNo, (then individual fields for datesrequested and received along with fields for docs requested and Y/N fields
for docs submitted for each draw, there are a maximum of 7 payment (draw)
requests. Also Notes-memo field. (this is large table -due to poor design?);
DrawCompletionChecklist: ID (pk), HOAcctNo, stepdescription fields with
corresponding y/n completed fields for each step that is required throughout
the course of the particular payment request or draw phase. Remember each
bank has different draw schedules each with different requirements during
each phase and some payment schedules are even % to completion...with far
more steps or fields than some of the phases schedules. DONT KNOW BEST WAY TO
DEAL W THIS;
InspectionsMilestoneDates:ID (pk) HOAcctNo, fields list various required
inspections, dates, Y/N (passed);
Q: Should I create another table that tracks lender required inspections
which are seperate from previous inspections?
Q: How do I create linking tables when I have to manage: AccountNumber,
Bank, DrawInfo, DrawCompletionChecklist, InspectionsMilestoneDates, possible
LenderInspections, and desired additional table to track the contacts that I
have with the contacts from the bank and the contacts that I have with the
account holder.
Q: How are the relationships established when 1 account holder deals with
one lender who can have multiple contacts and multiple draw (payment
schedules) that I must adhere to and each has different phases and
requirements that I must track along with contact information and follow up
dates both with the lender and specific internal people who are responsible
for tracking the actual progress of each project?
Please help, thank you.