Junction Table issue

[jd9913]

Hi,

I've been searching the forums (and the internet) and can't find an answer to an issue I seem to have.

I'm creating an incident management database. Each log entry created has a type (and occasionally a subtype). Some of the types have 3+ subtypes.


I have the following tables:
Type_ID (PK)
IncidentType
TypeDescription

JoinTypeSubtype
Join_Type_Subtype_ID (PK)
FK_Type
IncidentSubtype
Description

JoinLogType
JoinLogType_ID (PK)
FK_JoinTypeSubtype
FK_Log

Log
Log_ID (PK)

I've created queries based on these tables and then used the queries to build the forms.

My problem is: every time I enter a value into the form for Log, the data doesn't populate the JoinLogType table, it adds it to the JoinTypeSubtype table.

I cannot figure out how to structure the underlying queries to cause the JoinLogType table to be populated, but the JoinTypeSubtype table to be essentially a lookup table. I've tried different suggestions that I've found online, used the parent/child relationships in form/subforms and cannot get the data in the junction table JoinLogType to populate without adding to the JoinTypeSubtype table.

The way it should work is that for every Log_ID that is created, I want to associate a Type (and a subtype if relevant--although that can remain Null if necessary). I want to keep the JoinTypeSubtype table a list that is used to lookup values but I don't want it to be populated as it is now. I also will need to edit the JoinTypeSubtype table as subtypes are added and modified due to our changing needs so I can't lock that table to edits.

Any help would be appreciated. I've attached a screen shot of my relationships. The relevant tables are in the upper left of the picture.

Jennifer

 

[plog]

In general, forms shouldn't be based on queries, they should be based on tables; 1 form, 1 table. A form can be a subform in another table, but that subform should still be based on just 1 table.

My guess is because one of your forms is based on a query, you're data isn't going where you intend it to. You think its one field in one table, but your query is muddying the waters making it go elsewhere.

My suggestion is try to make each form be dependent on just one table. That will clear up any misunderstanding about where data is coming from/going to.

If you could post your actual database that would lend more insight.