Link to home
Create AccountLog in
Avatar of PipMic
PipMicFlag for Gibraltar

asked on

Tagging records

Hi all,

My problem is as follows:

I have a table of clients. Each clients record has a field, named "DateFrom".

The table is updated constantly whereby additional records are being appended. There are moments when the record appended has the same client name but different date in the "DateFrom" field. So I can end up with something like the following:-

record1  (A.N Other, 1/1/10)
record2  (A.N Other, 1/1/11)

The routine I am trying to devise by seeking assistance is that when I load the table via a form that a routine will toggle the most recent record)

Grateful for any assistance or advice.

Thanks
(using Access 2003)
Avatar of als315
als315
Flag of Russian Federation image

It is not clear how are you trying to get records on your form. Sample DB will be good.
As a proposal - add query, where will be group by clientID and max by DateFrom. Join your table and this query and you will have only recent dates. May be in this case you will not be able to update records.
Also Not sure what you mean by "Toggle" the records...?

1. Info like this is sometimes better handled with a One-To-Many relationship.
One Client, Many Date Records...
Then you can create a Main form to select the Customer, and a subform to display Date Records (Then use the navigation button to quickly toggle between the first and last DateFrom Records.
You will have to set the "Order By" property of the subform to: DateFrom DESC
(and make sure the "Order By On Load" property is set to: Yes)

2. While you can certainly do what you are asking for here directly,...it is not clear what "Toggle" means, or if you want to simply "Find" (display) the most recent record or if you want to "Filter for" the most recent record.
Besides, you would still need a system to select the Customer.

Using a Main/subform seems simpler to me...

JeffCoachman
Avatar of PipMic

ASKER

Thanks all, and apologies for not replying earlier.

I am in the process of creating a sample to upload but before I do that and in order to make it clearer, I have written the following to illustrate what I am doing and trying to do.

I am attaching the word document explaining my problem a bit better (hopefully).

Once again grateful for advice.
EE-tagging-records.doc
Look at sample. qryMain has only last records. qryMainTags - with tags.
DBTags.mdb
Avatar of PipMic

ASKER

Hi als315,

Working on this principle... will get back to you asap.

Thanks for the idea
Avatar of PipMic

ASKER

hi,

The end result of tbl_Main should be the result as expressed by qry_MainTags.

Which means I would require an additional field in tbl_Main which would be updated with the result of qry_MainTags.

I have tried different types of queries, updates etc. but I cant seem  to achieve the desired result.

Grateful for your assistance once again.
It is not clear why do you like to have this field in table, when you can get expected result in query?
Avatar of PipMic

ASKER

Hi,

True but its just one of those things :)  keeping it all together.

Basically, I already have queries and reports based on that table. my thoughts were to keep it altogether so that way it would facilitate it my work.

But if it cant be done...I may have to rethink it.
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of PipMic

ASKER

Hi,

Many thanks for your advice.

You've given me food for thought.....in fact I could include this last update query as part of a routine, perhaps a macro, when i append the single record to the main table.

Will try that approach now.

Thanks
Avatar of PipMic

ASKER

hi,

DMax("datefrom","tblMain","[id] ='" & [id] & "'")=[datefrom]

How does it read?

i.e. Find the Max (the most recent "datefrom" form the table "tbl_tblMain" where "id" is......

I can't figure the remaining arguments even though it does what  I want.
Grateful for this assistance.
Dmax interpretation is correct (id in tblMain equal id in query)
If result of DMax(...) = [datefrom] then True
else False
Avatar of PipMic

ASKER

still unsure...

I am assuming that the update query stands on its own and is no longer reliant on the other sample queries to carry out the tagging.

In which case i still dont understand the logic of the statement

DMax("datefrom","tblMain","[id] ='" & [id] & "'")=[datefrom]

So...

Find the maximum value for tblMain where  [criteria].....???

Its the criteria and the True Else False section that I am unsure...

Thanks for your patience...
Yes, you can use select query, where Tag will be calculated field or update records in table.
Logic of update query:
1. Dmax: criteria is id from table (tblMax) equal to id in current record in query
2. In update field we can directly use boolean result of equation, for example:
A = B
result will be true if A = B and false if A <> B.
So if returned value from Dmax equal to datefrom in current record in query, result will be true.
Avatar of PipMic

ASKER

Excellent example and very good assistance at understanding the problem.