Newby Here, Query Outcome

[Woodyrus]

I would like my data in the excel file to reference the excel pivot table with one query, is that possible? If so, how do I set that up?

MaxOfCLAIM_RECEIVED_DATE	CLAIM_ENTERED_BY	CEV_CLM_ID	CLAIM_STATUS	CLAIM_STATUS_DATE	LINE_NUMBER
15-Jan-25	CM004288	961798059	DN	30-Jan-25	1
13-Jan-25	JB004615	717777213	ALW	30-Jan-25	1
10-Jan-25	JB004615	488695412	DN	30-Jan-25	1
03-Dec-24	JB004615	614481021	DN	30-Jan-25	1
17-Jan-25	SS004830	988145564	DN	30-Jan-25	1
27-Jan-25	SS004830	630465278	DN	30-Jan-25	1
16-Jan-25	SS004830	33063289	DN	30-Jan-25	1
10-Jan-25	JB004615	783435101	DN	30-Jan-25	1
23-Dec-24	JH002299	468925296	PD	30-Jan-25	1
23-Dec-24	JH002299	380955296	PD	30-Jan-25	1
04-Dec-24	JB004615	120751507	DN	30-Jan-25	1
03-Dec-24	JB004615	138390452	DN	30-Jan-25	1
13-Jan-25	JB004615	951973247	DN	30-Jan-25	1
10-Jan-25	VL001752	927129241	ALW	30-Jan-25	1
09-Jan-25	JB004615	880156204	DN	30-Jan-25	1
13-Jan-25	JB004615	886420996	DN	30-Jan-25	1
04-Dec-24	JB004615	785811838	DN	30-Jan-25	1
13-Jan-25	JB004615	876478575	DN	30-Jan-25	1
03-Dec-24	JB004615	94879672	DN	30-Jan-25	1
21-Jan-25	SV004617	35532260	DN	31-Jan-25	1
31-Jan-25	JM004376	610825893	DN	31-Jan-25	1
30-Jan-24	JM004376	195199788	ALW	31-Jan-25	1
24-Oct-24	JM004376	198898345	ALW	31-Jan-25	1
27-Dec-24	SV004617	455015671	DN	31-Jan-25	1
30-Dec-24	SV004617	53768101	DN	31-Jan-25	1
10-Dec-24	CM004288	300429844	PD	31-Jan-25	1
21-Jan-25	SV004617	66801952	DN	31-Jan-25	1
04-Dec-24	JB004615	660567722	DN	31-Jan-25	1
10-Dec-24	CM004288	399829996	DN	31-Jan-25	1

See this in Access:

Count of CEV_CLM_ID	Column Labels
Row Labels	ALW	DN	PD	(blank)	Grand Total
CM004288		2	1		3
JB004615	1	12			13
JH002299			2		2
JM004376	2	1			3
SS004830		3			3
SV004617		4			4
VL001752	1				1
(blank)
Grand Total	4	22	3		29
SELECT Max(dbo_FINANCE_CLAIM_DETAIL.CLAIM_RECEIVED_DATE) AS MaxOfCLAIM_RECEIVED_DATE, dbo_FINANCE_CLAIM_DETAIL.CLAIM_ENTERED_BY, dbo_CEV_CLM_EV.CEV_CLM_ID, dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS, dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS_DATE, dbo_FINANCE_CLAIM_DETAIL.LINE_NUMBER FROM dbo_CEV_CLM_EV INNER JOIN dbo_FINANCE_CLAIM_DETAIL ON dbo_CEV_CLM_EV.CEV_CLM_ID = dbo_FINANCE_CLAIM_DETAIL.CLAIM_NUMBER GROUP BY dbo_FINANCE_CLAIM_DETAIL.CLAIM_ENTERED_BY, dbo_CEV_CLM_EV.CEV_CLM_ID, dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS, dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS_DATE, dbo_FINANCE_CLAIM_DETAIL.LINE_NUMBER HAVING (((dbo_FINANCE_CLAIM_DETAIL.CLAIM_ENTERED_BY) Not Like "EDI837*" And (dbo_FINANCE_CLAIM_DETAIL.CLAIM_ENTERED_BY) Not Like "MC*") AND ((dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS) Not Like "SUS*" And (dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS) Not Like "VD*") AND ((dbo_FINANCE_CLAIM_DETAIL.CLAIM_STATUS_DATE)>=Dat e()-7) AND ((dbo_FINANCE_CLAIM_DETAIL.LINE_NUMBER)=1));

[mike60smart]

I imported your Excel data into a new table in Access

I then created a Crosstab query based on this table as follows:-

TRANSFORMCount(tblData.[MaxOfCLAIM_RECEIVED_DATE]) AS CountOfMaxOfCLAIM_RECEIVED_DATESELECT tblData.[CLAIM_ENTERED_BY], Count(tblData.[MaxOfCLAIM_RECEIVED_DATE]) AS [Total Of MaxOfCLAIM_RECEIVED_DATE]
FROM tblData
GROUP BY tblData.[CLAIM_ENTERED_BY]
PIVOT tblData.[CLAIM_STATUS];

This produced the following output:-