UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Conditional Formatting Based On Two Separate Tables, Access 2016    
 
   
MrFormula
post Apr 22 2020, 05:36 PM
Post#1



Posts: 732
Joined: 28-May 05



I have a form ScheduledTeams where I would like to change the back ground color of ArriveDate based on date. I need to check if my emails have been sent out.
I have the list of emails that need to be sent on the table EmailMain

EmailMain
EmailMainID
EmailSubject
AdvanceDays

And my EmailLog table

EmailLogID
SentDate
TeamDetailsID
EmailMainID

So if the arrive date is less than advanceDays and the email log does not have that email listed this is when I need to change the background color.

TeamDetailsID is the common item between TeamPerDiem and EmailLog EmailMainID is the common field for the Email side.

I have used Conditional formatting before but it was always connected to the form I was working on... This seems to be much harder.

Thanks in advance for your help.
Go to the top of the page
 
theDBguy
post Apr 22 2020, 09:00 PM
Post#2


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Which table has the "arrive date?"

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MrFormula
post Apr 23 2020, 08:48 AM
Post#3



Posts: 732
Joined: 28-May 05



TeamDetails table has the ArriveDate field

Go to the top of the page
 
MrFormula
post Apr 25 2020, 06:51 AM
Post#4



Posts: 732
Joined: 28-May 05



I have attached the Relationship layout showing how the tables are connected.

Is this layout too complicated for the conditional formatting?
Attached File(s)
Attached File  EmailConditional.png ( 74.92K )Number of downloads: 0
 
Go to the top of the page
 
tina t
post Apr 25 2020, 10:39 AM
Post#5



Posts: 6,689
Joined: 11-November 10
From: SoCal, USA


i see that you don't have Referential Integrity enforced on any of your table relationships. why is that?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MrFormula
post Apr 25 2020, 03:09 PM
Post#6



Posts: 732
Joined: 28-May 05



I guess I don't have a reason.
Looks like it need to check into that.

Do you have any suggestions for the conditional formatting?
Go to the top of the page
 
MrFormula
post Apr 25 2020, 05:03 PM
Post#7



Posts: 732
Joined: 28-May 05



Maybe the conditional formatting won't work.. I might just need to have a continuous form with the list of reminders. I am starting to play with a query now.
I also don't believe I need to use EmailBody table in this query.

Here is the start of the query.

So how do I qualify the data properly.
I have the list of emails that need to be sent and the number of days in advance that they need to be sent on the EmailMain table.
There is an email that needs to be sent 180, 170, 90 and 30 days in advance.
If I cross the 180 days, I just want to see that one listed... I don't want to see the "Future Emails" ... If I forget to send the 180 day email and I cross the 170 day.. I want to see them both listed.

How do I write that criteria?

CODE
SELECT TeamDetails.TeamDetailsID, EmailMain.EmailMainID, EmailMain.AdvanceDays, TeamDetails.ArriveDate
FROM TeamDetails INNER JOIN (EmailMain INNER JOIN EmailLog ON EmailMain.EmailMainID = EmailLog.EmailMainID) ON TeamDetails.TeamDetailsID = EmailLog.TeamDetailsID;
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 05:38 AM