By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,638 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,638 IT Pros & Developers. It's quick & easy.

Automatic Data Entry on the basis of data in another field in same record

P: 26
HI.

I have the following query:-
How do i create field which updates its contents on the basis of data in another field in same record.

especially if I have a Date field, how can I change the contents of Status field in the same record based on the date field. for ex:-
- If the date is maximum the content in status should be "Current"
- If there are 2 records for one ID and if the date field content is minimum the content in status field should be "Old".
- If there are 3 records for one ID and if the date field content is in between the max & min values, then it should be "Old" for the in between value and "Older" for the min value.

Please help.
May 1 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
This is why you should never store a calculated field in the table. Just calculate status dynamically in whichever query, form or report you need to use it in.
May 1 '07 #2

P: 26
Even I want to do the same.

Can you please help me out in doing this dynamically in a form or a report.
May 1 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Even I want to do the same.

Can you please help me out in doing this dynamically in a form or a report.
I'll have to think about this one.
May 1 '07 #4

P: 26
i shall be grateful
May 1 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
As I don't know the name of your table I've used Clients as the tablename.
Expand|Select|Wrap|Line Numbers
  1. SELECT C1.ID, [C1].[Date], C2.NumID, 
  2. IIf([C2].[NumID]=1,"Current",
  3. IIf([C2].[NumID]=2,IIf([C1].[Date]=MaxDate,"Current","Old"),
  4. IIf([C2].[NumID]=3,IIf([C1].[Date]=MaxDate,"Current",
  5. IIf([C1].[Date]=MinDate,"Older","Old"))))) AS Status
  6. FROM Clients1 AS C1 INNER JOIN 
  7. (SELECT [ID], Max([Date]) As MaxDate, Min([Date]) As MinDate, Count([ID]) As NumID FROM Clients1 GROUP BY ID) AS C2 
  8. ON C1.ID = C2.ID;
May 1 '07 #6

P: 26
Thanks
Will try this one

Regards

Vijayan
May 2 '07 #7

P: 26
hi

It worked.

Thanks a TON

REgards

Vijayan
May 2 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
hi

It worked.

Thanks a TON

REgards

Vijayan
You're welcome.
May 2 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.