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!
12 1677
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.
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?
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
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).
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
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?"
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);
-
FishVal,
This is incredible. Thanks for putting so much effort into this!
You are quite welcome.
Best regards,
Fish.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Oksana Yasynska |
last post by:
Hi all,
I'm running Postgres 7.2.1 and I need to return multiple row sets from plpgsql
function. I'm new in plpgsql but according documentation and everything I
could find in the mailing list...
|
by: maxmarengo |
last post by:
I am trying to write a query in Access (or SQL) that works on a table
like this:
Location Gridreference
Ben Nevis NQ1234512345
Ben Doon NQ1230012300
and so on for several thousand...
|
by: Robert McEuen |
last post by:
Using Access 97 on WinXP
I have data in a DB2 table that I'm trying to get into an identical
table in my backend db. Based on volume of data and frequency of
download, I'm trying to avoid...
|
by: RAJASEKHAR KONDABALA |
last post by:
Hi,
Does anybody know what the fastest way is to "search for a value in a
singly-linked list from its tail" as oposed to its head?
I am talking about a non-circular singly-linked list, i.e.,...
|
by: ebernedo |
last post by:
Hey guys,
I have two main questions
First off (pictures are kind of blurry)
I have this table
http://i197.photobucket.com/albums/aa109/ebernedo/DiscTable.jpg
And thats my database I use my...
|
by: suek |
last post by:
I have a table with over 4000 records to search upon, and the users
don't like a combo box.
So what I have been trying to do for the last twelve hours is do some
code to get a text box to search. ...
|
by: Aditya |
last post by:
Hi All,
I would like to know how it is possible to insert a node in a linked
list without using a temp_pointer. If the element is the first element
then there is no problem but if it is in...
|
by: =?Utf-8?B?UGF1bA==?= |
last post by:
Hi I am using a list search extender and have the control it is assigned to
as a control in a template column in a gridview. Anyhow for some reason the
type to search section that normally appears...
|
by: AmberJain |
last post by:
Windows Autorun FAQs: List of autostart locations
Linked from the Original article- "Windows Autorun FAQs: Description".
Que: Can you list all the autostart locations for windows?
Ans: Here is...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |