By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Temp list created to search locations

P: 78
I want to compare flight data to fuel purchase data and make sure our planes were at the place the fuel vendor says they were.

I have one table of flight data (flight), one table of Vendor info (Vendors) and one union query of fuel invoices (Union Master Invoices). flight has columns nNumber, arrDate, and arrLocation. Vendors has columns VendorID and AirportID. UMI has columns TransDate, VendorID, and TailNumber. UMI gets it's location (AirportID) from a join on VendorID.

The following code is a start towards what I need:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Union Master Invoices].TransDate, [Union Master Invoices].TailNumber, flight.arrLocation, Vendors.AirportID, [Union Master Invoices].VendorID
  2. FROM ([Union Master Invoices] INNER JOIN flight ON ([Union Master Invoices].TransDate = flight.arrDate) AND ([Union Master Invoices].TailNumber = flight.nNumber)) INNER JOIN Vendors ON (Vendors.AirportID = flight.arrLocation) AND ([Union Master Invoices].VendorID = Vendors.VendorID);
Now this only checks to see if the airplane was at the same airport given from the fuel data and flight data on the same date (theoretically a perfect match!). But alas, our data isn't perfect, and we need some leeway. What I need is to know if there is a match of the locations (fuel data (AirportID) and the flight data (arrLocation)) within a day of the flight date (arrDate); that is, arrDate =, +1, or -1 TransDate (3 criteria). I don't know how to get this to work correctly, as a simple criteria selection doesn't work because it will pull multiple rows. I just need to know if the airplane was there any of those 3 days.

What I'm thinking is some sort of temporary list (or complex iif statement), where for each TailNumber for each TransDate, get list of [arrLocation]'s where arrDate is =,+1, or -1 TransDate, and check if AirportID is in list. It's kinda there in my head conceptually but I don't know how to write that out, or if that's event he best route. Thanks for the help. And please let me know if I need to add more detail!
Apr 15 '08 #1
Share this Question
Share on Google+
12 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, kpfunf.

Try to replace this join criteria
Expand|Select|Wrap|Line Numbers
  1. .... [Union Master Invoices].TransDate = flight.arrDate ....
with this one
Expand|Select|Wrap|Line Numbers
  1. .... Abs(DateDiff("d", [Union Master Invoices].TransDate, flight.arrDate) )<=1  ....
.

Regards,
Fish

P.S. Access query builder is unable to represent that type of join. So this change as well as the further ones should be done in SQL view.
Apr 15 '08 #2

P: 78
FishVal,
That criteria works, thanks. As I'm working on this, I'm seeing a problem. What I need is the following:
1. Is there a match between the two locations for the same day?
2. If not, is there a match between the two locations within 1 day?
(Having these two in seperate columns would be best, so I can investigate seperately).

Does this make sense? I need to see if the plane visited the airport (arrLocation) at all within a day of the fueling date (TransDate) and location (AirportID). Because there are multiple flights per day, and to different locations per day, I need more of an iif statement to check true or false (Was the plane there are any point over this 3 day span?). This lead me to the "in list" idea. Just joining things brings back multiple rows and can cause problems. Any thoughts?
Apr 15 '08 #3

P: 78
Bump .
Apr 16 '08 #4

P: 78
Bump .
Apr 16 '08 #5

P: 78
Bump .
Apr 17 '08 #6

FishVal
Expert 2.5K+
P: 2,653
Hi. Sorry for the delay.

I certainly have some ideas but have a doubts whether all of them will be efficient.
Would you like to attach a sanitized copy of you db to the thread to let me make some trials?

Regards,
Fish
Apr 17 '08 #7

P: 78
FishVal,
Here's a small sample of data. This should give you enough to work with (I hope!). Let me know if you have questions, as some of the data may seem odd. Also, not all aircraft in flight will appear in UMI. As well, I made UMI a table rather than it's original form of a query, which it will remain in the main database (but the columns are the same).
Attached Files
File Type: zip temp.zip (15.2 KB, 58 views)
Apr 18 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Hi, kpfunf.

I've looked at the sample and I've got several questions.
  • As far as I've understood, the purpose of the query is to inspect flight.nNumber - UMI.TailNumber one-to-many relationship. I mean you'd like to get a dataset of records from [flight] and [UMI] combined on [flight.nNumber] = [UMI.TailNumber] with additional fields: whether a plane was in an airport on that date, whether a plane was in an airport on the day before/after. Am I right?
  • [UMI] contains several full duplicate records and much more records with all field except [Invoice] duplicate. How these records are supposed to be treated? I mean particularly the second type duplicates (with different [Invoice] field). Are they related to one record in [UMI]?

