Query Where Code

[swt027]

Show all the patient states that end in the letter“I” (capital i).
How do I isolate "I"


Where P_state like ??

[kd2017]

SELECT [the field you want to show] FROM [your table or query] WHERE [the field you're searching] LIKE "*I"

[swt027]

Thank you! Can I ask you another question on here or do I have to start a new thread?

[kd2017]

i dunno... go for it!

[swt027]

Calculatethe average balance for all patients of each therapist

Patient_Number	P_LastName	P_FirstName	P_Address	P_City	P_State	P_ZipCode	P_Balance	P_Insurance	TherapNumber
AL26	Alardyce	Lisa	311 Birchwood	Lamont	MI	49160	$196.62	$180.00	05
BR31	Bryce	Roger	617 College	Lamont	Mi	49160	$96.00	$0.00	08
DI32	Dalton	Irene	41 Lafayette	Madison	IN	42909	$875.00	$600.00	14
GC92	Gutierez	Carlos	476 Fulton	Jakson	OH	49401	$273.00	$150.00	05
GT43	Grant	Thomas	247 Fuller	Lamont	MI	49160	$276.00	$0.00	08
JG22	Jenkins	Glen	201 Plymouth	Madison	IN	42909	$0.00	$0.00	08
LI66	Lawrence	Irving	912 Devonshire	Beulah	MI	45621	$346.50	$175.00	05
PE33	Pezato	Eduardo	346 Vernor	Homer	MI	49162	$467.12	$500.00	14
PE76	Perez	Enzo	216 Four Mile	Perry	MI	47211	$216.00	$0.00	08
T73	Acton	Thomas	312 Newcastle	Homer	MI	49162	$726.42	$550.00	08

SELECT Therapnumber
FROM Patient_Table;
Having avg(P_balance)

This didn't work

[swt027]

Figured it out
SELECTavg(P_balance) as NewPtBaln, Therapnumber
FROM Patient_Table
Group by Therapnumber

[kd2017]

You want an aggregate query

SELECT Patient_Number, P_LastName, TherapNumber, Avg(P_Balance) AS AvgBalance
FROM Patient_Table
GROUP BY Patient_Number, P_LastName, TherapNumber;

But it will be alot easier to build your query in the query designer. https://www.youtube.com/watch?v=CgBcI8rq10k

[davegri]

SELECT Patient_Table.TherapNumber, Avg(Patient_Table.P_Balance) AS AvgOfP_Balance
FROM Patient_Table
GROUP BY Patient_Table.TherapNumber;

[June7]

Are these class assignments? Could build a report and use its Sorting & Grouping with aggregate calcs. This will allow display of detail records as well as summary calculations.