Hello,

I have a table that holds visits (including a date) for a provider visit (so, multiple dates for the same provider). [tblAllVisitsThru2001]

I need to populate another table (which holds about 150 records) with the most recent date for the same provider from above mentioned table. [tbl2003NewVisits]


How do I do this using just SQL? Is it even possible??

I have tried several permutations of this and got zero results (this one to select all dates from year 2001):

SELECT tbl2003NewVisits.*, tblAllVisitsThru2001.Review, tblAllVisitsThru2001.Date, tblAllVisitsThru2001.Rate, tblAllVisitsThru2001.Records, tblAllVisitsThru2001.Date
FROM tbl2003NewVisits INNER JOIN tblAllVisitsThru2001 ON tbl2003NewVisits.pin = tblAllVisitsThru2001.PIN
WHERE (((tblAllVisitsThru2001.Date)>=#1/1/2001#) AND ((tblAllVisitsThru2001.Date)<=#12/1/2001#));

Joyce

the sql should look like
select provider
,max(date_column)
from table.

as an insertinto statment

insert into tableb
select provider
,max(date_column)
from tableA.

as an update statment

update tableb
set date = max(date)
from tablea
where b.provider = a.provider

those are the generic sql forms
let us know if you need more
Jim

Hi,

Thanks. I had previously tried max in my select statement and would get this message:
Had not thought to put it in an update statement, so I tried it, and now am getting:


actual code below:
Thought that maybe it was because of a reserved word conflict (date) so I changed it to an update statement I think my problem us that I have 150 records I am trying to find the WHERE providerID = providerID and can't seem to do a join since there are multiple rows for a provider in the main table that I am trying to pull from, because even without the clause regarding the date, trying to do a join on the table where I have one providerID (with the 150 rows) and the main table (900 rows) but when I take the join out I get 21000 plus rows (I do understand why this happens). Does that make sense?

Thanks in advance,
~Joyce

it's me again and it's domain aggregate functions again

result = DMax("[FieldYouWantToFindTheMaxFor]", "theQuery")



izy

The erros taht you are getting are because of my stupidity!!!!
you need a group by statment for all the columns that are not in the max statment, max is treated like an aggr function in that regards
so
add
group by column name at the end and you will get the correct answer
Jim

I just wanted to thank everyone who tried to help me with the Max(Date) issue I was having.

I ended up using this

and then attempting a join it to my table with the one provider id (which is very similar to what I had been doing and I kept getting zero rows or errors due to my own fault), but I finally figured out that I had made an erroneous assumption about my data; I had thought that what would exist in one would exist in the other (due tot he nature of the subsets of data I had compiled) but in fact the one table that had the one record per provider that I was initially trying to join to the table that had many records (many dates) per provider were mutually exclusive!

There is one learning experience I won't shortly forget....though I am sure I learned it before, in a galaxy far, far away.....

Thanks again from one sometime confused database droid named Joyce