Comparing data from 2 lines in a query

  • Thread starter Thread starter clueless
  • Start date Start date
C

clueless

Hi,
I have spent a couple of weeks with this reading and trying and I am still
nowhere. I have 1 table tbl_Inbound/Outbound. I will be importing info from
an inbound sheet & an outbound sheet from Excel. This will serve as a yard
check on freight trailers. If a trailer comes in the yard, the status would
be open. When the trailer leaves the yard, the status would change to
closed. With it being in 1 table the inbound trailer is on 1 line and the
outbound trailer is on another line. This is where the water gets murky for
me. (I also tried with 2 tables and that didn't work either.) How do I
connect the 2 lines to close the trailer out? The second part is that the
same trailer can re-enter the yard again; the only difference would be the
arrival would change. Is this doable? Is the setup correct? Am I trying for
the impossible? I am using Access 2007. I have included what I have so far.

Fields
Transaction (primary key)
CarrierName
Trailer#
ArrivalDate
ArrivalTime
DepartureDate
DepartureTime
DriverName
Tractor#
OrderID
Outbound
Inbound

SELECT [tbl_Inbound/Outbound].CarrierName,
[tbl_Inbound/Outbound].[Trailer#], [tbl_Inbound/Outbound].[Tractor#],
IIf([ArrivalDate] And [ArrivalTime] Is Not
Null,DateValue([ArrivalDate])+TimeValue([ArrivalTime]),Null) AS Arrival,
[tbl_Inbound/Outbound].ArrivalDate, [tbl_Inbound/Outbound].ArrivalTime,
IIf([DepartureDate] And [DepartureTime] Is Not
Null,DateValue([DepartureDate])+TimeValue([DepartureTime]),Null) AS
Departure, [tbl_Inbound/Outbound].DepartureDate,
[tbl_Inbound/Outbound].DepartureTime, [tbl_Inbound/Outbound].OrderID,
IIf([OrderID] Is Not Null And [Inbound]="yes","IB/Loaded",IIf([OrderID] Is
Not Null And [Outbound]="yes","OB/Loaded","Empty")) AS TrailerStatus,
Diff2Dates("dh",[Arrival],Now()) AS DwellTime,
[tbl_Inbound/Outbound].Outbound, [tbl_Inbound/Outbound].Inbound
FROM [tbl_Inbound/Outbound];
 
Show us sample data that would be in the table and show us how the desired
query would display the results that you want from those data. Are you
storing everything in one ACCESS table? Your description appears to conflict
with respect to whether you're using 1 table or 2 tables.
 
Hi,

To answer your question. Yes, it is doable and pretty easily. Let me
recap what you wrote; you can indicate if I understood. You have as a data
source a spreadsheet with the mentioned fields. You are taking that data and
I am guessing planning to insert it into another table? And/or update a
status field from open to closed in that other table when the trailer has
left. And that other table may not look quite the same as the source table?
If so, please describe what the data looks like in the incoming spreadsheet.
Things like arrival date and time; when will there be a vaue, when will one
or both be null? Likewise for departure date and time. What is in Inbound
and what is in Outbound? Are either of those null? And please describe your
destination table.

Clifford Bass

clueless said:
Hi,
I have spent a couple of weeks with this reading and trying and I am still
nowhere. I have 1 table tbl_Inbound/Outbound. I will be importing info from
an inbound sheet & an outbound sheet from Excel. This will serve as a yard
check on freight trailers. If a trailer comes in the yard, the status would
be open. When the trailer leaves the yard, the status would change to
closed. With it being in 1 table the inbound trailer is on 1 line and the
outbound trailer is on another line. This is where the water gets murky for
me. (I also tried with 2 tables and that didn't work either.) How do I
connect the 2 lines to close the trailer out? The second part is that the
same trailer can re-enter the yard again; the only difference would be the
arrival would change. Is this doable? Is the setup correct? Am I trying for
the impossible? I am using Access 2007. I have included what I have so far.

Fields
Transaction (primary key)
CarrierName
Trailer#
ArrivalDate
ArrivalTime
DepartureDate
DepartureTime
DriverName
Tractor#
OrderID
Outbound
Inbound

SELECT [tbl_Inbound/Outbound].CarrierName,
[tbl_Inbound/Outbound].[Trailer#], [tbl_Inbound/Outbound].[Tractor#],
IIf([ArrivalDate] And [ArrivalTime] Is Not
Null,DateValue([ArrivalDate])+TimeValue([ArrivalTime]),Null) AS Arrival,
[tbl_Inbound/Outbound].ArrivalDate, [tbl_Inbound/Outbound].ArrivalTime,
IIf([DepartureDate] And [DepartureTime] Is Not
Null,DateValue([DepartureDate])+TimeValue([DepartureTime]),Null) AS
Departure, [tbl_Inbound/Outbound].DepartureDate,
[tbl_Inbound/Outbound].DepartureTime, [tbl_Inbound/Outbound].OrderID,
IIf([OrderID] Is Not Null And [Inbound]="yes","IB/Loaded",IIf([OrderID] Is
Not Null And [Outbound]="yes","OB/Loaded","Empty")) AS TrailerStatus,
Diff2Dates("dh",[Arrival],Now()) AS DwellTime,
[tbl_Inbound/Outbound].Outbound, [tbl_Inbound/Outbound].Inbound
FROM [tbl_Inbound/Outbound];
Click to expand...
 
Thank you Ken and Clifford for responding. I was planning on importing the
data from Excel, both inbound and outbound, into my table
tbl_Inbound/Outbound. Right now I only have 1 table (I did try it with 2 and
I had duplicates and a big mess). The data sheets would look something like
below:

Inbound Sheet
CarrierName Trailer# Tractor# ArrivalDate ArrivalTime DriverName
OrderIDInbound
JBHunt 123 ABC 1/1/2009 13:00 Joe
999 yes
Schneider 456 DEF 1/1/2009 10:00 John
888 yes
Crete 789 GHI 1/1/2009 17:00 Paul
(Null) yes
JBHunt 123 444 1/3/2009 07:00 Carl
(Null) Yes

Outbound Sheet
CarrierName Trailer# Tractor# DepartureDate DepartureTime DriverName OrderID
JBHunt 123 JKL 1/2/2009 13:00
Mary
Crete 789 MNO 1/2/2009 18:00 Ed
111

(Last Column) Outbound
yes
yes


Results
CarrierName Trailer# Tractor# ArrivalDate ArrivalTime Arrival
1.JBHunt 123 ABC 1/1/2009 13:00 1/1/2009
13:00
2. Schneider 456 LNY 1/1/2009 10:00 1/1/2009
10:00
3. Crete 789 91A 1/1/2009 17:00
1/1/2009 17:00
4. JBHunt 123 JKL
5. Crete 789 ZZZ
6. JBHunt 123 444 1/3/2009 07:00
1/3/2009 07:00
DepartureDate DepartureTime Departure OrderID TrailerStatus DwellTime
Status
1. (Null) (Null) (Null) 999
I/BLoaded Diff2Dates Closed
2. (Null) (Null) (Null) 888
I/BLoaded Open
3. (Null) (Null) (Null) (Null) Empty
Closed
4. 1/2/2009 13:00 1/2/2009 13:00 (Null) I/BLoaded 1
day Closed
5. 1/2/2009 18:00 1/2/2009 18:00 111 O/BLoaded 1 day
1hr Closed
6. (Null) (Null) (Null) (Null) Empty
Open
Outbound Inbound
1. (Null) yes
2. (Null) yes
3. (Null) yes
4. yes (Null)
5. yes (Null)
6. (Null) yes

Sorry, space is tight. Any suggestions to make it more efficient, would be
greatly appreciated. I have not started collecting data yet, so anything can
be changed. I really just need suggestions on how to make this work from
some experts. My destination table was described in my first post under
fields but I can change whatever needs to be changed. Hope I didn't confuse
you, hope I answered all your questions. Glad to hear that it is doable,
can't wait to see how it's done. Thanks!
-
-
clueless


Clifford Bass said:
Hi,

To answer your question. Yes, it is doable and pretty easily. Let me
recap what you wrote; you can indicate if I understood. You have as a data
source a spreadsheet with the mentioned fields. You are taking that data and
I am guessing planning to insert it into another table? And/or update a
status field from open to closed in that other table when the trailer has
left. And that other table may not look quite the same as the source table?
If so, please describe what the data looks like in the incoming spreadsheet.
Things like arrival date and time; when will there be a vaue, when will one
or both be null? Likewise for departure date and time. What is in Inbound
and what is in Outbound? Are either of those null? And please describe your
destination table.

Clifford Bass

clueless said:
Hi,
I have spent a couple of weeks with this reading and trying and I am still
nowhere. I have 1 table tbl_Inbound/Outbound. I will be importing info from
an inbound sheet & an outbound sheet from Excel. This will serve as a yard
check on freight trailers. If a trailer comes in the yard, the status would
be open. When the trailer leaves the yard, the status would change to
closed. With it being in 1 table the inbound trailer is on 1 line and the
outbound trailer is on another line. This is where the water gets murky for
me. (I also tried with 2 tables and that didn't work either.) How do I
connect the 2 lines to close the trailer out? The second part is that the
same trailer can re-enter the yard again; the only difference would be the
arrival would change. Is this doable? Is the setup correct? Am I trying for
the impossible? I am using Access 2007. I have included what I have so far.

Fields
Transaction (primary key)
CarrierName
Trailer#
ArrivalDate
ArrivalTime
DepartureDate
DepartureTime
DriverName
Tractor#
OrderID
Outbound
Inbound

SELECT [tbl_Inbound/Outbound].CarrierName,
[tbl_Inbound/Outbound].[Trailer#], [tbl_Inbound/Outbound].[Tractor#],
IIf([ArrivalDate] And [ArrivalTime] Is Not
Null,DateValue([ArrivalDate])+TimeValue([ArrivalTime]),Null) AS Arrival,
[tbl_Inbound/Outbound].ArrivalDate, [tbl_Inbound/Outbound].ArrivalTime,
IIf([DepartureDate] And [DepartureTime] Is Not
Null,DateValue([DepartureDate])+TimeValue([DepartureTime]),Null) AS
Departure, [tbl_Inbound/Outbound].DepartureDate,
[tbl_Inbound/Outbound].DepartureTime, [tbl_Inbound/Outbound].OrderID,
IIf([OrderID] Is Not Null And [Inbound]="yes","IB/Loaded",IIf([OrderID] Is
Not Null And [Outbound]="yes","OB/Loaded","Empty")) AS TrailerStatus,
Diff2Dates("dh",[Arrival],Now()) AS DwellTime,
[tbl_Inbound/Outbound].Outbound, [tbl_Inbound/Outbound].Inbound
FROM [tbl_Inbound/Outbound];
Click to expand...
Click to expand...
 
Hi,

A question for clarification: For any one trailer, can a different
carrier take it out from the one that brought it in?

Clifford Bass
 
Hi Clifford,
Unfortunately yes. Sometimes the carrier will contract another carrier to
go pick it up the trailer. Again, thanks for all your help!
 
Hi,

Overall, an interesting project! The following is only one possible
way to do it. I have taken a few liberties in forming your results somewhat
differently and redefining some stuff. My status output format may work for
you, or you can use the concepts behind it to produce whatever you need.

For starters, I presume that you create/update links to the data in the
spreadsheets. I called the table links tblInbound_Sheet and
tblOutbound_Sheet.

In thinking it over, since a lot of the data is the same for inbound as
for outbound, I ended up deciding to use a self-referencing table. The
reason for making it self-referencing is that it then makes it easy to match
up the inbound records with the outbound records with a simple join of the
table to itself. No need to figure out every time what record goes with what
record. I chose to name the table tblInbound_Outbound. The use of the slash
in a table (or field) name causes you always to have to enclose it in square
brackets and I did not want that hassle. Also I have a preference for naming
tables without an underscore after tbl. You can adjust as needed for your
preferred names. The fields:

TransactionID (autonumber)
TransactionType ("I" for inbound or "O" for outbound)
TransactionDateTime
CarrierName
TrailerNumber (used text type in case some are non-numeric)
TractorNumber (used text type also)
DriverName
OrderID
RelatedTransactionID (Long number, the TransactionID of the
matching inbound/outbound record)

All disallow Null except for OrderID and RelatedTransactionID. Again,
the use of a # sign causes the need for the square brackets, so I went with
Number as part of the trailer and tractor number field names. Note that you
really should have a bunch of other tables for identifying the various
objects: carrier, trailer, tractor and driver) and in the above table you
would use the appropriate ID instead of the actual value. For example, a
carriers table with a CarrierID, CarrierName and other carrier-related
fields. Then the above would have CarrierID instead of CarrierName. This
will complicate the import, which I will leave for you to figure out, but
will be worth it in the long run.

The query to import the inbound data:

INSERT INTO tblInbound_Outbound ( TransactionType, TransactionDateTime,
CarrierName, TrailerNumber, TractorNumber, DriverName, OrderID )
SELECT "I" AS TransactionType, [ArrivalDate]+[ArrivalTime] AS
ArrivalDateTime, A.CarrierName, A.[Trailer#], A.[Tractor#], A.DriverName,
A.OrderID
FROM tblInbound_Sheet AS A;

The query to import the outbound data:

INSERT INTO tblInbound_Outbound ( TransactionType, TransactionDateTime,
CarrierName, TrailerNumber, TractorNumber, DriverName, OrderID,
RelatedTransactionID )
SELECT "O" AS TransactionType, [DepartureDate]+[DepartureTime] AS
DepartureDateTime, A.CarrierName, A.[Trailer#], A.[Tractor#], A.DriverName,
A.OrderID, B.TransactionID
FROM tblOutbound_Sheet AS A, tblInbound_Outbound AS B
WHERE (((B.TransactionType)="I") AND
((B.TransactionDateTime)<[A].[DepartureDate]+[A].[DepartureTime]) AND
((B.TrailerNumber)=CStr([A].[Trailer#])) AND ((Not Exists (select * from
tblInbound_Outbound as C where C.TransactionType = B.TransactionType and
C.TrailerNumber = B.TrailerNumber and C.TransactionDateTime >
B.TransactionDateTime and C.TransactionDateTime < A.DepartureDate +
A.DepartureTime))=True));

That one is more complicated because it also assigns the associated
inbound TransactionID to the RelatedTransactionID (I think I got it right--if
not, let me know).

Then a query to assign the related outbound TransactionID into the
RelatedTransactionID of the inbound transactions that now have corresponding
outbound transactions:

UPDATE tblInbound_Outbound AS I INNER JOIN tblInbound_Outbound AS O ON
I.TransactionID = O.RelatedTransactionID SET I.RelatedTransactionID =
[O].[TransactionID]
WHERE (((I.RelatedTransactionID) Is Null) AND ((I.TransactionType)="I"));

And finally a query to list the status of each trailer that has come
in, and possibly left:

SELECT I.TransactionID, I.TrailerNumber, I.CarrierName AS InboundCarrier,
I.TractorNumber AS InboundTractor, I.TransactionDateTime AS ArrivalDateTime,
I.DriverName AS InboundDriver, I.OrderID AS InboundOrder,
IIf(IsNull(.[OrderID]),"Empty","I/B Loaded") AS InboundTrailerStatus,
O.CarrierName AS OutboundCarrier, O.TractorNumber AS OutboundTractor,
O.TransactionDateTime AS DepartureDateTime, O.DriverName AS OutboundDriver,
O.OrderID AS OutboundOrder,
Switch(IsNull(.[RelatedTransactionID]),Null,IsNull([O].[OrderID]),"Empty",True,"O/B
Loaded") AS OutboundTrailerStatus,
DateDiff("h",.[TransactionDateTime],DateAdd("n",30,IIf(IsNull([O].[TransactionDateTime]),Now(),[O].[TransactionDateTime])))
AS DwellHoursTotal, [DwellHoursTotal]\24 AS DwellDays, [DwellHoursTotal] Mod
24 AS DwellHours, IIf(IsNull(.[RelatedTransactionID]),Null,"Yes") AS
Outbound, IIf(IsNull(.[RelatedTransactionID]),"Yes",Null) AS Inbound,
IIf(IsNull(.[RelatedTransactionID]),"Open","Closed") AS TransactionStatus
FROM tblInbound_Outbound AS I LEFT JOIN tblInbound_Outbound AS O ON
I.RelatedTransactionID = O.TransactionID
WHERE (((I.TransactionType)="I"));

I set it up to round the dwell hours to the nearest whole hour. You
can adjust as needed. Don't view this as an end product; there are
refinements that can be made to the above. Also there is a potential problem
if you define and enforce the referential integrity of the TransactionID /
RelatedTransactionID. It can prevent the deletion of some records.

Questions or whatnot, let me know.

Good Luck,

Clifford Bass
 
Thank you, thank you, thank you.....I really mean it! Your the best! I am
so grateful. I just got in and my brain is functioning slow tonight so I
will work on it tomorrow. If I have any questions or get stuck (I do that
often!), I will let you know. Thank you again so much for your help. From
looking at it, I stated in the first line of my post I had been trying to get
this for a couple of weeks, I think I still would of been trying a couple of
years from now. Thank You!
--
clueless


Clifford Bass said:
Hi,

Overall, an interesting project! The following is only one possible
way to do it. I have taken a few liberties in forming your results somewhat
differently and redefining some stuff. My status output format may work for
you, or you can use the concepts behind it to produce whatever you need.

For starters, I presume that you create/update links to the data in the
spreadsheets. I called the table links tblInbound_Sheet and
tblOutbound_Sheet.

In thinking it over, since a lot of the data is the same for inbound as
for outbound, I ended up deciding to use a self-referencing table. The
reason for making it self-referencing is that it then makes it easy to match
up the inbound records with the outbound records with a simple join of the
table to itself. No need to figure out every time what record goes with what
record. I chose to name the table tblInbound_Outbound. The use of the slash
in a table (or field) name causes you always to have to enclose it in square
brackets and I did not want that hassle. Also I have a preference for naming
tables without an underscore after tbl. You can adjust as needed for your
preferred names. The fields:

TransactionID (autonumber)
TransactionType ("I" for inbound or "O" for outbound)
TransactionDateTime
CarrierName
TrailerNumber (used text type in case some are non-numeric)
TractorNumber (used text type also)
DriverName
OrderID
RelatedTransactionID (Long number, the TransactionID of the
matching inbound/outbound record)

All disallow Null except for OrderID and RelatedTransactionID. Again,
the use of a # sign causes the need for the square brackets, so I went with
Number as part of the trailer and tractor number field names. Note that you
really should have a bunch of other tables for identifying the various
objects: carrier, trailer, tractor and driver) and in the above table you
would use the appropriate ID instead of the actual value. For example, a
carriers table with a CarrierID, CarrierName and other carrier-related
fields. Then the above would have CarrierID instead of CarrierName. This
will complicate the import, which I will leave for you to figure out, but
will be worth it in the long run.

The query to import the inbound data:

INSERT INTO tblInbound_Outbound ( TransactionType, TransactionDateTime,
CarrierName, TrailerNumber, TractorNumber, DriverName, OrderID )
SELECT "I" AS TransactionType, [ArrivalDate]+[ArrivalTime] AS
ArrivalDateTime, A.CarrierName, A.[Trailer#], A.[Tractor#], A.DriverName,
A.OrderID
FROM tblInbound_Sheet AS A;

The query to import the outbound data:

INSERT INTO tblInbound_Outbound ( TransactionType, TransactionDateTime,
CarrierName, TrailerNumber, TractorNumber, DriverName, OrderID,
RelatedTransactionID )
SELECT "O" AS TransactionType, [DepartureDate]+[DepartureTime] AS
DepartureDateTime, A.CarrierName, A.[Trailer#], A.[Tractor#], A.DriverName,
A.OrderID, B.TransactionID
FROM tblOutbound_Sheet AS A, tblInbound_Outbound AS B
WHERE (((B.TransactionType)="I") AND
((B.TransactionDateTime)<[A].[DepartureDate]+[A].[DepartureTime]) AND
((B.TrailerNumber)=CStr([A].[Trailer#])) AND ((Not Exists (select * from
tblInbound_Outbound as C where C.TransactionType = B.TransactionType and
C.TrailerNumber = B.TrailerNumber and C.TransactionDateTime >
B.TransactionDateTime and C.TransactionDateTime < A.DepartureDate +
A.DepartureTime))=True));

That one is more complicated because it also assigns the associated
inbound TransactionID to the RelatedTransactionID (I think I got it right--if
not, let me know).

Then a query to assign the related outbound TransactionID into the
RelatedTransactionID of the inbound transactions that now have corresponding
outbound transactions:

UPDATE tblInbound_Outbound AS I INNER JOIN tblInbound_Outbound AS O ON
I.TransactionID = O.RelatedTransactionID SET I.RelatedTransactionID =
[O].[TransactionID]
WHERE (((I.RelatedTransactionID) Is Null) AND ((I.TransactionType)="I"));

And finally a query to list the status of each trailer that has come
in, and possibly left:

SELECT I.TransactionID, I.TrailerNumber, I.CarrierName AS InboundCarrier,
I.TractorNumber AS InboundTractor, I.TransactionDateTime AS ArrivalDateTime,
I.DriverName AS InboundDriver, I.OrderID AS InboundOrder,
IIf(IsNull(.[OrderID]),"Empty","I/B Loaded") AS InboundTrailerStatus,
O.CarrierName AS OutboundCarrier, O.TractorNumber AS OutboundTractor,
O.TransactionDateTime AS DepartureDateTime, O.DriverName AS OutboundDriver,
O.OrderID AS OutboundOrder,
Switch(IsNull(.[RelatedTransactionID]),Null,IsNull([O].[OrderID]),"Empty",True,"O/B
Loaded") AS OutboundTrailerStatus,
DateDiff("h",.[TransactionDateTime],DateAdd("n",30,IIf(IsNull([O].[TransactionDateTime]),Now(),[O].[TransactionDateTime])))
AS DwellHoursTotal, [DwellHoursTotal]\24 AS DwellDays, [DwellHoursTotal] Mod
24 AS DwellHours, IIf(IsNull(.[RelatedTransactionID]),Null,"Yes") AS
Outbound, IIf(IsNull(.[RelatedTransactionID]),"Yes",Null) AS Inbound,
IIf(IsNull(.[RelatedTransactionID]),"Open","Closed") AS TransactionStatus
FROM tblInbound_Outbound AS I LEFT JOIN tblInbound_Outbound AS O ON
I.RelatedTransactionID = O.TransactionID
WHERE (((I.TransactionType)="I"));

I set it up to round the dwell hours to the nearest whole hour. You
can adjust as needed. Don't view this as an end product; there are
refinements that can be made to the above. Also there is a potential problem
if you define and enforce the referential integrity of the TransactionID /
RelatedTransactionID. It can prevent the deletion of some records.

Questions or whatnot, let me know.

Good Luck,

Clifford Bass
Click to expand...
 
Hi,

You are very welcome! Do let me know how it goes once your brain gets
better.

Clifford Bass
 
Hi Clifford,
I just finished...WOW! It took me quite a while to get it working. My 3
day class at the local community college spent, I believe, 20 minutes on
append queries. Yes, I am an advanced student....lol! I think at first I
broke every violation Access has, but I didn't give up thanks to the
community. I did some reading and went back and forth and finally got it
working. It works beautifully!!!!! Better than I could have ever imagined.
I never doubted you just me! One more question, my next step in my journey
is learning how to write the language. Any suggestions on what class I
should be looking for? I am hooked on Access and want to fully understand it
or you could move next door and I wouldn't have to take any more
classes...lol! Again, thanks so much. I am forever grateful!
 
Hi Em,

Hurrah!

Well, we are looking at single-digit (- and +) temperatures (F) in the
coming days; again. So, if it is warmer where you are.... <Grin> Well
probably not due to other reasons.

A class on VBA or advanced Access programming might be worthwhile. But
I probably would start with a book on VBA. Browse the database and/or
programming sections of your local book store. There are also tutorials on
the internet. Try a search for "access vba tutorial" or variations. There
are a bunch of web sites from MVP listed at
<http://www.mvps.org/links.html#Access>. A former MVP's site:
<http://accessjunkie.com/default.aspx>.

Hope that helps, and you are quite welcome!

Clifford Bass
 
Hi Clifford,
Just wanted to let you know that I took your advice and went to Borders and
picked up a book on "the language". I think there is a misprint because it
says I will learn it in 21 days, I think it should say "years". Thanks again
for you help and advice.
Em
P.S. I was starting to think David Copperfield was out in the yard doing
some magic but thanks to you, I believe he is in Vegas! (All trailers are
accounted for!) Have a great day and stay warm!
 
Back
Top