Designing A/R aging Form

[June7]

So after March import there would be 3 records. The resulting balance would be 0 and you want all three records deleted?

[Ray67]

Yes it would be deleted off the list.
The list would only show all new charges and old charges with balance that we are still working on.

So after March import there would be 3 records. The resulting balance would be 0 and you want all three records deleted?

[Ray67]

Last Status	Last Status Date	New Status	Status Date	User
Called Pt	7/1/2014	Recently Paid	8/1/2014	Betty
Awaiting Payment Cycle	7/1/2014

Need a little help on how I should go about the status column.
Every time a user works on an account they would enter a new status. I would like to be able to build reports and query base on the status.
Example
One query would show me all patient that have a Status but with the most current status


Should I put three columns in the table? Status1 Date Status2 Date Status3 Date
Or Is there another why of going about this?

[June7]

Automating the deletion of records where they aggregate to a 0 balance is an unusual practice but I suppose could be done. I am just not sure how. Perhaps a DELETE sql action. Maybe:

DELETE FROM tablename WHERE PtID IN (SELECT PtID FROM tablename WHERE Sum(Charges) - Sum(Payments) - Sum(Adjustments) = 0 GROUP BY PtID);


Multiple status fields is not normalized structure and will cause problems. Maybe:

SELECT * FROM tablename WHERE StatusDate = DMax("StatusDate","tablename","PtID=" & [PtID]);

[Ray67]

I don't want to delete any of the records.
I would like to keep all of the records So if the patient had two status i would be able to see what the first status was and the second and if i build any reports i would be able to show last status.
How would i going about normalized status column?(still have time to make any corrections)




SELECT * FROM tablename WHERE StatusDate = DMax("StatusDate","tablename","PtID=" & [PtID]); (this code works great)

Example

	Status1	Date1	Status2	Date2	Status3	Date3
Doe John	Billed	8/1/2014	Awaiting Payment Cycle	8/12/2014
Doe Mike	Paid	8/15/2014

[June7]

My question in post 16 asked if you wanted to delete records. I thought the answer in post 17 was "yes". Actually, you only meant exclude from query. Try:

SELECT * FROM tablename WHERE PtID IN (SELECT PtID FROM tablename WHERE Sum(Charges) - Sum(Payments) - Sum(Adjustments) <> 0 GROUP BY PtID);

Normalized Status would be a record for each status 'event'. But if you feel a non-normalized structure is satisfactory then go for it. Be aware that searching multiple similar fields presents its own complications. Also, number of status actions that can be documented limited by the number of status fields.

[Ray67]

Normalized status i think would be best.
So would i have to build a status table? and if so how would i link it to the main table?

[June7]

A status table does sound reasonable but since I don't know your data and business process, I can't be sure what you need. Regardless, I expect these records would need the PtID as foreign key.

Perhaps a subform on Patients form.