Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to show latest date in Access 2010?

Posted on 2013-05-22
18
423 Views
Last Modified: 2013-06-02
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?
0
Comment
Question by:Anthony6890
  • 8
  • 4
  • 4
  • +1
18 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39187508
not enough detail here to provide much help?

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?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39187536
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39187622
You will Group By Customer_Name (not Pay_Date) and select this additional column:

LatestPayDate: Max([Pay_Date])

/gustav
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39187635
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.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39187650
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?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39187680
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39187729
> will your idea still work?

Yes. Try it. Takes two minutes or so.

/gustav
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39187819
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39187856
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
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39187861
Agreed Gustav, I will try that.  thanks.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39187869
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39187893
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39187895
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39187929
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"?
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 39187971
Ok, here is the entire SQL...

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#];

Open in new window


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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39188051
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#];

Open in new window

0
 
LVL 1

Accepted Solution

by:
Anthony6890 earned 0 total points
ID: 39201344
I ended up getting this t work by creating two separate queries doing the max of two separate columns in each query and then combining them.  Thanks everyone for your help.
0
 
LVL 1

Author Closing Comment

by:Anthony6890
ID: 39214064
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.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question