You cannot add or change a related record in table (1 Viewer)

[jaryszek]

Hi,

this is not about business model data but technical thing - how to solve this.
But maybe i am not understanding a purpose to use junction table here.

I can have multiple pairs: Customers, Machines and Tasks.
In different combinations.

My model is in attachment.

For each customer-machine-task we can have certain documents.

I create Form to input data with comboboxes.
When i want to skip one of nullable FK - there is an error like in attachment.

What are yours ways to solve the problem?

Assume that for every Customer and his Task - we can have also machine to help doing the job (like computer, or laptop - whatever). But sometimes Customer will do his task without access to Machine - so this field should be empty (maybe employer will buy him machine later, or he will not get any machine at all).

Please help Guys,
Best,
Jacek

 

[Minty]

Your description doesn't match the data you then show in the forms.

On the form you have a customer number of 0 . This cannot be correct based on your description.

This again doesn't make sense as an example.
How would the documents be related to the Junction ID?

I understand you are trying to "see the model", but if you invent non-real world situations, your data model will also make no sense. Please try and make demonstration or sample data based on real world situations.

Customers, Orders, Products - make sense
Cars, Customers, Service records, Labour Bills, Parts used - makes sense
Cheese , Computers, Moons, Footballers that eat might cheese - makes no sense.

 

[jaryszek]

Hi Minty,

but do you see technical problem here? Or only model and data?

I am sure that a lot of people have problem like this.

How would the documents be related to the Junction ID?

JunctionID represents connection between Customer-Task-Machine.

This is simple. Customer has task to do and machine which will help him do it.
How to show it properly?

Imagine that you have office. You can have Machine1 there but not assigned to any customer. You can have also task which is not assigned to any customer. or in opposite way - combination of three of them. And each combination you have to document somehow. And create different documents. This make sense.

If you can - with pleasure i will see data model which will mirror the behaviour like here. With junction table and error: "you cannot add or change because a related record is required in table"

Question is, how to avoid this behaviour.

Best,
Jacek

 

[Minty]

As an additional point, my understanding is that a junction table is used where you join two related tables that are not dependent on each other.

The obvious one would be Classes and Students.

You may have classes with students but you might have classes with no current students.
You may have students that aren't currently doing classes.

Junction table would hold StudentID, ClassID, and probably enrollment date.

I think as soon as you add a third or forth table that isn't directly related to the junction, you will run into your problem.

So you have added another table to your junction, but it's not directly required, so should be in other junction tables if you could have many machines / tasks per customer or none.

 

[jaryszek]

Thank you,

so are you suggesting that in juntion table i should have only max 2 FK?
What if you have teacher also there. Private teacher.

So Students can not currently doing classes and have teacher assigned for them
Teachers can not have classes and students
Classes can not have teachers and students.

So 3 FK.

Problem is still the same.
How to avoid situation like here or create form to input data with nullable FK?

Best,
Jacek

 

[jaryszek]

And imagine that for each Teacher-Students-Class you have to write bunch of documents (school diary, presence list and others). So from junction ID you will have relation 1:M to documents...

Best,
Jacek

 

[Minty]

Our Posts crossed.

As I see it from your description, I don't understand how you would have task without a customer.

This is why your model doesn't work.

If you can have a task without a customer then your model is wrong and you should have a tasks table. That task must relate to something else directly, if it doesn't then your model is wrong again.

Do you see why the data model is more important than anything else, and must relate to the real world.

Don't invent a fictional scenario and then try and solve it, (unless you really have nothing better to do).

You have process / business model / task to accomplish, and make a solution based around the current process. You may in the event of modelling your data / process spot a better way to achieve the end result, and this may influence your data models design.

 

[jaryszek]

Minty,

last time when we talked about model i could not share data with you.
So we stucked. I have to show in other ways what i want to achieve.

You can have task without customer. The task can be assgined to one of your employees.

But for business you have to only track tasks, they dont care form where these tasks are.

Best,
Jacek

 

[Minty]

Well I would have said that in a simple model you have a teacher per class. Not many teachers, again this depends on the real life situation.

This is where the exact description and requirements become critically important.

If in the initial design brief you are told that you simply need a teacher per class you would design it that way.

If you are then told later that actually we might have different teachers doing that class on different days, you would need to redesign your tables to include a timetable and the relevant teacher / class / day information.

 

[jaryszek]

I am not understanding your point of view...

Hope that others may have any examples how they solve the issue.

Best,
Jacek

 

[mike60smart]

Hi Jacek

To carry out Data Input for your process you would need the table relationships to be as shown:-



You would then create a Main Form based on Customer and a Subform based on CustomerTasks as shown:-

 

[jdraw]

Jacek,

