Finding the Next record

[rjbautista20]

Hi Guys,

hope you can help me again on my scenario:

How do i get the next data for specific condition.

here's my scenario: i have two tables:

Table1: RAW Data (which consist of Invoice Date) and Country
Table2: Lookup Data ( which consist of Country, Month and days)

Example: In my Raw data table i have US (country) and my Invoice Date is 1/1/2012, then in my Lookup Data i have two rows consist of the following.

Table 2:
US - 1-7
US - 1-22
US - 1 31

if invoice date is fall on 1/1/2012 up to 1/7/2012. payment date should be 1/7/2012


if invoice date is fall on 1/8/2012 up to 1/22/2012. payment date should be 1/22/2012
if invoice date is fall on 1/23/2012 up to 1/31/2012. payment date should be 1/31/2012


how do i get this result?

Hope you can help me on this.

[June7]

How is the Lookup table relevant? Are you saying the ranges will vary by country? Do you mean there are two fields (columns), not rows? An IIF or Switch Function needed.

Switch([Invoice Date] Between #1/1/2012# And #1/7/2012#, #1/7/2012#,
[Invoice Date] Between #1/8/2012# And #1/22/2012#, #1/22/2012#,
[Invoice Date] Between #1/23/2012# And #1/31/2012#, #1/31/2012#)

However, this hard codes the date values. Making it dynamic using input parameters (lookup to the LookupData table, is that the table name) and calculate due date will be trickier. The expression might get too long for a Control Source property and will need a custom function that can be called in the Control Source.

[rjbautista20]

@june7 thanks for your reply.

in the Source Data, the format are this:

Column1(Country)
Column2(Invoice Date)
Column3(Pay date) - this is were the result should put in

Country
US1
US1
US1

Columns2 (Invoice Date)
01/6/2011
01/20/2011
01/26/2011

how will the query/code would know the right paydate? the only given identical to the two tables are country and month. which shown below.

LOOKUP DATA

Column1 (Country)
US1
US1
US1

Column2 (Pay Date Month)
1
1
1

Column3 (pay date date)
7
22
27

if i use dlookup, the result will get the first row. which is not correct in this sense.

Here's what i did:
in the Source Data, I concatenate the Country(US1) and InvoiceDateMonth(1) and iif(Country = US1 and invoicedate<=2, "1st")

result is US111st

in the Lookup Data i added new Column where i input 1st 2nd and 3rd respectively.

result in Lookup data is US111st and so on.


my problem is, we have a lot of countries and some has 10 paydate and so on.


Please help... my programming still a bit low and having difficulty finding the right code function to do this.

Hope you all understand my explanation.


Thanks again for all the help

[June7]

Programming is not the issue, logic is. I still don't understand the table structure and criteria. Do you want to provide project for analysis?

Your sample lookup data shows 3 records for US month 1.
Invoice dates shown:
01/6/2011 - pay date would be 1/7/2011 because between 1st and 7th?
01/20/2011 - pay date would be 1/22/2011 because between 8th and 22nd?
01/26/2011 - pay date would be 1/31/2011 because between 23rd and 31st?

I presume there will also be records for months 2 through 12 for each country? The pay dates will change for each country and for each month?

[recyan]

just check out if below gives some guidelines :
The tables :
tblRawData
Country
InvoiceDate

tblLookUpData
Country
CountryMonth
CountryDays

The sub-queries :
qrytblRawData

SELECT 
	tblRawData.Country, 
	tblRawData.InvoiceDate, 
	Day([InvoiceDate]) AS DayInvoiceDate, 
	Month([InvoiceDate]) AS MonthInvoiceDate
FROM 
	tblRawData;

qryPayDayInter

SELECT 
	qrytblRawData.Country, 
	qrytblRawData.InvoiceDate, 
	qrytblRawData.MonthInvoiceDate, 
	qrytblRawData.DayInvoiceDate, 
	tblLookUpData.CountryMonth, 
	tblLookUpData.CountryDays, 
	Min(IIf([CountryDays]>=[DayInvoiceDate],[CountryDays],0)) AS DayGreaterThanEqualToInvoiceDay
FROM 
	qrytblRawData 
	INNER JOIN 
	tblLookUpData 
	ON 
	(qrytblRawData.MonthInvoiceDate = tblLookUpData.CountryMonth) 
	AND 
	(qrytblRawData.Country = tblLookUpData.Country)
GROUP BY 
	qrytblRawData.Country, 
	qrytblRawData.InvoiceDate, 
	qrytblRawData.MonthInvoiceDate, 
	qrytblRawData.DayInvoiceDate, 
	tblLookUpData.CountryMonth, 
	tblLookUpData.CountryDays
HAVING 
	(((Min(IIf([CountryDays]>=[DayInvoiceDate],[CountryDays],0)))>0));

qryPayDayFinal

SELECT 
	qryPayDayInter.Country, 
	qryPayDayInter.InvoiceDate, 
	Min(qryPayDayInter.DayGreaterThanEqualToInvoiceDay) AS MinOfDayGreaterThanEqualToInvoiceDay
FROM 
	qryPayDayInter
GROUP BY 
	qryPayDayInter.Country, 
	qryPayDayInter.InvoiceDate;

The final query :

qryPayDateFinal

SELECT 
	qryPayDayFinal.Country, 
	qryPayDayFinal.InvoiceDate, 
	qryPayDayFinal.MinOfDayGreaterThanEqualToInvoiceDay, DateSerial(Year([InvoiceDate]),Month([InvoiceDate]),[MinOfDayGreaterThanEqualToInvoiceDay]) AS PayDate
FROM 
	qryPayDayFinal;

Edit :

Was wondering if we had tables like below :
tblRawData
Country
InvoiceDate

tblLookUpDataCopy
Country
CountryMonth
CountryDaysStart
CountryDaysEnd

with data like say :

Country    CountryMonth    CountryDaysStart    CountryDaysEnd
US    1    1    7
US    1    7    22
US    1    22    31 

perhaps things could be simpler, something like below or even simpler :

pseudo-code

the sub-query :
qry1

SELECT 
tblRawData.Country, 
tblRawData.InvoiceDate, 
Month([InvoiceDate]) AS MonthInvoiceDate, 
Day([InvoiceDate]) AS DayInvoiceDate
FROM tblRawData;

the final query :

SELECT 
qry1.Country, 
qry1.InvoiceDate, 
qry1.MonthInvoiceDate, 
qry1.DayInvoiceDate, 
tblLookUpDataCopy.CountryDaysStart, 
tblLookUpDataCopy.CountryDaysEnd, 
IIf(([DayInvoiceDate]>=[CountryDaysStart]) And ([DayInvoiceDate]<[CountryDaysEnd]),[CountryDaysEnd],0) AS PaymentDay, 
DateSerial(Year([InvoiceDate]),Month([InvoiceDate]),[PaymentDay]) AS PaymentDate
FROM 
qry1 
INNER JOIN 
tblLookUpDataCopy 
ON 
(qry1.Country = tblLookUpDataCopy.Country) 
AND 
(qry1.MonthInvoiceDate = tblLookUpDataCopy.CountryMonth)
WHERE 
(((IIf(([DayInvoiceDate]>=[CountryDaysStart]) And ([DayInvoiceDate]<[CountryDaysEnd]),[CountryDaysEnd],0))>0));

Thanks

[rjbautista20]

@recyan thank you for your reply.

I will try this. Thanks. Will Give you feedback.

Thanks again.

[rjbautista20]

hi,

on qrytblrawdata: it work's
on qryPaydayInter: the problem here the records was reduced. tblrawdata should be intact.

another question: what if on the last query. let say the invoice date was 1/28/2011 and the condition is less than 27. the next pay date should be 2/7/2011 which is the first for the next month.

Thank you for your help again and please help me again on this.

God Bless to all of you guys

[recyan]

on qrytblrawdata: it work's
on qryPaydayInter: the problem here the records was reduced. tblrawdata should be intact.

Was just packing up for the day, when I saw your reply.
Am a bit confused.
If you can ( or have ) create the two tables with some dummy data:
tblRawData
tblLookUpData

just copy all the queries as it is, and run the final query.
qryPayDateFinal

Check the results of qryPayDateFinal.
If the results are not as per your requirement,
Post the tables with the dummy data & the results that the query qryPayDateFinal is giving & what is wrong with the results.

Perhaps some one in the meanwhile should be able to help you out.

another question: what if on the last query. let say the invoice date was 1/28/2011 and the condition is less than 27. the next pay date should be 2/7/2011 which is the first for the next month.

Right now not in a position to think about this, hence quoting this to enable others to look in to it.
Off the cuff, the logical last day conditions
for months 1; 3; 5; 7; 8; 10; 12 should be 31,
for months 4; 6; 9; 11 should be 30 and
for month 2 should be 28 or 29.

Thanks

[rjbautista20]

@recyan,

thanks again for your reply.

that's what i did..

the result should be in tblrawdata, which is the main table. if tblrawdata has 1,000 data, it mean paydate must have 1,000 output also.

hope you can still help me on this.

Thanks again.

[recyan]

the result should be in tblrawdata, which is the main table.

Are you indicating that you want the PaymentDate to be updated in the tblrawdata ?

if tblrawdata has 1,000 data, it mean paydate must have 1,000 output also.

I agree with above.

Quoting myself :

If the results are not as per your requirement,
Post the tables with the dummy data & the results that the query qryPayDateFinal is giving & what is wrong with the results.

Edit :
Quoting rjbautista20;99622 in post no 7

hi,
another question: what if on the last query. let say the invoice date was 1/28/2011 and the condition is less than 27. the next pay date should be 2/7/2011 which is the first for the next month.

Looked in to this now & I think, have understood what you want.
Yes, it can be handled in a different way, but not by the queries I have posted earlier.

Now that I am thinking, Is that the reason, you are telling, that the queries, which I have posted earlier, are not working (Is the query qryPayDateFinal giving less number of results as compared to the number of invoice dates).

Thanks

[rjbautista20]

@recyan,

it's working already. Thank you so much for your help. thank god that he let me find this forum.

Thank again.

[recyan]

Glad, you got things working.

Thanks

[rjbautista20]

Hi @recyan,

i have no more scenario:

I have vendor ID ranges to 3510000 to 3599999

in my query, i put in this in criteria. but i want to exclude the vendor id from 3530000 - 3550000? and some individual vendor also.

what i did is: Between 3510000 and 3599999 and not between 3530000 and 3550000

the problem, here all of the vendor ranges from 10111111 to 90000000 is visible in the query.


Thanks again for your help.

[recyan]

Hi,
Two suggestions :
1) Never ask a specific person for help in your post, when you have so many members who can help & perhaps / definitely give even better solutions than that person.
2) I think you should be starting a new thread for this.

Till then just as an example :
See if below gives some guidelines :
Assume you have
InvoiceID's from 1 to 20
and you want InvoiceIDs
from 5 to 18
but
exclude
from
10 to 15

then

SELECT 
	tblInvoiceID.InvoiceID
FROM 
	tblInvoiceID 
	LEFT JOIN 
	(
		SELECT 
			tblInvoiceID.InvoiceID
		FROM 
			tblInvoiceID
		WHERE 
			tblInvoiceID.InvoiceID 
			BETWEEN 
			10 AND 15
	)
	AS qryExclude 
	ON 
	tblInvoiceID.[InvoiceID] = qryExclude.[InvoiceID]
WHERE 
	(
		((tblInvoiceID.InvoiceID)>=5 
		And 
		(tblInvoiceID.InvoiceID)<=18) AND ((qryExclude.InvoiceID) Is Null)
	);

Thanks

[rjbautista20]

@recyan,

sorry for that. ok i will..

thanks again.