Anthony
asked on
How to show latest date in Access 2010?
Hi Everyone,
I have a query that is complex and runs great. The query displays the results from a cash payment table for individual customers. If a customer as 12 rows of payments (one for each month of the year), then all 12 show up in the result of the query. What I want is the latest paydate to show up. When I try to do the Group By, and elect my PAY_DATE field, it doesn't work.
Any suggestions?
I have a query that is complex and runs great. The query displays the results from a cash payment table for individual customers. If a customer as 12 rows of payments (one for each month of the year), then all 12 show up in the result of the query. What I want is the latest paydate to show up. When I try to do the Group By, and elect my PAY_DATE field, it doesn't work.
Any suggestions?
ASKER
Sorry about the lack of detail.
The query has three tables linked that pull values from each table based on one value being in the first table. The query provides these results:
Customer Name
Pay Date
Pay Amount
Old Store
New Store
If the customer name has payments for a year, I will have 12 rows for one person with the the Pay Date changing and potentially the Pay Amount changing (but this change is not applicable to the application).
When I run the query I want the result to just have the latest pay date row display as the result. So if someone has paid from 1/1/2013-5/1/2013, I just want the 5/1/2013 row to be displayed.
The query has three tables linked that pull values from each table based on one value being in the first table. The query provides these results:
Customer Name
Pay Date
Pay Amount
Old Store
New Store
If the customer name has payments for a year, I will have 12 rows for one person with the the Pay Date changing and potentially the Pay Amount changing (but this change is not applicable to the application).
When I run the query I want the result to just have the latest pay date row display as the result. So if someone has paid from 1/1/2013-5/1/2013, I just want the 5/1/2013 row to be displayed.
You will Group By Customer_Name (not Pay_Date) and select this additional column:
LatestPayDate: Max([Pay_Date])
/gustav
LatestPayDate: Max([Pay_Date])
/gustav
Hi,
Try adding a WHERE clause on your existing SQL statement (query) something like this:
WHERE Month([Pay Date])=Month(Now())
...or, if still using a GROUP BY clause, use:
HAVING Month([Pay Date])=Month(Now())
BFN,
fp.
Try adding a WHERE clause on your existing SQL statement (query) something like this:
WHERE Month([Pay Date])=Month(Now())
...or, if still using a GROUP BY clause, use:
HAVING Month([Pay Date])=Month(Now())
BFN,
fp.
ASKER
Gustav,
Is this expression: LatestPayDate: Max([Pay_Date])
a Total: Group By?
Fp, I can't guarantee that the customer will have a payment for this month, it could also be that the latest payment is from 3 months ago... will your idea still work?
Is this expression: LatestPayDate: Max([Pay_Date])
a Total: Group By?
Fp, I can't guarantee that the customer will have a payment for this month, it could also be that the latest payment is from 3 months ago... will your idea still work?
Fp, I can't guarantee that the customer will have a payment for this month, it could also be that the latest payment is from 3 months ago... will your idea still work?
No, sorry.
The MAX([Pay_Date]) for the Customer is the way to go in that case.
> will your idea still work?
Yes. Try it. Takes two minutes or so.
/gustav
Yes. Try it. Takes two minutes or so.
/gustav
ASKER
I'm having a problem entering this:
HAVING Month([Pay Date])=Month(Now())
I'm still using GROUP BY.
Where do I enter this line?
HAVING Month([Pay Date])=Month(Now())
I'm still using GROUP BY.
Where do I enter this line?
Why don't you use the GUI Query designer? Then you can switch back and forth between this and the SQL code ... and experiment and learn at the same time. Much easier.
/gustav
/gustav
ASKER
Agreed Gustav, I will try that. thanks.
ASKER
Gustav, there is something that might be a problem.
My PAY_DATE field is an expression...
PAY_DATE: DateSerial(Mid([BCPDTE] & "01",2,2),Mid([BCPDTE] & "01",4,2),Mid([BCPDTE] & "01",6,2))
Does this complicate things.
My PAY_DATE field is an expression...
PAY_DATE: DateSerial(Mid([BCPDTE] & "01",2,2),Mid([BCPDTE] & "01",4,2),Mid([BCPDTE] & "01",6,2))
Does this complicate things.
Anthony6890: It may make everybody's task easier if you post the entire SQL statement you are using.
We can then advise exactly what is required.
Thanks.
We can then advise exactly what is required.
Thanks.
Yes, if the Query asks for parameter Pay_Date, you may need to use:
LatestPayDate: Max(DateSerial(Mid([BCPDTE ] & "01",2,2),Mid([BCPDTE] & "01",4,2),Mid([BCPDTE] & "01",6,2)))
Is BCPDTE a string?
/gustav
LatestPayDate: Max(DateSerial(Mid([BCPDTE
Is BCPDTE a string?
/gustav
I'm trying to determine the purpose of adding the "01" onto the end of field [BCPDTE].
Can you provide some examples of the values in that field and explain why you think you need to add the "01"?
Can you provide some examples of the values in that field and explain why you think you need to add the "01"?
ASKER
Ok, here is the entire SQL...
The values in BCPDTE are: 10304 (CYYMM)
Pay Date isn't a variable, it's simply just an expression field converting a CYYMM date from an iseries into a date value.
SELECT BILMAC_MBRLOG.[MLSS#], BILMAC_MBRLOG.MLFILE, BILMAC_MBRLOG.MLFLD, BILMAC_MBRLOG.MLOLCO, BILMAC_MBRLOG.MLNUCO, CDate(IIf(Len([MLCHDT])=7,Format(Mid([MLCHDT],4,2) & "-" & Mid([MLCHDT],6,2) & "-" & Mid([MLCHDT],2,2),"mm/dd/yyyy"),Format(Mid([MLCHDT],3,2) & "-" & Mid([MLCHDT],5,2) & "-" & Left([MLCHDT],2),"mm/dd/yyyy"))) AS CHANGE_DATE, CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm/dd/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm/dd/yyyy"))) AS HIRE_DATE, CDate(IIf(Len([MBELDT])=7,Format(Mid([MBELDT],4,2) & "-" & Mid([MBELDT],6,2) & "-" & Mid([MBELDT],2,2),"mm/dd/yyyy"),Format(Mid([MBELDT],3,2) & "-" & Mid([MBELDT],5,2) & "-" & Left([MBELDT],2),"mm/dd/yyyy"))) AS ELIG_DATE, LBSI_BCHCSHHC.BCAMT2, DateSerial(Mid([BCPDTE] & "01",2,2),Mid([BCPDTE] & "01",4,2),Mid([BCPDTE] & "01",6,2)) AS PAY_DATE
FROM (BILMAC_MBRLOG LEFT JOIN BILMAC_MBRMSTR ON BILMAC_MBRLOG.[MLSS#] = BILMAC_MBRMSTR.[MBSS#]) LEFT JOIN LBSI_BCHCSHHC ON BILMAC_MBRLOG.[MLSS#] = LBSI_BCHCSHHC.[BCSS#]
WHERE (((BILMAC_MBRLOG.[MLSS#])<>123456789) AND ((BILMAC_MBRLOG.MLFILE)="MBRMST") AND ((BILMAC_MBRLOG.MLFLD)="MBSTOR") AND ((BILMAC_MBRLOG.MLOLCO)<>"000000" And (BILMAC_MBRLOG.MLOLCO)<>"000" And (BILMAC_MBRLOG.MLOLCO)<>"") AND ((BILMAC_MBRLOG.MLNUCO)<>"002" And (BILMAC_MBRLOG.MLNUCO) Not Like "0000*") AND ((Left([MLOLCO],3))<>Left([MLNUCO],3)) AND ((BILMAC_MBRLOG.MLCHDT)>=[Forms]![RUNDTEf].[CONVERTED_START] And (BILMAC_MBRLOG.MLCHDT)<=[Forms]![RUNDTEf].[CONVERTED_END]) AND ((BILMAC_MBRMSTR.MBSTUS)="A") AND ((LBSI_BCHCSHHC.BCTYPE)="P"))
ORDER BY BILMAC_MBRLOG.[MLSS#];
The values in BCPDTE are: 10304 (CYYMM)
Pay Date isn't a variable, it's simply just an expression field converting a CYYMM date from an iseries into a date value.
To save anybody else the hassle of re-formatting the SQL statement into something (semi-)legible:
SELECT
BILMAC_MBRLOG.[MLSS#],
BILMAC_MBRLOG.MLFILE,
BILMAC_MBRLOG.MLFLD,
BILMAC_MBRLOG.MLOLCO,
BILMAC_MBRLOG.MLNUCO,
CDate(IIf(Len([MLCHDT])=7,
Format(Mid([MLCHDT],4,2) & "-" & Mid([MLCHDT],6,2) & "-" & Mid([MLCHDT],2,2),"mm/dd/yyyy"),
Format(Mid([MLCHDT],3,2) & "-" & Mid([MLCHDT],5,2) & "-" & Left([MLCHDT],2),"mm/dd/yyyy")
)
) AS CHANGE_DATE,
CDate(IIf(Len([MBHIRE])=7,
Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm/dd/yyyy"),
Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm/dd/yyyy")
)
) AS HIRE_DATE,
CDate(IIf(Len([MBELDT])=7,
Format(Mid([MBELDT],4,2) & "-" & Mid([MBELDT],6,2) & "-" & Mid([MBELDT],2,2),"mm/dd/yyyy"),
Format(Mid([MBELDT],3,2) & "-" & Mid([MBELDT],5,2) & "-" & Left([MBELDT],2),"mm/dd/yyyy")
)
) AS ELIG_DATE,
LBSI_BCHCSHHC.BCAMT2,
DateSerial(Mid([BCPDTE] & "01",2,2),Mid([BCPDTE] & "01",4,2),Mid([BCPDTE] & "01",6,2)) AS PAY_DATE
FROM
(
BILMAC_MBRLOG
LEFT JOIN BILMAC_MBRMSTR
ON
BILMAC_MBRLOG.[MLSS#] = BILMAC_MBRMSTR.[MBSS#]
)
LEFT JOIN LBSI_BCHCSHHC
ON
BILMAC_MBRLOG.[MLSS#] = LBSI_BCHCSHHC.[BCSS#]
WHERE
(
(
(BILMAC_MBRLOG.[MLSS#])<>123456789
)
AND
(
(BILMAC_MBRLOG.MLFILE)="MBRMST"
)
AND
(
(BILMAC_MBRLOG.MLFLD)="MBSTOR"
)
AND
(
(BILMAC_MBRLOG.MLOLCO)<>"000000"
And
(BILMAC_MBRLOG.MLOLCO)<>"000"
And
(BILMAC_MBRLOG.MLOLCO)<>""
)
AND
(
(BILMAC_MBRLOG.MLNUCO)<>"002"
And
(BILMAC_MBRLOG.MLNUCO) Not Like "0000*"
)
AND
(
(Left([MLOLCO],3))<>Left([MLNUCO],3)
)
AND
(
(BILMAC_MBRLOG.MLCHDT)>=[Forms]![RUNDTEf].[CONVERTED_START]
And
(BILMAC_MBRLOG.MLCHDT)<=[Forms]![RUNDTEf].[CONVERTED_END]
)
AND
(
(BILMAC_MBRMSTR.MBSTUS)="A"
)
AND
(
(LBSI_BCHCSHHC.BCTYPE)="P"
)
)
ORDER BY
BILMAC_MBRLOG.[MLSS#];
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Needed to come up with an alternative way of reaching the max of two fields and was able to figure it out on my own.
What does the query look like that "runs great". What have you tried for this new query?
"What I want is the latest paydate to show up."
show up where? do you want this as a separate row in the query results?