How to Query for the most recent payment by date in the format YYMMDD

[Russellh]

Name: screenshot 1.png  Views: 19  Size: 23.0 KB  ID: 22113" class="thumbnail" style="float:CONFIG" />

In the query result above I used a specific mbrsep (account#) to make things simple. What I need to do is query this table that contains every payment any customer has ever paid for just the payment amt and date of the most recent payment they made, whether that was last week or last year.

I will have another query reference this one for the information. So I need this query to only return 1 record for each mbrsep and that be the one for the most recent date. Not really using dates here are the field that contains the payment date is just a numerical field with the date in the format of YYMMDD which means basically I am querying for the record with the largest number in this field.

How can I achieve that?

[pbaldy]

One way:

http://www.baldyweb.com/LastValue.htm

[Russellh]

One way:

http://www.baldyweb.com/LastValue.htm

I simplified by only looking at 1 mbrsep. This table will include 30-50K mbrseps and a record for each payment made by that member sep. Would I still use Max?

[pbaldy]

Yes; you didn't try it?

[Russellh]

Yes; you didn't try it?

Of Course I tried it.
SELECT MST1_CAV_MBRHISTDETL.MBRSEP, MST1_CAV_MBRHISTDETL.LOCATION, MST1_CAV_MBRHISTDETL.BILLDATE, MST1_CAV_MBRHISTDETL.READDATE, MST1_CAV_MBRHISTDETL.NBRDAYSSVC, MST1_CAV_MBRHISTDETL.BILLTYPE, MST1_CAV_MBRHISTDETL.BILLMOYR, Max (MST1_CAV_MBRHISTDETL.PAYMENT),
FROM MST1_CAV_MBRHISTDETL
WHERE (((MST1_CAV_MBRHISTDETL.MBRSEP)="00001824004"));

Gives me an error when ran. "The Select statement includes a reserved word or argument that is mispelled or missing, or the punctuation is incorrect".
Obviously I tried it wrong lol

[pbaldy]

Well, that's not what I suggested, but the immediate error is probably due to the comma after the last field (before FROM). Why would you want the max on payment?

[Russellh]

Well, that's not what I suggested, but the immediate error is probably due to the comma after the last field (before FROM). Why would you want the max on payment?

Yea so I started from scratch and copied the example word for word. Then created 2nd query to give me the actual details. I haven't verified yet but it appears to have given me exactly what I needed.
Sometimes it pays not to rush it and just follow the directions!!

[Russellh]

Well actually it is giving me the same information for every mbrsep and I just broke the max size of a 2gb database. lol I'll try to tweak the joining of the tables.

[pbaldy]

If you get stuck, maybe you can post a sample db here with a few mbrsep's worth of data to play with?

[Russellh]

If you get stuck, maybe you can post a sample db here with a few mbrsep's worth of data to play with?

I got the join fixed. I didnt join it by both fields AS THE DIRECTIONS CLEARLY LAY OUT. Once I did it appears to give me exactly the data I needed.
Thanks for sticking it out with a noob!

[pbaldy]

LOL! Glad you got it working.

[Russellh]

Want to help with the 2nd part of the equation? I need to be able to query the service dates for the bill. So I can query 1509 for the billYYMM.readdate and it will give me the TO Date. How do I in the same query tell the query to subtracr 1 from the BillYYMM that I just entered and give me the read date for that field in that query?
So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE.
So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?

Any idea on how to begin that?

[pbaldy]

Sorry, maybe I'm having late-afternoon brain cramps, but I'm not following what your're trying to do. You want data from 2 different records?

[Russellh]

Sorry, maybe I'm having late-afternoon brain cramps, but I'm not following what your're trying to do. You want data from 2 different records?

The same table contains the billing detail for every bill. It designates them differently by BillMOYR. So the same Customer ID will have a record for every bill they have ever received. What I need is the field "READDATE" from the table, but I need it for what ever BILLMOYR that I type in and for the previous month (or BILLMOYR-1). So in design view I would select fields CUSTOMERID, BILLMOYR, READDATE (and other stuff). In the criteria section of BILLMOYR I will enter [Enter the Bill Month Year in the format YYMM]. That will set the query to be able to run for any month based on the users input. That part is easy. On the same query though I need to add a new field called "PREVREADDATE". I need that field to run a query to pull just the READDATE field but for BILLMOYR it needs to use what the user entered at the prompt -1. 1509 would be september 2015 and 1509-1 = 1508 or august 2015. I believe a subquery is how to do it. But I do not know how to set one up and I don't have a very clear understanding at SQL statements.
Does that clear it up any?

[Russellh]

On a side note: Do any of the moderators on here offer services to do the programming? At this point this needs to be completed and we don't mind paying to get it done right.