Comparing two fields in two different records

A

AccessIM

Is it possible to compare a field in a record to a different field in the
previous record?

For example, I would like to create a field that compares the RcvdDate field
in record 2 to the LeadTimeEndDate in record 1, the RcvdDate field in record
3 to the LeadTimeEndDate in record 2 and so on. If the received date is
before the previous record’s LeadTimeEndDate, I would like it to say
“Received Within Previous Receiving’s Lead Timeâ€. If not, it should say “Not
Received Within Previous Receiving’s Lead Timeâ€.

Below is a sample of the data. LeadTimeEndDate is a calculated field that
add the number of days in the Vendor_Lead_Time field to the RcvdDate. The
Type field would be the field I want to show the results of the comparison.

Item Code Description PO# RcvdDate Vendor_Lead_Time LeadTime EndDate Type
8856813 DEEP MSTR BDY LTN 420842 5/27/2009 7 6/3/2009
8856813 DEEP MSTR BDY LTN 422875 6/4/2009 7 6/11/2009 Not
Received Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423943 6/10/2009 7 6/17/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423947 6/16/2009 7 6/23/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 424934 6/22/2009 7 6/29/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426064 6/29/2009 7 7/6/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426066 7/1/2009 7 7/8/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 427135 7/2/2009 7 7/9/2009 Received
Within Previous Receiving’s Lead Time

Thank you in advance.
 
K

KARL DEWEY

Access has to be told what the 'previous record' is. Best way is to add an
autonumber.

