Query for Deliquent Accounts

[geebanga88]

Hi i have the follow Database schema:

Patient:
PK PatientNo
FirstName
LastName

PatientAccount
P/FK PatientNo
P/FK ProivderNo
PK Date

Payment
PK Date
P/Fk PatientNo
P/Fk ProivderNo
P/Fk PaymentDate

Was wondering how i could get(select) all the patients with accounts that havent payed thier accounts within 42 days of the creation date.
References will also be most welcome.

[Jim Doherty]

Hi i have the follow Database schema:

Patient:
PK PatientNo
FirstName
LastName

PatientAccount
P/FK PatientNo
P/FK ProivderNo
PK Date

Payment
PK Date
P/Fk PatientNo
P/Fk ProivderNo
P/Fk PaymentDate

Was wondering how i could get(select) all the patients with accounts that havent payed thier accounts within 42 days of the creation date.
References will also be most welcome.

Assuming there to be a correllation between ProviderNo in Patient Accounts and ProviderNo in Payment then the date difference in days is calculated from the Date column in PatientAccounts and the current date using the DateDiff function. It is included as column in itself as part of the SQL. You can then control the dataset based on that ie greater than 42 (>42) and only where the PaymentDate is null

 
SELECT PatientAccounts.PatientNo, Patient.FirstName, Patient.LastName, PatientAccounts.Date, DateDiff("d",[PatientAccounts].[Date],Date()) AS DaysSince
FROM (PatientAccounts LEFT JOIN Patient ON PatientAccounts.PatientNo = Patient.PatientNo) LEFT JOIN Payment ON (PatientAccounts.ProviderNo = Payment.ProviderNo) AND (PatientAccounts.PatientNo = Payment.PatientNo)
WHERE (((DateDiff("d",[PatientAccounts].[Date],Date()))>42) AND ((Payment.PaymentDate) Is Null));
 

The above SQL mirrors your table and fieldnames which thankfully you posted therefore you should just be able to copy and paste the above into the Access query design SQL window, then view it in design and see if it befits your needs.

Regards

Jim :)

[geebanga88]

ahh thanks for the qry, i have the table patientaccounts as "patient account" in access so once i edited that e.g SELECT Patient Account.PatientNo it comes up with missing operator? do i needed to put a type of brackets around it or leave out the space. If i put it as one word it a syntax error has occured.

[Jim Doherty]

ahh thanks for the qry, i have the table patientaccounts as "patient account" in access so once i edited that e.g SELECT Patient Account.PatientNo it comes up with missing operator? do i needed to put a type of brackets around it or leave out the space. If i put it as one word it a syntax error has occured.

Keep spaces out of table names and fieldnames. If you are unable to do that then wrap in square brackets ie [Patient Account] if you still have problem post your SQL back and I will look at it :)

Jim

[geebanga88]

Ok i tried wraping it but still failed with an syntax error join. I then renamed it so it has no spaces, but i get the same error so wraping it produced the same result. (meaing wraping can work :D)

Statement:

 
SELECT PatientAccount.PatientNo, Patient.FirstName, Patient.LastName, PatientAccount.Date, DateDiff("d",[PatientAccount].[Date],Date()) AS DaysSince
FROM (PatientAccount LEFT JOIN Patient ON PatientAccount.PatientNo = Patient.PatientNo) LEFT JOIN Payment ON (PatientAccount.ProviderNo = Payment.ProviderNo) AND (PatientAccounts.PatientNo = Payment.PatientNo)
WHERE (((DateDiff("d",[PatientAccount].[Date],Date()))>42) AND ((Payment.PaymentDate) Is Null)); 
 

Error occurs at second statement, AND (PatientAccounts.PatientNo = Payment.PatientNo) saying "syntax error in join operation"

[geebanga88]

AAHH i think i noe why this is happening i have, Patient.Patinet No and PatientAccount.PatientNo (no space) same kinds of error.Also made sum spaces in other columns so ill get back to you once i have fixed these errors.

[geebanga88]

ahhh yes finally got it, all the spaces between the words was the main problem. Thanks for the help, and great query:D

[Jim Doherty]

ahhh yes finally got it, all the spaces between the words was the main problem. Thanks for the help, and great query:D

You're welcome finally got there in the end :) spaces cause untold problems best to keep to a discipline of 'none at all', that way brackets never enter into it and it scales up much easier too.

Regards

Jim :)