Normalization Help -- Lookup table creating new records

[iuianj07]

Hello guys,

First of all, thanks for taking the time to read my post, and I want to let you know that this is my first attempt to create a properly normalized database... I have been creating Access databases before but tables are not properly normalized...

I am trying to overhaul an existing database that we have... and before the tables are kind of more like an excel spreadsheet than a relational database table... Now, I think I have normalized it (hopefully correctly) and now on the part of creating forms...

What this database does is, it tracks Job requests that we get from our client. What I am currently trying to do now, is creating a New Transaction Form, wherein we enter data whenever a new Job request has been received from the client... Our Client is a bank and what we do is we index loans for them and sometimes there will be loans that also needs to have an ASR created for them...

My table structure kinda looks like:

Job_Tracking table
Situs_ID -- PK
Project
Facility_Number
etc

ASR_Loan_Status
ASRStatusID -- PK
SitusID -- FK to Job_Tracking
Analyst
ASRStChID -- FK to ASR_Status_Change
StatusDate
StatusComment

ASR_Status_Change
ASRStChID -- PK
Status


On the New Transaction Form -- This form is bound to this query:

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Property_Loan_Name, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files, ASR_Status_Change.Status
FROM Job_Tracking LEFT JOIN (ASR_Status_Change RIGHT JOIN ASR_Loan_Status ON ASR_Status_Change.ASRStChID = ASR_Loan_Status.ASRStChID) ON Job_Tracking.Situs_ID = ASR_Loan_Status.Situs_ID;

I enter the information needed ( Project Name, Facility Number, Requested By, Client Sponsor, Property Loan Name, etc...) and one of them is ASR? field which is a combo box field and has a default value of 1 (No Data Required).. The problem though is that whenever I create a new record/transaction from the form... a new record also is being created on ASR_Status_Change table which shouldn't be since this is just my lookup table...

Please guide me into the right direction and let me know what I did wrong...

below is a link of my database (sanitized it as much as I can) so that you could see the relationship diagram and let me know if there is something wrong with it...

http://www.mediafire.com/?f4vsdbt3wd45scp

Please let me know if you have any questions,

Thank you for your time in helping me out

 

[MajP]

the control source for the combo needs to be the foreign key ASRStChID

The foreign key ASRStChID needs to be included as a field in the forms recordset.

 

[iuianj07]

Hello MajP,

Thanks for your response... I will try this and let you know

Did you download the database? I was wondering if you have looked at it and let me know if I have normalized it properly and made right relationships...

Thank you very much