Temp list created to search locations

[kpfunf]

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:

SELECT [Union Master Invoices].TransDate, [Union Master Invoices].TailNumber, flight.arrLocation, Vendors.AirportID, [Union Master Invoices].VendorID
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!

[FishVal]

Hi, kpfunf.

Try to replace this join criteria

.... [Union Master Invoices].TransDate = flight.arrDate ....

with this one

.... 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.

[kpfunf]

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?

[kpfunf]

Bump .

[kpfunf]

Bump .

[kpfunf]

Bump .

[FishVal]

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

[kpfunf]

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).

[FishVal]

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

[kpfunf]

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?"

[FishVal]

Ok.

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

qryUMIFKs - returns unique combinations of Date/Vendor/TailNumber from [UMI]

SELECT DISTINCT [Union Master Invoices].TransDate, [Union Master Invoices].VendorID, [Union Master Invoices].TailNumber
FROM [Union Master Invoices];
 

qryQualifiedHits - returns matches from [flight] and [UMI] with calculated boolean fields indicating exact or approximate date match

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
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);
 

qryGroupedQualifiedHits - groups records from [qryQualifiedHits] by Date/Vendor/TailNumber

SELECT qryQualifiedHits.nNumber, qryQualifiedHits.arrLocation, qryQualifiedHits.TransDate, Sum(qryQualifiedHits.blnApproxDateMatch) AS blnApproxMatch, Sum(qryQualifiedHits.blnExactDateMatch) AS blnfblnExactMatch
FROM qryQualifiedHits
GROUP BY qryQualifiedHits.nNumber, qryQualifiedHits.arrLocation, qryQualifiedHits.TransDate;
 

qryUMIFKsMatchStatus - returns the same but with all records from [qryUMIFKs].

SELECT qryUMIFKs.*, qryGroupedQualifiedHits.blnApproxMatch, qryGroupedQualifiedHits.blnfblnExactMatch, IIf(IsNull(qryGroupedQualifiedHits.blnApproxMatch Or qryGroupedQualifiedHits.blnfblnExactMatch), True, Null) AS blnNoMatch
FROM qryUMIFKs LEFT JOIN qryGroupedQualifiedHits ON (qryUMIFKs.TailNumber = qryGroupedQualifiedHits.nNumber) AND (qryUMIFKs.TransDate = qryGroupedQualifiedHits.TransDate) AND (qryUMIFKs.VendorID = qryGroupedQualifiedHits.VendorID);
 

[kpfunf]

FishVal,
This is incredible. Thanks for putting so much effort into this!

[FishVal]

You are quite welcome.

Best regards,
Fish.