Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    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));

  2. #2
    Join Date
    Apr 2009

    Pretty easy fix if i understand you correctly. Bear in mind I have a SQL background so im not sure if Access can do what you require, however here is the upshot.

    You need to do a left join from your SENT table to your RECEIVED table joining on Loan No.

    A typical query would look like this

    select * from loanmaster a left join loansent b on a.loanno = b.loanno

    Now im not sure how a left join translates in access... at least this give you a heads up

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts