Results 1 to 5 of 5
  1. #1
    pinkbits is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    2

    Trying to insert a subquery into a like statement.

    I'm trying to use a subquery inside a LIKE statement to pull info from a table back to the original query and use that information for the like statement.

    It's currently returning nothing, so obviously something is wrong here.

    The subquery should return MIKE, which is then inserted into the main query and used to search a transaction list for that payment reference, providing the date, full reference and amount of the payment for each match.

    I hope I've provided enough information, if not, question away!

    Thanks in advance for any help.

    Code:
    SELECT Transactions.[Payment Date], Transactions.Reference, Transactions.Amount
    FROM Transactions
    WHERE (Transactions.Reference LIKE '*'+(SELECT Housemate_Details.Payment_Reference1
    FROM Housemate_Details
    WHERE ("Housemate_Details.Name" LIKE "*Michael*"))+'*');
    The point of this database is to track payments made by housemates to myself allowing me to see who owes me what. I'm using Access to parse the transaction history from my bank and display it in a readable manner.

    Tables:
    Transactions (A table linked to the CSV downloaded from my bank. As I download more transactions, I just append these to the bottom).
    Contains: Payment_Date (Date), Reference (Text), Amount (Currency).
    Note: Reference contains the full reference provided by my bank, I have to search these for the piece unique to each housemate. E.g. DIRECT CREDIT NETBANK elec is from 1 housemate in particular, unique by the string "DIRECT CREDIT NETBANK".
    Housemate_Details (Contains details of the people, mainly important is the payment reference, and the tenancy begin and end date.)


    Contains: ID (Autonumber), Name (Text), Mobile (Text), Tenancy_Began (Date), Tenancy_Ended (Date), Payment_Reference1 (Text), Payment_Reference2 (Text, unused at this point).
    Note: I've set Tenancy_Ended to 1/01/2099 for all tenants that are currently living here, comparison didn't seem to work the way I did it without.
    Bills (Contains all bills, for searching based on how long the housemate has been here)
    Contains: ID (Autonumber), Date Due (Date), Type (Text), Amount (Currency).
    Note: Amount is the share paid by each housemate when the bill came in. I couldn't be bothered at the moment getting access to work out based on the date ranges how many people it should be dividing it by.
    From there I've made queries for each housemate to parse their particular information from the database.
    <Housemate name>_Bills_Applicable:
    Code:
    SELECT Bills.[Date Due], Bills.Type, Bills.Amount
    FROM Bills, Housemate_Details
    WHERE (((Bills.[Date Due]) Between Housemate_Details.Tenancy_Began And IIf(Housemate_Details.Tenancy_Ended>Date(),Date(),Housemate_Details.Tenancy_Ended)) And ((Housemate_Details.Name)="<Housemate Name>"));
    <Housemate name>_Payments_Made:
    Code:
    SELECT Transactions.[Payment Date], Transactions.Reference, Transactions.Amount
    FROM Transactions
    WHERE (Transactions.Reference LIKE '*'+(SELECT Housemate_Details.Payment_Reference1
    FROM Housemate_Details
    WHERE ("Housemate_Details.Name" = <Housemate name>))+'*');
    I'm then trying to format these into a report, but that's not my main issue at the moment.

    If more info is needed, let me know. Thanks to those that have replied.
    Last edited by pinkbits; 09-03-2010 at 02:49 PM. Reason: Adding more information

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Access gave me an error message when I tried it saying only 1 result should be return in the sub query.
    that means should not use a subquery inside a LIKE statement .

    I just don't understand why you need to match transactions.referenceno with *payment_referenceno* instead of match them directly. How was the referenceno defined?

  3. #3
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    here is a try:

    SELECT distinct a.[Payment Date], a.Reference, a.Amount
    FROM Transactions as a
    inner join Housemate_Details as b
    on a.Reference like "*" & b.Payment_Reference1 & "*"
    WHERE b.Name LIKE "*Michael*"

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    pink,

    one thing that you might want to be wary of is using single quotes (' ') in sql. Access doesn't like them most of the time. trying using doubles instead.

    it may also help people help you if you posted the tables and structure of them so you have a visual to accompany your desc.

  5. #5
    pinkbits is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    2
    I've updated my original post to include more on the tables & Queries I'm using.

    To those confused as to why I'm searching referece vs reference.

    The payment reference is the unique string I use to search the transaction history provided by my bank. Now the issue is that this string will be within a larger string, so requiring the need to search.

    E.g. DIRECT CREDIT NETBANK rent is a transaction reference, whereas I'd be searching for "Netbank" within that to ascertain whether it's a particular housemate or not.

    Ideally I'd love to be able to run the one query which provides the transactions they've made, vs the transactions applicable to the period that they've been staying here and give a total for each, with a master total at the bottom to show in big letters whether they owe me money or not.

    If I could just type in a name when I run the query that would be even better instead of needing to hardcode them.

    Now I'm mainly looking at how to do reports so I can present it to them in a visual manner they'll understand.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. INSERT query: insert new data only
    By drh in forum Access
    Replies: 2
    Last Post: 04-04-2014, 05:31 PM
  2. Update from Subquery
    By JoshS in forum Queries
    Replies: 0
    Last Post: 04-28-2010, 07:45 AM
  3. SQL Subquery on Previous Record
    By GoVols02 in forum Queries
    Replies: 1
    Last Post: 01-05-2010, 02:40 PM
  4. Problem with subquery
    By bakerdenn in forum Queries
    Replies: 1
    Last Post: 04-24-2009, 10:37 PM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums