Help with execution

[kieranharrison]

Hi Guys,



Any chance anyone could please provide an idea/ tell me how I can execute this;

So basically, I have the 2 tables which are shown below which get populated by the two forms (also shown below) each month every time we receive an upload file from an airport. Each airport has different airlines with different upload files.

My question is, how can I create something which tells me which of the airline files I have not yet received from the corresponding airport? I could create a directory table which tells me what airline files I am expecting to receive from each airport, but I don't really know what to do from there?

Thanks

[June7]

So somewhere you record the date of last received file for each airport? Use that table in query.

Need a better understanding of database structure to advise more specific.

If you want to provide db for analysis, follow instructions at bottom of my post.

[kieranharrison]

Hi June7,

Yep, definitely record the date.

Thanks very much for your help! I appreciate it. Please see here for the file - Monthly Upload File Register - Edit.zip

[kieranharrison]

The same process would then follow for files I have not yet uploaded/ published, but once I know how to do it I can do the other two.

[June7]

Try this query:

SELECT IATA_Dir.IATA_Code
FROM IATA_Dir
WHERE IATA_Code NOT IN (SELECT IATA_Code FROM received_files_table WHERE Format(Received_Date, "mmmyy") = [enter MMMYY]);

The [enter MMMYY] parameter can be reference to control on form. Or don't put filter criteria in query and instead apply to report when opened (my preference).


Advise not to build lookups in tables, just build combobox or listbox on form. Probably should have an Airlines table.

[kieranharrison]

Thanks very much for your help June, I really appreciate it!

Just a question, how will the query/report know which airlines have not been received from each airport? That data isn't actually in there yet so i'm thinking i'll have to create a separate table for each airport, then just have one field in each table which tells you what airlines are expected to be received for that specific airport?

For example, Ballina Airport should receive - Jetstar, Qantas and Virgin data each month. How can I create a query/ report which tells me which of those 3 airlines has not yet been received ?

PS: I'm very new to access, forgot to mention.

Thanks again

[June7]

Don't have a table for each airport - one table with two fields: IATA_Code and Airline.

SELECT AirportAirlines.*
FROM AirportAirlines
WHERE IATA_Code & Airline NOT IN (SELECT IATA_Code & Airline FROM received_files_table WHERE Format(Received_Date, "mmmyy") = [enter MMMYY]);

Might want to have a table of Airlines with name and airline code and use Airline_Code as key instead of repeating full Airline name in multiple tables.

[kieranharrison]

Ok so I've done that. Now I have a field in my Received Files Form which is a combo box that uses the airline values that I have chosen to type myself because if I just link it to the 'airline' field in the airline_dir table then there is a bunch of duplicates - see below




Would that be correct?

Also, I put some dummy data in the received_files_table so that I could test the query. I input the query so it should only show that I have not yet received 'Rex' (airline) for 'BNK' (airport), however, it just copies the whole airline_dir table??

See here if it helps - Monthly Upload File Register - v2 for june.zip

[ssanfu]

PMFJI,

I would recommend you get your tables straightened out before moving on. You have a lot of look-up FIELDs. You should never use look-up FIELDS (look-up TABLES are OK).
What happens when we reach the next year (2020)??? You (or someone) have to go into each field and edit the dates in every field that used the dates.

You should never allow anyone to enter/edit data directly in tables, so setting up the look-up FIELDs is a waste of time (IMHO) - and you also have to set up the combo boxes so you've just doubled your design work/time.


See http://www.theaccessweb.com/lookupfields.htm
and http://www.theaccessweb.com/tencommandments.htm


And maybe you would post a couple of received files and a couple of published files. (change any sensitive data)

[June7]

I suggested you have a table of airlines.

Otherwise, query can be: SELECT DISTINCT Airline FROM airline_dir;

If you want airline combobox to show only airlines associated with selected airport, then that would be cascading combobox - a very common topic.

If lookup is set in table (I NEVER do this) then dragging field from field list in form design will adopt the lookup settings defined in table. A minor convenience not worth the headaches lookups in table cause.

[kieranharrison]

Thanks very much for that ssanfu!

Don't worry about jumping in, the more help the better haha.

I will straighten out the tables and post the DB again shortly. I am just trying to get the first stage (received files) working properly then I can move forward with uploaded and published. So don't worry about them for now.

Attached is the upload register file we are using at the moment.

June7 - I do have a table of airlines with the fields you advised? I managed to get the query working properly using the following;

SELECT airline_dir.*
FROM airline_dir
WHERE Airport & Airline NOT IN (SELECT Airport & Airline FROM received_files_table WHERE Format(Processing_Month, "mmyy") = [enter MMYY]);

Give me some time to fix this all up and I will comment again. Thanks very much for your help guys, I really appreciate it.

[June7]

You can have tables:

AirportIATA (a table of airports and info about airports - AirportName, IATA, Address, etc)

AirlineCodes (a table of airlines and info about airlines - AirlineName, AirlineCode, HQAddress, etc)

AirportsAirlines (a junction table that associates airports with airlines)

Or if you don't care about details for airline and airport entities, just have AirportsAirlines and use DISTINCT qualifier in query.

[kieranharrison]

Hi Guys,

Think I've got a final product here (or close to it).

Would really appreciate if you could have a quick skim over it and see if there is anything I could improve? (file attached)

I no longer have any lookup fields within tables, all of my combo box values are pulling from a directory table and I have replicated the code provided to create a couple additional queries.

I just have one question though - is it possible to change the column headers in a query? I have a query titled 'Monthly Upload Register - All' and I think it would be much easier to understand if the column headers could be labelled differently.


Thanks again,

[ssanfu]

I downloaded the file from Post #13.....it still has the look up fields.......



Oops, I forgot to answer "is it possible to change the column headers in a query??

Yes, you can and it is easy.

You have a query "Query_Test" - the SQL is

SELECT airline_dir.*
FROM airline_dir
WHERE Airport & Airline NOT IN (SELECT Airport & Airline FROM received_files_table WHERE Format(Received_Date, "mmmyy") = [enter MMMYY]);

It is not recommended to use the "*" (all fields) so I'm going to change the query to

SELECT airline_dir.Airport, airline_dir.Airline
FROM airline_dir
WHERE ((([Airport] & [Airline]) Not In (SELECT Airport & Airline FROM received_files_table WHERE Format(Received_Date, "mmmyy") = [enter MMMYY])));

I'm going to change the query column names to "MyAirport" and MyAirline"

The SQL looks like

SELECT airline_dir.Airport AS MyAirline, airline_dir.Airline AS MyAirline
FROM airline_dir
WHERE ((([Airport] & [Airline]) Not In (SELECT Airport & Airline FROM received_files_table WHERE Format(Received_Date, "mmmyy") = [enter MMMYY])));

In the query design grid:


(Note the colon)
MyAireline:Airline

[kieranharrison]

Ssanfu - I must be going blind. Where are they?