date query (1 Viewer)

[cvaccess]

I have tried to create a query that shows the last date entered in the table. My table is indiv_tp_sub. The fields are SCRUB_DATE,CHILD_TP,CLAIM_SUB. The table has data entered in weekly, usually. So the scrub date would show for example, 7/10/02, 7/15/02,7/17/02,7/18/02. Based on this example, I want to pull the last date (most current, in this case 7/18/02) with the sum of claim_sub for each child_tp.

I can't figure this out. Please help.

Thanks.
cvaccess

 

[Jon K]

Try this total query (copy and paste to the SQL View of a new query and run it):-

SELECT CHILD_TP, Max(SCRUB_DATE) as LastDate, Sum(CLAIM_SUB) as SumOfClaim
FROM indiv_tp_sub
GROUP BY CHILD_TP

 

[cvaccess]

date query still has problem

Thanks, Jon. I had to add fields from another table to show just as output from the query. The other table is trading partners containing fields:tradingpartnernum(same as child_tp on indiv_tp_sub),tradingpartnername, and pcco approved.
I don't get anything when I run this query. Please help.
Thx.
SELECT indiv_tp_sub.CHILD_TP, Max(indiv_tp_sub.SCRUB_DATE) AS LastDate, Sum(indiv_tp_sub.CLAIM_SUB) AS SumOfClaim, [trading partners].tradingpartnername, [trading partners].ProviderTaxID, [trading partners].[PCCO Approved]
FROM indiv_tp_sub INNER JOIN [trading partners] ON indiv_tp_sub.CHILD_TP = [trading partners].tradingPartnerNum
GROUP BY indiv_tp_sub.CHILD_TP, [trading partners].tradingpartnername, [trading partners].ProviderTaxID, [trading partners].[PCCO Approved];

 

[Jon K]

There is no syntax error in your query, so it should work.

Since no error message has popped up and it simply returns no records, it signifies that there are no matching values in CHILD_TP and tradingPartnerNum in the tables.


You can run this query as a test:

SELECT *
FROM indiv_tp_sub INNER JOIN [trading partners] ON indiv_tp_sub.CHILD_TP = [trading partners].tradingPartnerNum;

If again no records are returned, you can be sure there are no matching CHILD_TP and tradingPartnerNum in the tables.