Hello, I am working on a program in access and am stumped how I do this particular calculation.

I have two tables i'm working with:

1) Sheet1 - has all the information about my files (airwaybills like on UPS, it has all the information in here)
2) Airlines - this information has all the rates for each airline.

For the Table "Sheet1" I have a field named "AWB NUMBER" and it's in the format of XXX-XXXX-XXXX with the first three X's designating an "AIRLINE CODE".

The Table "airlines" has a field "AIRLINE CODE" and in this query I am creating I am trying to find the best way to make a query where the first three digits of the AWB are matched to the AIRLINE CODE, then the "CHARGE" in the "airline" table would be applied for that particular AWB. I have it all working but the only part I can't get to work is if I do not have the particular airline code in the system, the query will just ignore that entry in "sheet1".. Below is an example:

SHEET1:

REF# AWB NUMBER
1 020-5555-5555
2 172-5555-5555
3 999-5555-5555

AIRLINE:

AIRLINE CODE CHARGE
020 25
172 20

QUERY1:

REF# AWB NUMBER CHARGE
1 020-5555-5555 25
2 172-5555-5555 20

In the query I have added both Sheet1 and Airlines for tables. Under the SQL i'm selecting all the above fields THEN i'm using WHERE ([AIRLINE CODE]=Mid$([AWB NUMBER],1,3)).
I really want my query to add the 3rd file which doesn't have an airline code designated and I want it to put in a default value of say 25.. Can anybody help me?

I can get you half way there...What you need is a left join, which specifies that you want all the rows in the first table (sheet1) to show up, even if a match isn't found in the next table. Depending on how Access works, it may or may not be possible to get the default of 25 in there...Maybe if the field's default (in the field definition) was 25 it would work.

I'm not too great on access sql syntax, but the query would look something like: SELECT * FROM [SHEET1] Left join [AIRLINE] WHERE ([AIRLINE CODE]=Mid$([AWB NUMBER],1,3)).

Good luck,
-SN

As SN suggested use a LEFT OUTER JOIN on your query which will return all values from the left table and will show nulls for the right table where there is no value.

However the column where there is likely to be nulls needs to be wrapped inside the IIF function.

i.e IIF(TableName.ColumnName Is Null, 25, TableName.ColumnName,) AS ColumnName

The example above evaluates each column value. if a value exists then it is displayed. If no value exists then 25 is returned instead.