Inner Outer Join

[steve1111]

I am trying to create a query where i look at the [date],[driver],[amount] from table E and look at the same fields in table F and i need to know all the entries where they do not match. the inner join i am creating in the SQL view of Access (i cannot right code very well in VBA) is returning the matching line items.

essentially table E records when i enter a receipt manually into access. Table F is an upload from a credit vendor. i need to reconcile when there is a charge on on table f that was not input in table e, and the other way around.

thanks for any help you can give a beginner,

 

[pbaldy]

It sounds like what you want is a FULL OUTER JOIN, which unfortunately Access doesn't support (SQL Server and other platforms do). The workaround is a UNION query that joins together 3 SELECT's with INNER, LEFT and RIGHT joins.

 

[steve1111]

Thanks Paul i will try working the union.

 

[pbaldy]

It should work; post back if you get stuck. I have a similar situation with data manually entered being compared to a vendor upload. My data is in SQL Server, so I can use the full outer join, which is certainly simpler.

 

[steve1111]

yeah i am a little stuck tying to write the three selects and froms

 

[pbaldy]

Hard to know what you're doing wrong without seeing the SQL.

 

[steve1111]

SELECT FuelmanImport_tbl.[Transaction Date], FuelmanImport_tbl.[Cardholder Name], FuelmanImport_tbl.[Card Expiration Date], FuelmanImport_tbl.[Prompted ID], FuelmanImport_tbl.[MCC Description], FuelmanImport_tbl.[Merchant Name], FuelmanImport_tbl.[Merchant Address], FuelmanImport_tbl.[Merchant City], FuelmanImport_tbl.[Posted Amount]
FROM FuelmanImport_tbl LEFT JOIN Expense_tbl ON (FuelmanImport_tbl.[Posted Amount] <> Expense_tbl.Amount) AND (FuelmanImport_tbl.[Transaction Date] <> Expense_tbl.Date);

 

[pbaldy]

Are you sure you don't want = in the join?

 

[steve1111]

well i attempted the <> as i want the record where the amount is not equal, trying to find if the Date, Driver and Amount in one table is not in the other table then add to the query, but i am continuing to hit road blocks.

should i create the following queries table 1 unmatched date, table 1 unmatched amount
Table 2 unmatched date, table 2 unmatched amount and then join all of those in a union?

 

[pbaldy]

Well, what I do is join with = on the fields that should match, in my case date and driver. Then in the reporting I filter out the ones where the amounts are equal and the users don't care about seeing. What's left are the records where the amounts don't match, or where a record didn't exist in one of the tables.

 

[steve1111]

Would you be willing to show ow that SQL view looks?

 

[pbaldy]

It wouldn't help, because I'm using the FULL OUTER JOIN in SQL Server. In Access, you need to union together the 3 queries I mentioned earlier.

SELECT...INNER JOIN...
UNION ALL
SELECT...LEFT JOIN...
UNION ALL
SELECT...RIGHT JOIN...

 

[steve1111]

ok, i think i have a handle on this now, thanks so much Paul, when the report is widely loved i will give you a footnote!!

 

[pbaldy]

LOL! I appreciate you waiting until it's widely loved.

 

[steve1111]

When i write this SQL it returns the same number of records as what is in the Fuelman table and only shows one column named Transaction date but has driver names in it?!? so it has not pulled out the matching records from the expense leaving the unmatched

 

[steve1111]

Select AAexpense.[amount]
From AAexpense Left Join AAFuelman ON AAExpense.[Amount] = AAFuelman.[posted amount]
Where AAexpense.[amount]<>AAfuelman.[posted amount]
Union ALL
SELECT AAFuelman.[Transaction date]
From AAFuelman Inner Join AAExpense ON AAFuelman.[transaction date]=aaExpense.[date]
Where AAfuelman.[Transaction date]<>AAexpense.[date]
Union ALL
Select [Driver Pin query].[Driver]
From [Driver PIN query] Right Join AAFuelman ON [Driver PIN Query].[Prompted ID]=AAFuelman.[Prompted ID]

 

[pbaldy]

Well, I'm not really familiar with your data, but in the SQL above you only pulled from one table. I pull from both, so that Nulls are easily visible and differences can be calculated. Here's an example, comparing CNG data to driver trip sheet data (this is from a stored procedure, disregard the #):

SELECT #CNG.VehicleNumber AS CNGCar, #CNG.CNGQty, #CNG.DORDate,
  #TS.car_no AS TSCar, #TS.TSQty ,#TS.Enter_date AS TSDORDate,
  IsNull(#CNG.CNGQty, 0) - IsNull(#TS.TSQty, 0) As Diff
FROM #CNG FULL OUTER JOIN #TS
  ON #CNG.VehicleNumber = #TS.car_no AND  #CNG.DORDate = #TS.Enter_date