Using Like operator to link tables

WSC

Registered User.
Local time
Yesterday, 19:02
Joined
Nov 16, 2006
Messages
19
I have 2 tables, an Oracle source table and an Access table. The data in the Oracle table has Item names, however they tend to end with month and date and other misc info. I set up the Access table with the cleaned up versions of the Item names (took out the excess crap) to be able to set up a query that would pull in data from the Oracle table that had Item descriptions Like the descriptions in the Access table. That is working great, however, i would also like to pull in another field from the Access table to categorize these items in to groups. Once I do this, each record shows up 4 times (the number of categories that exist). How do I link these tables to allow me to do this? None of the standard link types work since the fields are not equal but are Like each other.
 
Can you post some example data from each table that should join together?
 
Access table info...
Description = Operating Reserves
Category = Reserves
Charge/Credit = Charge

Oracle table info...
Item = Operating Reserves - April 2007
Amount = $10
Item = Operating Reserves - May 2007
Amount = $50

I want a query that pulls in both Items from the Oracle table based on the single, similar Description in the Access table along with the Category and Charge/Credit fields from the Access table. Then I want to sum the Amounts by the Category.

is this enough info?
 
Are all the itme fields in the Oracle table in the same format
 
Try something like the below FROM clause in your query

From AccessTable Inner Join OracleTable On AccessTable.Item= left(OracleTable.Item,(instr(OracleTable.Item," -")-1))
 
This is my From Clause and I am getting an "Invalid procedure call" error.

FROM [Bill Info] Inner Join [PJMBPRD_V_RES_MONTHLY_BILL] On [Bill Info].[Description]= left([PJMBPRD_V_RES_MONTHLY_BILL].[ITEM],(instr([PJMBPRD_V_RES_MONTHLY_BILL].[ITEM]," -")-1))
 
The info is confidental so i cant post it. anything I can try to troubleshoot it?
 
Can you post your db with some fake example data in the exact same format as the real data?
 
i have attached a sample of the data, please let me know if you have any questions. my issues are with Query2, it is bringing in extra sets of the data as soon as I try to add fields from 2 different tables. I need to have the total amount grouped by the Category field by the org id.
 

Attachments

Yes! Let me put the same logic in the real database and see if it works. Thank you so much!
 

Users who are viewing this thread

Back
Top Bottom