Good Day All,
I have a query that involves two linked tables that looks something like the example below(Linked on Provider ID):
Table A Table B
ProviderID (Primary Key) ProviderID(not Primary Key) Date
prov123 prov123 1/1/2000
prov124 prov123 1/2/2000
prov125 prov125 1/3/2000
I am looking to create a query that returns one(and only one) Max Date for each provider in Table A. At the same time I need the count of returned data to reflect only the count of providerid's in table A.
I am gong to use a cross tab to do this.
What I am running into is that the counts reflect the total number of records in table B no matter how I put the data together. So prov123 would have a count of 2 when there really there is only 1 Max Date for prov123.
Im thinking that I need to somehow get the Select expert to only recognize the max date for the date field in Table B but am not sure how to do it. Any thoughts?
IP Logged
