One to Many relationship

[BSG75]

I have a database Ms2000 and i have several users that are using the databse over a network. The form is pulling from a Query. In one table(Loan Center) i have a primary key on Loan Center Number and i joined it with another table(Frozen Screen) that has the same field. I inputed a set of loan centers into the table called Loan center. Then i pulled additional information from the Frozen Screen table. My users enter in Loan Centers that are not in the Loan center table and they get a message that ms could not find a matching key field. How do i allow them to enter in the loan center and other information in this type of join?

 

[Golom]

They get the message from where? Is Access raising an error (in which case there's some problem with your SQL,) or is the "cannot find matching key field" something that the code is displaying?

What "type of join"? INNER? LEFT, RIGHT?, CROSS?, UNION?

 

[BSG75]

The error message is generated from from MS, and it is a Left outer join. The error is The Microsoft Jet Database engine cannot find a record in the table Loan Center with a key matching Field Loan center number. I have tried to change the join to a right join but i then get a message that the primary key can't contain a null value. Is there anyway to add a record to the Loan center table? If not then is there a way to add a record to the Frozen screen table that does not cause an issue with the iformation in the loan center table.

 

[BSG75]

Here is the SQL

SELECT [Screen frozen].Number, [Screen frozen].Date, IIf(([Loan Center Contacted])=-1,"Loan Center Has Already Been Surveyed "," ") AS Alert, [Screen frozen].Interviewer, [Screen frozen].[Loan Center Number], [Loan Center].[Loan Center Name], [Loan Center].State, [Loan Center].[Loan Center Contacted], [Screen frozen].[Interviewee Name], [Screen frozen].[Interviewee Extension], [Screen frozen].ALPS, [Screen frozen].Imaging, [Screen frozen].[Mircrosoft Outlook], [Screen frozen].[Other 1], [Screen frozen].[Mainframe APPS OPEN], [Screen frozen].[ALPS 2 APPS OPEN], [Screen frozen].[Web Query APPS OPEN], [Screen frozen].[Task Manager ACTIONS TAKEN], [Screen frozen].[Reboot Computer ACTIONS TAKEN], [Screen frozen].[Close Out Application ACTIONS TAKEN], [Screen frozen].[Other 2], [Screen frozen].[Registering a Loan], [Screen frozen].[Pricing a Loan], [Screen frozen].[Moving between screens], [Screen frozen].[Viewing docs in Query], [Screen frozen].[Other 3], [Screen frozen].[How often does this happen], [Screen frozen].[Word APPS OPEN], [Screen frozen].[Excell Apps open], [Screen frozen].[Other 4 apps open], [Screen frozen].[Remedy Ticket], [Screen frozen].[HLE Imaging], [Screen frozen].[HLE Imaging Apps open], [Screen frozen].[Apps Freez], [Screen frozen].[Apps freez no]
FROM [Loan Center] LEFT JOIN [Screen frozen] ON [Loan Center].[Loan Center Number] = [Screen frozen].[Loan Center Number]
ORDER BY [Screen frozen].Number;

 

[Golom]

OK. That message usually means that you are attempting to add new records to the query but the underlying tables either require a value in fields that are not in the Query OR that some of the fields that are in the query (such as the computed "Alert" field) do not exist in the base tables.

You will probably need to provide seperate functionality to add new records to [Loan Center] or [Screen Frozen] because this recordset is likely not updatable.

 

[dhookom]

Does this SQL work"

SELECT [Screen frozen].Number, [Screen frozen].Date, 
    IIf(([Loan Center Contacted])=-1,"Loan Center  Has Already Been Surveyed "," ") AS Alert, 
    [Screen frozen].Interviewer, [Loan Center].[Loan Center Number], 
    [Loan Center].[Loan Center Name], [Loan Center].State, 
    [Loan Center].[Loan Center Contacted], [Screen frozen].[Interviewee Name], 
    [Screen frozen].[Interviewee Extension], [Screen frozen].ALPS, 
    [Screen frozen].Imaging, [Screen frozen].[Mircrosoft Outlook], [Screen frozen].[Other 1], 
    [Screen frozen].[Mainframe APPS OPEN], [Screen frozen].[ALPS 2 APPS OPEN], 
    [Screen frozen].[Web Query APPS OPEN], [Screen frozen].[Task Manager ACTIONS TAKEN], 
    [Screen frozen].[Reboot Computer ACTIONS TAKEN], 
    [Screen frozen].[Close Out Application ACTIONS TAKEN], [Screen frozen].[Other 2], 
    [Screen frozen].[Registering a Loan], [Screen frozen].[Pricing a Loan], 
    [Screen frozen].[Moving between screens], [Screen frozen].[Viewing docs in Query], 
    [Screen frozen].[Other 3], [Screen frozen].[How often does this happen], 
    [Screen frozen].[Word APPS OPEN], [Screen frozen].[Excell Apps open], 
    [Screen frozen].[Other 4 apps open], [Screen frozen].[Remedy Ticket], 
    [Screen frozen].[HLE Imaging], [Screen frozen].[HLE Imaging Apps open], 
    [Screen frozen].[Apps Freez], [Screen frozen].[Apps freez no]
FROM [Loan Center] LEFT JOIN [Screen frozen] ON 
    [Loan Center].[Loan Center Number] = [Screen frozen].[Loan Center Number]
ORDER BY [Screen frozen].Number;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]