Conditional formatting

[Indi123]

Hi All I have two tables access DB

Table one – Patients’ bio data

Table two – Referral Data

Referral table collects data such as – date of referral, time Reason/s for referral etc

I created One to many relationship – as same pt can get referred many times over months and years.

Based on this, I created a Registration with Referral details form as the Sub form.

My Question

For example If Mr Smith get referred to the clinic more than once, within a 4 months period (for example two referrals within 4 months either for same reason or different reason) then I want Mr Smith’s font colour to change to Red from normal Black.

What matters is count of referrals – if < 2 then to change the font colour

For example Mr Smith

Referral date
01/06/14
05/07/14

So Mr Smith got two referrals in less than 4 months

Referral date: Is a filed in the referral Table

In the Referral sub form I created a field under the same name – Referral date (at present this not a Bound control filed )

Please advise

Thanks

 

[vbaInet]

For example If Mr Smith get referred to the clinic more than once, within a 4 months period (for example two referrals within 4 months either for same reason or different reason) then I want Mr Smith’s font colour to change to Red from normal Black.

What matters is count of referrals – if < 2 then to change the font colour

Two conflicting statements highlighted in red. I suppose you mean the former, i.e. if > 1 OR if >= 2 AND referral BETWEEN 4 months.

Now I don't know if you're looking for referrals between some start and end date or if you're looking for referrals between a start date and today's date but you can adopt the below to suit:

1. Create a Totals query that includes only the Client ID and the date field(s). 2. Set criteria under the date field(s) to find records within the 4 months. You can use the DateDiff() function:

http://www.techonthenet.com/access/functions/date/datediff.php

3. Under the Counted valued, add criteria to filter it down by more than 1, i.e. >1.
4. Save this query and join it to your report's Record Source (which I'm hoping is based on a query) making sure your join "includes ALL records from the report's query and only those records from your new Totals query where the joined fields are equal".
5. Include the counted field in your query and in the Conditional Format criteria it would look like this:

[COLOR="Blue"][I]Field Value Is[/I][/COLOR]
[CountOfReferrals] Not Is Null

 

[Indi123]

Thanks so much for the rapid response.
pls Be kind to me when you advise - as I have no previous knowledge of Access or Dbs
I think, reflecting on my post I haven’t made things clear for what I need.
I have two tables (1) Patient info (2) Referral Info
One patient can have many visits to the hospital – so the table relationship says – One To Many.
What I need is:
In referral table there is a field called – Referral date
Say I am accessing Mr Smith’s Record – I want Mr Smith’s name to change from Black to Red if he has had more than 2 referrals during last 4 months (from current date)
I also want to do a report with following information:
First Name Surname Total Referrals (I want to put criteria to count how many referrals during last 4 months (from current date)
Thanks so much guys

 

[vbaInet]

Everything you've just re-iterated is what I covered in my last post. It is listed from top to bottom so if there's a step you don't understand just ask.

For step 1, here are some links on how to do a Totals query:

http://office.microsoft.com/en-gb/access-help/sum-data-by-using-a-query-HA010096310.aspx

http://www.fontstuff.com/access/acctut04.htm

 

[Indi123]

sorry to be a pain,

will you be able to kindly post the that Datediff criteria (how to write the criteria)

 

[Indi123]

I just managed to get it going - thanks for now, but I am sure I will need yours and others help later on

 

[vbaInet]

Good to hear!

 

[Indi123]

Hi VbaInet and Co

It looks like I jumped in the air too quick

Ok this is what happening .. I could not get the DateDiff to work.. (did I hear you said Idiot ...lol no worries)

However I used this expression >=DateAdd("m",-4,Date()) so this kind of did what I needed (not sure whether this is technically correct ...but it did the job

Now the query (sure I know this is form section - but as the end result is connecting to a form hence I am here) (Pt = Patient)

Pt ID............Referral Date..................Referral Date
01.................01/04/14..................... 1
01.................12/06/14......................2 (in this same pt had come twice
01.................01/07/14......................1
03..................01/05/14.....................1
03..................02/06/14.....................2

This kind of near to what I need ...but I really really want is (possibly a in a new column) to add 1 +2 + 1 and say...

Pt ID............Referral Date..................Referral Date........ New Column
01.................01/04/14..................... 1...........................4
01.................12/06/14......................2
01.................01/07/14......................1


That means Pt Id (01) say Mr Smith had referred to the clinic 4 times during last 4 months

Your advise please guys/girls

 

[vbaInet]

Like I mentioned before if there's a particular step you don't understand fully, just ask. If you had followed my steps to the letter you won't have encountered this "problem". Step 4 says you should JOIN your count query to your report's query via Pt ID, then include the count field there.

 

[Indi123]

hi VbaInet and others

Thanks so much for your reply.

As you can see clearly, I don't understand step4

The first time I knew, that there is something called "Access" in my Ms Office pack ...was the day I registered with this forum.

So guys, any simple step for you...is a VERY BIG step for me.

In my chosen field of work I have more than 30 yrs experience, when it comes to Access I hardly understand the difference between Query and a Report....however I am gaining some basic knowledge thanks to you guys and Google.

So, is it possible to do what I asked for within a query Or has it got to involve a query and a report as well...

If VbaInet reading this - can you kindly explain step 4 in... How to style

And for my general knowledge is it possible to calculate the total within the Query

Thanks guys

 

[Indi123]

Some positive update.
I managed to get what I needed within the query

Now My query says:

Patient ID.................Referrals within Last 3 months
01...................................... 05

04...................................... 02

12.......................................... 01

Then I created a unbound text box in the form

in the control source I put:
=DLookUp("[CountofRefdate]","last three months","[CountofRefDate]" Is Not Null)

When I go the form view... Text box works..However it shows 05 on the pt Id 01..... but when I go to Pt Id 04, Text box still says 05 (in other words the number in the text box does not change with the record)

Any advise ??

 

[vbaInet]

You're of course free to do it your way but I won't be able to advise you on why your way isn't working. I'm trying to get you to do it the proper way, so if you want to go with mine that's fine, I can guide you.

 

[Indi123]

Hi VbaInet - thanks for the reply and I am glad that you are still helping me

In the count/total query this is what I put under Referral date column

>=DateAdd("m",-6,Date())

For example, It counted How many times Mt Smith had visited the health clinic during last 6 months.

Two things - (1) If this formula is not the most appropriate, pls write down correct version

(2) By applying above -what the query did was it pulled the patients who visited ONCE during last six months as well, so what I need is people who came MORE than once only -so how do I modify or if I follow your method pls add that to it.

the biggest or almost equal help I need is

say the query results come as;

Pt ID ................................... Attendance
01............................................. 05
08..............................................02
100.............................................06

I want to get 05,02,06 into a textbox in the main form

So, for example - when I come to Pt ID 01 Record (sayMr Smith) the text box should display 05, When I come to PT 100 - textbox should display 06

so the figure in the textbox should change with the record - that way I can apply conditional formatting to the form .

Thanks

 

[vbaInet]

>=DateAdd("m",-6,Date())

(2) By applying above -what the query did was it pulled the patients who visited ONCE during last six months as well, so what I need is people who came MORE than once only -so how do I modify or if I follow your method pls add that to it.

DateAdd() is fine too as long as you're getting the difference in dates that you want.

Did you create the query using the link provided in post #6? Let me see the SQL.