Using Like operator to link tables

[WSC]

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.

 

[KeithG]

Can you post some example data from each table that should join together?

 

[WSC]

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?

 

[KeithG]

Are all the itme fields in the Oracle table in the same format

 

[WSC]

yes, text.

 

[KeithG]

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

 

[WSC]

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

 

[KeithG]

Can you post an example of you db?

 

[WSC]

The info is confidental so i cant post it. anything I can try to troubleshoot it?

 

[KeithG]

Can you post your db with some fake example data in the exact same format as the real data?

 

[WSC]

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.

 

[KeithG]

Is this what you want?

 

[WSC]

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