Comparing two fields in two different records

  • Thread starter Thread starter AccessIM
  • Start date Start date
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.
 
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));
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top