This is how it is done but I did not have time to figure which gets added to
the Vendor_Lead_Time.
SELECT Some_Table.[Item Code], Some_Table.Description, Some_Table.[PO#],
Some_Table.RcvdDate, Some_Table.Vendor_Lead_Time, Some_Table.[LeadTime
EndDate],
IIf([Some_Table].[RcvdDate]+[Some_Table].[Vendor_Lead_Time]<[Some_Table_1].[LeadTime
EndDate],"Received Within Previous Receiving’s Lead Time","Not Received
Within Previous Receiving’s Lead Time") AS Type
FROM Some_Table, Some_Table AS Some_Table_1
WHERE (((Some_Table_1.Auto)=[Some_Table].[Auto]-1));
 
A

AccessIM

Thank you so much for your reply. Unfortunately, I was pulled away to
another project and am just getting back to this one so I apologize for the
delay.

I think I am closer with the code you gave below ut still have a few
questions.

Below is the code I wrote based on your sample:

SELECT [tblReceivings].[ItemCode], [tblReceivings].[Description],
[tblReceivings].[PO#], [tblReceivings].[AutoNumber],
[tblReceivings].[RcvdDate], [tblReceivings].[Vendor_Lead_Time],
[tblReceivings].[LeadTimeEndDate],
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE ([tblReceivings_1.AutoNumber])=([tblReceivings.AutoNumber]-1);

The results appear close to what I am looking for but it looks like every
record is comparing RcvdDate to the LeadTimeEndDate of the previous record
even if the previous record is for a different item code. Here is the result
I am currently seeing:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 Received Within...
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 Recieved Within...
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Received Within...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 Recieved Within...

Here is what I would like to see for the same example data above:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 First Receiving
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 First Receiving
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Not Received...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 First Receiving

Thank you so much for your time.

KARL DEWEY said:
Access has to be told what the 'previous record' is. Best way is to add an
autonumber.

This is how it is done but I did not have time to figure which gets added to
the Vendor_Lead_Time.
SELECT Some_Table.[Item Code], Some_Table.Description, Some_Table.[PO#],
Some_Table.RcvdDate, Some_Table.Vendor_Lead_Time, Some_Table.[LeadTime
EndDate],
IIf([Some_Table].[RcvdDate]+[Some_Table].[Vendor_Lead_Time]<[Some_Table_1].[LeadTime
EndDate],"Received Within Previous Receiving’s Lead Time","Not Received
Within Previous Receiving’s Lead Time") AS Type
FROM Some_Table, Some_Table AS Some_Table_1
WHERE (((Some_Table_1.Auto)=[Some_Table].[Auto]-1));

--
Build a little, test a little.


AccessIM said:
Is it possible to compare a field in a record to a different field in the
previous record?

For example, I would like to create a field that compares the RcvdDate field
in record 2 to the LeadTimeEndDate in record 1, the RcvdDate field in record
3 to the LeadTimeEndDate in record 2 and so on. If the received date is
before the previous record’s LeadTimeEndDate, I would like it to say
“Received Within Previous Receiving’s Lead Timeâ€. If not, it should say “Not
Received Within Previous Receiving’s Lead Timeâ€.

Below is a sample of the data. LeadTimeEndDate is a calculated field that
add the number of days in the Vendor_Lead_Time field to the RcvdDate. The
Type field would be the field I want to show the results of the comparison.

Item Code Description PO# RcvdDate Vendor_Lead_Time LeadTime EndDate Type
8856813 DEEP MSTR BDY LTN 420842 5/27/2009 7 6/3/2009
8856813 DEEP MSTR BDY LTN 422875 6/4/2009 7 6/11/2009 Not
Received Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423943 6/10/2009 7 6/17/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423947 6/16/2009 7 6/23/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 424934 6/22/2009 7 6/29/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426064 6/29/2009 7 7/6/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426066 7/1/2009 7 7/8/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 427135 7/2/2009 7 7/9/2009 Received
Within Previous Receiving’s Lead Time

Thank you in advance.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
WHERE ([tblReceivings_1].[AutoNumber])=([tblReceivings].[AutoNumber]-1) AND
([tblReceivings_1].[ItemCode])=([tblReceivings].[ItemCode]);


--
Build a little, test a little.


AccessIM said:
Thank you so much for your reply. Unfortunately, I was pulled away to
another project and am just getting back to this one so I apologize for the
delay.

I think I am closer with the code you gave below ut still have a few
questions.

Below is the code I wrote based on your sample:

SELECT [tblReceivings].[ItemCode], [tblReceivings].[Description],
[tblReceivings].[PO#], [tblReceivings].[AutoNumber],
[tblReceivings].[RcvdDate], [tblReceivings].[Vendor_Lead_Time],
[tblReceivings].[LeadTimeEndDate],
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE ([tblReceivings_1.AutoNumber])=([tblReceivings.AutoNumber]-1);

The results appear close to what I am looking for but it looks like every
record is comparing RcvdDate to the LeadTimeEndDate of the previous record
even if the previous record is for a different item code. Here is the result
I am currently seeing:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 Received Within...
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 Recieved Within...
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Received Within...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 Recieved Within...

Here is what I would like to see for the same example data above:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 First Receiving
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 First Receiving
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Not Received...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 First Receiving

Thank you so much for your time.

KARL DEWEY said:
Access has to be told what the 'previous record' is. Best way is to add an
autonumber.

This is how it is done but I did not have time to figure which gets added to
the Vendor_Lead_Time.
SELECT Some_Table.[Item Code], Some_Table.Description, Some_Table.[PO#],
Some_Table.RcvdDate, Some_Table.Vendor_Lead_Time, Some_Table.[LeadTime
EndDate],
IIf([Some_Table].[RcvdDate]+[Some_Table].[Vendor_Lead_Time]<[Some_Table_1].[LeadTime
EndDate],"Received Within Previous Receiving’s Lead Time","Not Received
Within Previous Receiving’s Lead Time") AS Type
FROM Some_Table, Some_Table AS Some_Table_1
WHERE (((Some_Table_1.Auto)=[Some_Table].[Auto]-1));

--
Build a little, test a little.


AccessIM said:
Is it possible to compare a field in a record to a different field in the
previous record?

For example, I would like to create a field that compares the RcvdDate field
in record 2 to the LeadTimeEndDate in record 1, the RcvdDate field in record
3 to the LeadTimeEndDate in record 2 and so on. If the received date is
before the previous record’s LeadTimeEndDate, I would like it to say
“Received Within Previous Receiving’s Lead Timeâ€. If not, it should say “Not
Received Within Previous Receiving’s Lead Timeâ€.

Below is a sample of the data. LeadTimeEndDate is a calculated field that
add the number of days in the Vendor_Lead_Time field to the RcvdDate. The
Type field would be the field I want to show the results of the comparison.

Item Code Description PO# RcvdDate Vendor_Lead_Time LeadTime EndDate Type
8856813 DEEP MSTR BDY LTN 420842 5/27/2009 7 6/3/2009
8856813 DEEP MSTR BDY LTN 422875 6/4/2009 7 6/11/2009 Not
Received Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423943 6/10/2009 7 6/17/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423947 6/16/2009 7 6/23/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 424934 6/22/2009 7 6/29/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426064 6/29/2009 7 7/6/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426066 7/1/2009 7 7/8/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 427135 7/2/2009 7 7/9/2009 Received
Within Previous Receiving’s Lead Time

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
A

AccessIM

Hi. No change with the suggestion below. It looks like every record returns
"Received Within Previous Receiving's Lead Time" for the Type field except
when the [Vendor_Lead_Time] is null or 0, then it shows "Not Received Within
Previous Receiving's Lead Time".

A sample:

Vendor Lead Time
Item Description PO# AutoNumber RcvdDate Lead Time
End Date Type
8545765 Berry/Orange 431888 905 8/11/09 14
8/25/09 Received Within..
8545765 Berry/Orange 435163 906 9/1/09 14
9/15/09 Received Within..

The second record/instance of item 8545765 that was received on 9/1/09
should say "Not Received"... as the type because it was received after the
previous record's lead time end date.

Here is the code I have right now:

SELECT tblReceivings.ItemCode, tblReceivings.Description,
tblReceivings.[PO#], tblReceivings.AutoNumber, tblReceivings.RcvdDate,
tblReceivings.Vendor_Lead_Time, tblReceivings.LeadTimeEndDate,
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received Within
Previous Receiving's Lead Time","Not Received Within Previous Receiving's
Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE (((tblReceivings_1.AutoNumber)=([tblReceivings].[AutoNumber]-1)) AND
((tblReceivings_1.ItemCode)=([tblReceivings].[ItemCode])));

This is turning out to be quite tricky. Thank you so much for your help.
Any additional suggestions would be appreciated.

KARL DEWEY said:
Try this --
WHERE ([tblReceivings_1].[AutoNumber])=([tblReceivings].[AutoNumber]-1) AND
([tblReceivings_1].[ItemCode])=([tblReceivings].[ItemCode]);


--
Build a little, test a little.


AccessIM said:
Thank you so much for your reply. Unfortunately, I was pulled away to
another project and am just getting back to this one so I apologize for the
delay.

I think I am closer with the code you gave below ut still have a few
questions.

Below is the code I wrote based on your sample:

SELECT [tblReceivings].[ItemCode], [tblReceivings].[Description],
[tblReceivings].[PO#], [tblReceivings].[AutoNumber],
[tblReceivings].[RcvdDate], [tblReceivings].[Vendor_Lead_Time],
[tblReceivings].[LeadTimeEndDate],
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE ([tblReceivings_1.AutoNumber])=([tblReceivings.AutoNumber]-1);

The results appear close to what I am looking for but it looks like every
record is comparing RcvdDate to the LeadTimeEndDate of the previous record
even if the previous record is for a different item code. Here is the result
I am currently seeing:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 Received Within...
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 Recieved Within...
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Received Within...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 Recieved Within...

Here is what I would like to see for the same example data above:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 First Receiving
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 First Receiving
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Not Received...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 First Receiving

Thank you so much for your time.

KARL DEWEY said:
Access has to be told what the 'previous record' is. Best way is to add an
autonumber.

This is how it is done but I did not have time to figure which gets added to
the Vendor_Lead_Time.
SELECT Some_Table.[Item Code], Some_Table.Description, Some_Table.[PO#],
Some_Table.RcvdDate, Some_Table.Vendor_Lead_Time, Some_Table.[LeadTime
EndDate],
IIf([Some_Table].[RcvdDate]+[Some_Table].[Vendor_Lead_Time]<[Some_Table_1].[LeadTime
EndDate],"Received Within Previous Receiving’s Lead Time","Not Received
Within Previous Receiving’s Lead Time") AS Type
FROM Some_Table, Some_Table AS Some_Table_1
WHERE (((Some_Table_1.Auto)=[Some_Table].[Auto]-1));

--
Build a little, test a little.


:

Is it possible to compare a field in a record to a different field in the
previous record?

For example, I would like to create a field that compares the RcvdDate field
in record 2 to the LeadTimeEndDate in record 1, the RcvdDate field in record
3 to the LeadTimeEndDate in record 2 and so on. If the received date is
before the previous record’s LeadTimeEndDate, I would like it to say
“Received Within Previous Receiving’s Lead Timeâ€. If not, it should say “Not
Received Within Previous Receiving’s Lead Timeâ€.

Below is a sample of the data. LeadTimeEndDate is a calculated field that
add the number of days in the Vendor_Lead_Time field to the RcvdDate. The
Type field would be the field I want to show the results of the comparison.

Item Code Description PO# RcvdDate Vendor_Lead_Time LeadTime EndDate Type
8856813 DEEP MSTR BDY LTN 420842 5/27/2009 7 6/3/2009
8856813 DEEP MSTR BDY LTN 422875 6/4/2009 7 6/11/2009 Not
Received Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423943 6/10/2009 7 6/17/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423947 6/16/2009 7 6/23/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 424934 6/22/2009 7 6/29/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426064 6/29/2009 7 7/6/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426066 7/1/2009 7 7/8/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 427135 7/2/2009 7 7/9/2009 Received
Within Previous Receiving’s Lead Time

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

It was comparing date within same record. Try this --
SELECT tblReceivings.ItemCode, tblReceivings.Description,
tblReceivings.[PO#], tblReceivings.AutoNumber, tblReceivings.RcvdDate,
tblReceivings.Vendor_Lead_Time, tblReceivings.LeadTimeEndDate,
IIf([tblReceivings.RcvdDate]>[tblReceivings_1.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE (((tblReceivings_1.AutoNumber)=([tblReceivings].[AutoNumber]-1)) AND
((tblReceivings_1.ItemCode)=([tblReceivings].[ItemCode])))
ORDER BY [tblReceivings].[AutoNumber];

You might consider changing the --
([tblReceivings.RcvdDate]>[tblReceivings_1.LeadTimeEndDate],
to
([tblReceivings.RcvdDate]>=[tblReceivings_1.LeadTimeEndDate],
so that 'Received Within Previous...' if same date.

--
Build a little, test a little.


AccessIM said:
Hi. No change with the suggestion below. It looks like every record returns
"Received Within Previous Receiving's Lead Time" for the Type field except
when the [Vendor_Lead_Time] is null or 0, then it shows "Not Received Within
Previous Receiving's Lead Time".

A sample:

Vendor Lead Time
Item Description PO# AutoNumber RcvdDate Lead Time
End Date Type
8545765 Berry/Orange 431888 905 8/11/09 14
8/25/09 Received Within..
8545765 Berry/Orange 435163 906 9/1/09 14
9/15/09 Received Within..

The second record/instance of item 8545765 that was received on 9/1/09
should say "Not Received"... as the type because it was received after the
previous record's lead time end date.

Here is the code I have right now:

SELECT tblReceivings.ItemCode, tblReceivings.Description,
tblReceivings.[PO#], tblReceivings.AutoNumber, tblReceivings.RcvdDate,
tblReceivings.Vendor_Lead_Time, tblReceivings.LeadTimeEndDate,
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received Within
Previous Receiving's Lead Time","Not Received Within Previous Receiving's
Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE (((tblReceivings_1.AutoNumber)=([tblReceivings].[AutoNumber]-1)) AND
((tblReceivings_1.ItemCode)=([tblReceivings].[ItemCode])));

This is turning out to be quite tricky. Thank you so much for your help.
Any additional suggestions would be appreciated.

KARL DEWEY said:
Try this --
WHERE ([tblReceivings_1].[AutoNumber])=([tblReceivings].[AutoNumber]-1) AND
([tblReceivings_1].[ItemCode])=([tblReceivings].[ItemCode]);


--
Build a little, test a little.


AccessIM said:
Thank you so much for your reply. Unfortunately, I was pulled away to
another project and am just getting back to this one so I apologize for the
delay.

I think I am closer with the code you gave below ut still have a few
questions.

Below is the code I wrote based on your sample:

SELECT [tblReceivings].[ItemCode], [tblReceivings].[Description],
[tblReceivings].[PO#], [tblReceivings].[AutoNumber],
[tblReceivings].[RcvdDate], [tblReceivings].[Vendor_Lead_Time],
[tblReceivings].[LeadTimeEndDate],
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE ([tblReceivings_1.AutoNumber])=([tblReceivings.AutoNumber]-1);

The results appear close to what I am looking for but it looks like every
record is comparing RcvdDate to the LeadTimeEndDate of the previous record
even if the previous record is for a different item code. Here is the result
I am currently seeing:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 Received Within...
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 Recieved Within...
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Received Within...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 Recieved Within...

Here is what I would like to see for the same example data above:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 First Receiving
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 First Receiving
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Not Received...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 First Receiving

Thank you so much for your time.

:

Access has to be told what the 'previous record' is. Best way is to add an
autonumber.

This is how it is done but I did not have time to figure which gets added to
the Vendor_Lead_Time.
SELECT Some_Table.[Item Code], Some_Table.Description, Some_Table.[PO#],
Some_Table.RcvdDate, Some_Table.Vendor_Lead_Time, Some_Table.[LeadTime
EndDate],
IIf([Some_Table].[RcvdDate]+[Some_Table].[Vendor_Lead_Time]<[Some_Table_1].[LeadTime
EndDate],"Received Within Previous Receiving’s Lead Time","Not Received
Within Previous Receiving’s Lead Time") AS Type
FROM Some_Table, Some_Table AS Some_Table_1
WHERE (((Some_Table_1.Auto)=[Some_Table].[Auto]-1));

--
Build a little, test a little.


:

Is it possible to compare a field in a record to a different field in the
previous record?

For example, I would like to create a field that compares the RcvdDate field
in record 2 to the LeadTimeEndDate in record 1, the RcvdDate field in record
3 to the LeadTimeEndDate in record 2 and so on. If the received date is
before the previous record’s LeadTimeEndDate, I would like it to say
“Received Within Previous Receiving’s Lead Timeâ€. If not, it should say “Not
Received Within Previous Receiving’s Lead Timeâ€.

Below is a sample of the data. LeadTimeEndDate is a calculated field that
add the number of days in the Vendor_Lead_Time field to the RcvdDate. The
Type field would be the field I want to show the results of the comparison.

Item Code Description PO# RcvdDate Vendor_Lead_Time LeadTime EndDate Type
8856813 DEEP MSTR BDY LTN 420842 5/27/2009 7 6/3/2009
8856813 DEEP MSTR BDY LTN 422875 6/4/2009 7 6/11/2009 Not
Received Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423943 6/10/2009 7 6/17/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423947 6/16/2009 7 6/23/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 424934 6/22/2009 7 6/29/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426064 6/29/2009 7 7/6/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426066 7/1/2009 7 7/8/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 427135 7/2/2009 7 7/9/2009 Received
Within Previous Receiving’s Lead Time

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
A

AccessIM

YEAH! That was it! It works and looks great! I also used your suggestion
and added an = sign in the IIf statement.

Thank you so much for your help. I don't know what I would do without these
discussion threads. They have really been a real stress reducer! :)

KARL DEWEY said:
It was comparing date within same record. Try this --
SELECT tblReceivings.ItemCode, tblReceivings.Description,
tblReceivings.[PO#], tblReceivings.AutoNumber, tblReceivings.RcvdDate,
tblReceivings.Vendor_Lead_Time, tblReceivings.LeadTimeEndDate,
IIf([tblReceivings.RcvdDate]>[tblReceivings_1.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE (((tblReceivings_1.AutoNumber)=([tblReceivings].[AutoNumber]-1)) AND
((tblReceivings_1.ItemCode)=([tblReceivings].[ItemCode])))
ORDER BY [tblReceivings].[AutoNumber];

You might consider changing the --
([tblReceivings.RcvdDate]>[tblReceivings_1.LeadTimeEndDate],
to
([tblReceivings.RcvdDate]>=[tblReceivings_1.LeadTimeEndDate],
so that 'Received Within Previous...' if same date.

--
Build a little, test a little.


AccessIM said:
Hi. No change with the suggestion below. It looks like every record returns
"Received Within Previous Receiving's Lead Time" for the Type field except
when the [Vendor_Lead_Time] is null or 0, then it shows "Not Received Within
Previous Receiving's Lead Time".

A sample:

Vendor Lead Time
Item Description PO# AutoNumber RcvdDate Lead Time
End Date Type
8545765 Berry/Orange 431888 905 8/11/09 14
8/25/09 Received Within..
8545765 Berry/Orange 435163 906 9/1/09 14
9/15/09 Received Within..

The second record/instance of item 8545765 that was received on 9/1/09
should say "Not Received"... as the type because it was received after the
previous record's lead time end date.

Here is the code I have right now:

SELECT tblReceivings.ItemCode, tblReceivings.Description,
tblReceivings.[PO#], tblReceivings.AutoNumber, tblReceivings.RcvdDate,
tblReceivings.Vendor_Lead_Time, tblReceivings.LeadTimeEndDate,
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received Within
Previous Receiving's Lead Time","Not Received Within Previous Receiving's
Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE (((tblReceivings_1.AutoNumber)=([tblReceivings].[AutoNumber]-1)) AND
((tblReceivings_1.ItemCode)=([tblReceivings].[ItemCode])));

This is turning out to be quite tricky. Thank you so much for your help.
Any additional suggestions would be appreciated.

KARL DEWEY said:
Try this --
WHERE ([tblReceivings_1].[AutoNumber])=([tblReceivings].[AutoNumber]-1) AND
([tblReceivings_1].[ItemCode])=([tblReceivings].[ItemCode]);


--
Build a little, test a little.


:

Thank you so much for your reply. Unfortunately, I was pulled away to
another project and am just getting back to this one so I apologize for the
delay.

I think I am closer with the code you gave below ut still have a few
questions.

Below is the code I wrote based on your sample:

SELECT [tblReceivings].[ItemCode], [tblReceivings].[Description],
[tblReceivings].[PO#], [tblReceivings].[AutoNumber],
[tblReceivings].[RcvdDate], [tblReceivings].[Vendor_Lead_Time],
[tblReceivings].[LeadTimeEndDate],
IIf([tblReceivings.RcvdDate]<[tblReceivings.LeadTimeEndDate],"Received
Within Previous Receiving's Lead Time","Not Received Within Previous
Receiving's Lead Time") AS Type
FROM tblReceivings, tblReceivings AS tblReceivings_1
WHERE ([tblReceivings_1.AutoNumber])=([tblReceivings.AutoNumber]-1);

The results appear close to what I am looking for but it looks like every
record is comparing RcvdDate to the LeadTimeEndDate of the previous record
even if the previous record is for a different item code. Here is the result
I am currently seeing:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 Received Within...
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 Recieved Within...
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Received Within...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 Recieved Within...

Here is what I would like to see for the same example data above:

Vendor LeadTime
Item Description PO# Auto# RcvdDate LeadTime
EndDate Type
8544797 Floor Shine 436251 871 9/15/09 7
9/22/09 First Receiving
8545244 Liquid-Caps 434669 872 9/7/09 35
10/12/09 First Receiving
8545244 Liquid-Caps 435770 873 9/14/09 35
10/19/09 Received Within...
8545244 Liquid-Caps 426985 874 10/20/09 35
11/24/09 Not Received...
8545246 Gel Caps 429360 875 8/3/09 35
9/7/09 First Receiving

Thank you so much for your time.

:

Access has to be told what the 'previous record' is. Best way is to add an
autonumber.

This is how it is done but I did not have time to figure which gets added to
the Vendor_Lead_Time.
SELECT Some_Table.[Item Code], Some_Table.Description, Some_Table.[PO#],
Some_Table.RcvdDate, Some_Table.Vendor_Lead_Time, Some_Table.[LeadTime
EndDate],
IIf([Some_Table].[RcvdDate]+[Some_Table].[Vendor_Lead_Time]<[Some_Table_1].[LeadTime
EndDate],"Received Within Previous Receiving’s Lead Time","Not Received
Within Previous Receiving’s Lead Time") AS Type
FROM Some_Table, Some_Table AS Some_Table_1
WHERE (((Some_Table_1.Auto)=[Some_Table].[Auto]-1));

--
Build a little, test a little.


:

Is it possible to compare a field in a record to a different field in the
previous record?

For example, I would like to create a field that compares the RcvdDate field
in record 2 to the LeadTimeEndDate in record 1, the RcvdDate field in record
3 to the LeadTimeEndDate in record 2 and so on. If the received date is
before the previous record’s LeadTimeEndDate, I would like it to say
“Received Within Previous Receiving’s Lead Timeâ€. If not, it should say “Not
Received Within Previous Receiving’s Lead Timeâ€.

Below is a sample of the data. LeadTimeEndDate is a calculated field that
add the number of days in the Vendor_Lead_Time field to the RcvdDate. The
Type field would be the field I want to show the results of the comparison.

Item Code Description PO# RcvdDate Vendor_Lead_Time LeadTime EndDate Type
8856813 DEEP MSTR BDY LTN 420842 5/27/2009 7 6/3/2009
8856813 DEEP MSTR BDY LTN 422875 6/4/2009 7 6/11/2009 Not
Received Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423943 6/10/2009 7 6/17/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 423947 6/16/2009 7 6/23/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 424934 6/22/2009 7 6/29/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426064 6/29/2009 7 7/6/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 426066 7/1/2009 7 7/8/2009 Received
Within Previous Receiving’s Lead Time
8856813 DEEP MSTR BDY LTN 427135 7/2/2009 7 7/9/2009 Received
Within Previous Receiving’s Lead Time

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

changing data only after a certain date 1
Please help. Question about querying a subform 4
Replace or insert characters in text string 6
How to validate data for new and past records being entered 2
Create new table from two tables 0
Dates don’t work within WHERE clauses 2
Comparing Two Adjacement Records 1
Linking Records between two tables using queries 3

Top