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.