Access SQL queries

[Lshamz]

I have some homework with Access that is very difficult, making queries with SQL..



Here is one question for example:

List the10 patients with highest annual Medicare bill for year 1996. Result forma by: [PatientID, Year, TotalBilledAmount]

So I started with
SELECT TOP 10 SUM(BilledAmount) AS TotalBilledAmount, PatientID , PaymentDate AS
FROM Claim
WHERE Year = 1996 ???
GROUP BY PatientID, TotalBilledAmount, Year
ORDER BY SUM(BilledAmount)

I know you cant see the database and column names but they are: BilledAmount, PatientID, PaymentDate, DateOfService, [PaidAmount &DateOfPayment bot from table name called ClaimPayment)...and there isn't a column for year? ...that's where I'm confused...we have two years: 1996 & 1997 listed like dates such as 5/16/1996 and 5/16/1997 for example but does this mean I have to separate the dates by year into 2 new tables or what? I'm so confused.can anyone help make this query for me given the column names?

thanks so much im so confused

[ranman256]

The year is probably in [paymentDate] , extract the year with
year([paymentDate])

[Lshamz]

So I just write WHERE PaymentDate= 1997 ?

[ssanfu]

Use the VBA Year() function to get just the year. Try

SELECT TOP 10 SUM(BilledAmount) AS TotalBilledAmount, PatientID , Year(PaymentDate) AS TheYear
FROM Claim 
WHERE Year(PaymentDate) = 1996 
GROUP BY PatientID, TotalBilledAmount, Year(PaymentDate) 
ORDER BY SUM(BilledAmount)