Unanswered: Access tables with similiar infromation
I have 2 tables. Both tables have loan numbers. I send a loan number to a vendor and I track home many loan numbers I sent. The 2nd table is the data back from the vendor with a status,and it has loan number as well. The vendor table may not match 100% of all loan numbers. This will be an exception, and track these events where I sent a loan number on a given date, and did not get that loan number back for the same date.
I want to see all activity for both sides of the transaction. I made a select query with a join by loan number , the join shows ALL records from the SENT table, and only those showing a match from the RECEIVED table.
On a particular day, with a 100% match send and received, I had 1581 records in both seperate tables. When I join the tables I get 1581. When I add the Loan number field from the received table to show any possible blanks, the count goes up to almost double. Note - a loan number can be sent twice, so all 1581 records may not be unique.
How can I design the query to show a max of only 1581 records, that will also display any possible blanks from the received table where the sent table had the loan number, but the received table did not contain that loan number for that day?
I am not an expert in SQL or VB, and work with the design view. Here is the SQL code. Is what I want possible to accomplish?
Thanks all for any help!
SELECT Disclosures_out_from_STM_Store.Loan_Num, Disclosures_out_from_STM_Store.Tranaction_type, Disclosures_out_from_STM_Store.Occupancy, Disclosures_out_from_STM_Store.Product, Disclosures_out_from_STM_Store.Field5, Disclosures_out_from_STM_Store.Field6, Disclosures_out_from_STM_Store.Branch, Disclosures_out_from_STM_Store.LOB, Disclosures_out_from_STM_Store.LO, Disclosures_out_from_STM_Store.First, Disclosures_out_from_STM_Store.Middle, Disclosures_out_from_STM_Store.Last, Disclosures_out_from_STM_Store.Field13, Disclosures_out_from_STM_Store.Current_Street, Disclosures_out_from_STM_Store.Current_City, Disclosures_out_from_STM_Store.Current_State, Disclosures_out_from_STM_Store.Current_Zip, Disclosures_out_from_STM_Store.Subject_Street, Disclosures_out_from_STM_Store.Subject_City, Disclosures_out_from_STM_Store.Subject_State, Disclosures_out_from_STM_Store.Subject_Zip, Disclosures_out_from_STM_Store.URL, Disclosures_out_from_STM_Store.Transaction, Disclosures_out_from_STM_Store.[Transaction Date], Disclosures_out_from_STM_Store.[Transaction dateTime], Disclosures_in_from_Elynx_Store.Loan
FROM Disclosures_out_from_STM_Store LEFT JOIN Disclosures_in_from_Elynx_Store ON Disclosures_out_from_STM_Store.Loan_Num = Disclosures_in_from_Elynx_Store.Loan
WHERE (((Disclosures_out_from_STM_Store.[Transaction Date])=#4/24/2009#) AND ((Disclosures_in_from_Elynx_Store.Loan) Is Null));