Microsoft Access

[Brentastic]

Ok, so I am trying to get the max date from several fields of dates. For instance, if a borrower receives a loan that has several disbursements (let's say 4) I want to know the most recent date - in this case, one record (in this situation, one borrower) will have 4 separate date fields (columns). I cannot figure this one out, can anyone help?

[Kid Cid]

Are you at all comfortable with SQL or do you do all your queries using the GUI?

[Brentastic]

Are you at all comfortable with SQL or do you do all your queries using the GUI?

I took an SQL class about 4 months ago but we haven't implemented it yet so I have had no practice. I create all my queries in design view in Access. Good news though, I came up with a solution that worked perfectly and it looks something like this:

 

IIf([disb4_date]<>"0000000" And [disb4_date]>=[disb3_date] And [disb4_date]>=[disb2_date] And [disb4_date]>=[disb1_date],[disb4_date],IIf([disb3_date]<>"0000000" And [disb3_date]>=[disb4_date] And [disb3_date]>=[disb2_date] And [disb3_date]>=[disb1_date],[disb3_date],IIf([disb2_date]<>"0000000" And [disb2_date]>=[disb4_date] And [disb2_date]>=[disb3_date] And [disb2_date]>=[disb1_date],[disb2_date],IIf([disb1_date]<>"0000000" And [disb1_date]>=[disb4_date] And [disb1_date]>=[disb3_date] And [disb1_date]>=[disb2_date],[disb1_date]))))

[Kid Cid]

I took an SQL class about 4 months ago but we haven't implemented it yet so I have had no practice. I create all my queries in design view in Access. Good news though, I came up with a solution that worked perfectly and it looks something like this:

 

IIf([disb4_date]<>"0000000" And [disb4_date]>=[disb3_date] And [disb4_date]>=[disb2_date] And [disb4_date]>=[disb1_date],[disb4_date],IIf([disb3_date]<>"0000000" And [disb3_date]>=[disb4_date] And [disb3_date]>=[disb2_date] And [disb3_date]>=[disb1_date],[disb3_date],IIf([disb2_date]<>"0000000" And [disb2_date]>=[disb4_date] And [disb2_date]>=[disb3_date] And [disb2_date]>=[disb1_date],[disb2_date],IIf([disb1_date]<>"0000000" And [disb1_date]>=[disb4_date] And [disb1_date]>=[disb3_date] And [disb1_date]>=[disb2_date],[disb1_date]))))

Never mess with success. Carry on.

[Brentastic]

Never mess with success. Carry on.

Agreed - thanks for your support.

[Big Country]

Glad you got it to work, but, under data normalization rules having a flat table with those dates as separate columns is likely incorrect methodolgy.

 

It should probably be a simple table that would reference the loan number, date and amount of disbursements. Then you would just do a lookup for the latest date for a disbursement on a loan, and could easily pull up all disbursements for a loan, etc.