Please provide a clear, complete description of the business involved.
Something like " a day at the office" to put the pieces together in your context.
Responses will not be focused until readers understand your set up.

 

[jaryszek]

mike60smart thank you.

How did you create this? Can you show an example?
It is awesome.

--->

Ok we are going to business model.
It would be not easy.
And i can not share any data...

I am creating configuration tool.
Model was described here:

http://www.accessforums.net/showthread.php?t=73173&page=2&p=405454#post405454

I have topology and customer. Topology is a system like SAP HANA, NetWeaver. So system and customer it is a pair.

For this combination i can have distinction for some topologies - t_workload is for this.

And customer can choose server separately and topology for it (so which system will be for choosen server).
For server and stuff there is a junction table.

So each server can be on different environment (test, production, customer can choose what he wants). And we can have different Machine runned on server. And we have bunch of characteristic for each server (serverdetailedinfo). Each server have to have also set of cpuusable memory to choose (for Virtual machine will be differ than Bare Metal - different for different machines).

And i have new customer right now. I added topology for him.
All is good but i do not have all data now - not have MachineInfo and Environment (i will get it later but now i have to input part of data).
So problem with nullable FK appeared...

Maybe i am not seeing here all dependencies and my model sucks.

Please help,
Best,
Jacek

 

[mike60smart]

Hi Jacek

Here is the Db

View attachment Database7.1.accdb.zip

 

[Cronk]

At first I thought this was a car servicing situation, with the machines being another name to car. Now it appears as if it is one where there are computer servers and I can't for the life of me see any relevance to what car the customer has.


But let me have a guess. A customer owns a machine which is having a number of tasks done on it and there are a number of documents associated with the work.



If so, here are relationships

 

[jdraw]

Sounds reasonable Cronk. I'm not any farther ahead after requesting some detail and a "day at the office" in post #12.
For the life of me I can not understand what is so secretive about describing a business in generic terms. We are not asking for proprietary info nor some emerging technical secrets.

 

[Cronk]

Jack, I know what you mean. Sometimes though newbies get caught up in the detail and have trouble taking an overview, or don't understand what we are seeking, especially people with English as a second language.



Makes me think of the time when I was a grade 1 green newbie before the days of google searches, being asked for an entity relationship diagram. I knew the principles, not the term.

 

[Pat Hartman]

I think that Cronk probably offered a better model than what you were using but the actual message is caused because you have CustomerIDFK set to default to 0 and 0 is not a valid FK. If CustomerIDFK is not defined as required, then Null would be accepted but wouldn't make sense in your context.

FYI, Access has vacillated over the years with what it uses as the default for numeric columns. In some Access versions, the default is 0 but in others it is Null. Null is what professionals prefer. 0 causes fewer problems for non-professionals but that doesn't make it correct. If the column is to be used as a FK, you MUST change the default to Null. Personally, I prefer to use Null as the default for ALL optional columns. That simplifies my coding. If you leave 0 as the default for numeric, non-FK columns, you must understand that:
Avg ( 3, null, 3) = 2
and
Avg ( 3, 0, 3) = 2
0 has meaning. Null has no meaning. When the value has meaning, it is included in all arithmetic domain functions whereas nulls are ignored.

And finally, for a junction table, you would NEVER allow the foreign key columns to ever be optional. They would always be required. So, set the default to null and required to true and that will change the error message to something more meaningful such as some field is required but not present.

 

[jaryszek]

Hi Jacek

Here is the Db

Attachment 71586

thank you!

If so, here are relationships

love your answer. I think that you have right - i am self lerner and sometimes it is difficult to say whant i want in (as Minty says) in "plain English".

I see that you are avoiding very hard relationships and creating juntion tables for a lot of entities and FK.

Sounds reasonable Cronk. I'm not any farther ahead after requesting some detail and a "day at the office" in post #12.
For the life of me I can not understand what is so secretive about describing a business in generic terms. We are not asking for proprietary info nor some emerging technical secrets.

I wrote about my business model in post #13.

But i will create new topic for this.

but the actual message is caused because you have CustomerIDFK set to default to 0 and 0 is not a valid FK. If CustomerIDFK is not defined as required, then Null would be accepted but wouldn't make sense in your context.

Pat, you broke the bank.
thank you!

And finally, for a junction table, you would NEVER allow the foreign key columns to ever be optional. They would always be required. So, set the default to null and required to true and that will change the error message to something more meaningful such as some field is required but not present.

understand.
So when i have situation like here i should use 1:M relationships instead of?
so we always have customer but sometimes we can or can not have machine for him...?

Best,
Jacek

 

[jaryszek]

Hi steve,

thank you.
Tru business model here:

Link

Best,
Jacek