Regards,
Fish
Apr 21 '08 #9

P: 78
FishVal,

Some UMI rows will seem duplicated due to multiple purchases same day, same aircraft, same location. This is because some aircraft make multiple flights in a day. Because the overall goal is to see if the tail and the location from the flight data has a match in the fuel data, I think the duplication shouldn't be an issue (correct me if I'm wrong); one column showing a direct match (same tail, same day, same location) and a column matching "with leniency" (same tail, +/-1 day (including same day), same location). These columns really would just be a Yes/No, indicating conditions were met (doesn't have to be a recordset return).

The joining was the most difficult part to me because I don't think a 1-to-1 can work because of the multiplicity. (In fact a plane may go to multiple airports in one day). That's why I need something that checks if conditions are true (a complicated iif?).

Thinking as I'm typing...the data to "verify" is fuel data (we are to assume flight data is accurate, checking to see if fuel is correct). This is because not all flights will have fuel data, but all fuel data should have flights.

In summary, this query is a quick way to check "Okay, this plane wasn't actually at this airport at all (within a 3 day window), why do we have a fuel purchase?"
Apr 21 '08 #10

FishVal
Expert 2.5K+
P: 2,653
Ok.

Here is query sequence to get matches from [flight] and [UMI].

qryUMIFKs - returns unique combinations of Date/Vendor/TailNumber from [UMI]
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Union Master Invoices].TransDate, [Union Master Invoices].VendorID, [Union Master Invoices].TailNumber
  2. FROM [Union Master Invoices];
  3.  
qryQualifiedHits - returns matches from [flight] and [UMI] with calculated boolean fields indicating exact or approximate date match
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT flight.nNumber, qryUMIFKs.TailNumber, flight.arrLocation, Vendors.AirportID, Vendors.VendorID, qryUMIFKs.VendorID, qryUMIFKs.TransDate, ((Abs(DateDiff("d",flight.arrDate,qryUMIFKs.TransDate))=1) Or Null) AS blnApproxDateMatch, (flight.arrDate=qryUMIFKs.TransDate Or Null) AS blnExactDateMatch
  2. FROM (flight INNER JOIN Vendors ON flight.arrLocation=Vendors.AirportID) INNER JOIN qryUMIFKs ON (Abs(DateDiff("d",flight.arrDate,qryUMIFKs.TransDate))<=1) AND (Vendors.VendorID=qryUMIFKs.VendorID) AND (flight.nNumber=qryUMIFKs.TailNumber);
  3.  
qryGroupedQualifiedHits - groups records from [qryQualifiedHits] by Date/Vendor/TailNumber
Expand|Select|Wrap|Line Numbers
  1. SELECT qryQualifiedHits.nNumber, qryQualifiedHits.arrLocation, qryQualifiedHits.TransDate, Sum(qryQualifiedHits.blnApproxDateMatch) AS blnApproxMatch, Sum(qryQualifiedHits.blnExactDateMatch) AS blnfblnExactMatch
  2. FROM qryQualifiedHits
  3. GROUP BY qryQualifiedHits.nNumber, qryQualifiedHits.arrLocation, qryQualifiedHits.TransDate;
  4.  
qryUMIFKsMatchStatus - returns the same but with all records from [qryUMIFKs].
Expand|Select|Wrap|Line Numbers
  1. SELECT qryUMIFKs.*, qryGroupedQualifiedHits.blnApproxMatch, qryGroupedQualifiedHits.blnfblnExactMatch, IIf(IsNull(qryGroupedQualifiedHits.blnApproxMatch Or qryGroupedQualifiedHits.blnfblnExactMatch), True, Null) AS blnNoMatch
  2. FROM qryUMIFKs LEFT JOIN qryGroupedQualifiedHits ON (qryUMIFKs.TailNumber = qryGroupedQualifiedHits.nNumber) AND (qryUMIFKs.TransDate = qryGroupedQualifiedHits.TransDate) AND (qryUMIFKs.VendorID = qryGroupedQualifiedHits.VendorID);
  3.  
Attached Files
File Type: zip temp_2008-04-18.zip (18.7 KB, 64 views)
Apr 22 '08 #11

P: 78
FishVal,
This is incredible. Thanks for putting so much effort into this!
Apr 22 '08 #12

FishVal
Expert 2.5K+
P: 2,653
You are quite welcome.

Best regards,
Fish.
Apr 22 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.