Jump to content

Relationship that shows records NOT in 2 other tables?


R2D2
 Share

This topic is 2838 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi!

 

I have a problem... again... :ermm:

 

There are 4 tables: Calendar (CAL), DutyWish (DW), DutyNotWanted (DNW) and personnel (PER). If a person wishes to work on a certain day, the record is added to DW-table. And same if he definitely don't want to work to DNW.

 

I wanted to show ALL those names in CAL-table that have NO wishes either in DW or DNW -tables (i.e. no records in either tables). But how can this be done with relationships? The CAL-table have dates and they can connect to dates in DW and DNW -tables. PER -table have names.

 

Thanks in advance!

Link to comment
Share on other sites

Hi!

 

Thanks for reply! I'm afraid also that my model will not work. Maybe I'll just combine those two tables (DW and DNW) to one and add one field to distinguish wheter it is DW or DNW wish. I wonder why I did 2 tables at all... Maybe there was some idea then.

 

But thanks!

Link to comment
Share on other sites

I wanted to show ALL those names in CAL-table that have NO wishes either in DW or DNW -tables (i.e. no records in either tables). 

 

While Wim's suggestion for consolidating your two tables is very sound, I can't see how this solves your issue.

 

Assuming you have consolidated file Schedule as join table between Staff and Calendar, i.e.

 

Calendar --< Schedule >-- Staff

 

and that you are using IDs as relationship keys, rather than names, try creating a calculation field in Calendar as

Case ( 
  IsEmpty ( Schedule::id_staff ) ; 
  0 ; 
  List ( Schedule::id_staff ) 
)

and create a new relationship with

 

Calendar::cListOfScheduledStaff ≠ Staff_notScheduled::id

 

Now a portal into Staff_notScheduled would show you exactly these staff members.

Link to comment
Share on other sites

Thank you, Eos!

 

I tried your solution and it worked - once. I made a test bed for this and in there it did work. But when I tried to implement it to original file it doesn't. I think that I try to make relationships among wrong files.

So, to be sure, could you explain the correlations with names. I assume:

CAL --- Calendar

DutyWish (DW+DNW) --- Schedule

Personnel --- Staff

 

What is "Staff_notScheduled" you mentioned in that relationship?

Link to comment
Share on other sites

And here is my present relationship model. There is (for testing) both correlation and non-correlation relationships. Neither of these work. (I hate to be so stupid... :logik: )

 

Screenshot%202015-02-24%2013.37.53.jpg

Link to comment
Share on other sites

what is the difference in Schedule between id and id-staff -fields?

 

One is the table's primary key (every table holding business data should have one), the other one is a foreign key – which you can tell by the fact that one field is defined as an auto-enter serial, and the other one isn't.

Link to comment
Share on other sites

This topic is 2838 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.