Easier way to select most recent value...

  • Thread starter Thread starter Ben Moore
  • Start date Start date
B

Ben Moore

I have a database that uses three different tables one for employee
information, one for wage information, and one for each employee's
status within the company. These table are related by employee ID numbers.

Here's the deal. I work the databases when I can, but bosses want to be
able to make changes on their own without having to learn query
language. Well, I have been using some thing like:

In (SELECT Max(T.dateChange)FROM status as T WHERE T.empId =
teamMembers.empId)

to select the most recent entry from the status table based on a certain
employee ID. Well, this is a little too complicated when it needs to be
applied to other queries.

Is there a way I can write a module/macro or SOMETHING where they can
just use it like a function? I'm browsing these newgroups and googling
like crazy, but I figure the fastest way is to just come out and ask for
help.

Any help is greatly appreciated. Thanks in advance.

cheers,
Ben Moore
 
Dear Ben:

I suggest you write a query that returns empId and dateChange, plus
any other columns you will need. Then join to that query instead of
to the table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Ok, I think I understand what you mean, but not 100% sure... you mean to
create a generic query that uses the Max(t.dateChange) stuff from
before, and saving that. Then, using that to extract the fields we need
for future queries?

My wording may not be the clearest, but of course I may not be
understanding correctly. If I add my SQL code to the criteria field, and
save the query, it should work the same if I add more criteria by
joining with a new query right? Man oh man, I just confused myself
writing this.

Thanks for the help.

cheers,
Ben Moore
 
Dear Ben:

I think your understanding of what I meant is correct.

Just thing of the result of this new query as being like a table,
except that it dynamically changes to show the newest row according to
the changeDate whenever a new row is added to status. This should
tend to make additional queries easier to write, as you have
"packaged" this functionality for easy reuse. Sort of like writing a
function you can reuse.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom:

Thanks a million! Worked like a charm, and now hopefully I won't have to
come back in the middle of the semester to design a few more queries.
Now if only they would let me remote desktop in, I would never have to
come in to the office. Oh the joys of VBA in front of the television...

Thanks again.

cheers,
Ben Moore
 
Is there a way I can write a module/macro or SOMETHING where they can
just use it like a function? I'm browsing these newgroups and googling
like crazy, but I figure the fastest way is to just come out and ask for
help.
Click to expand...

You can use the DMax() function in a criterion to retrieve the latest
date; this will not interfere with updatability.
 
Back
Top