how to join the table with query based on 'like' clause

[varadha_72]

Hi,
Can someone guide me here please and suggest how I can achieve this in Access

I have a table created in Access- "Master"
FileName Sortorder
ABC_ ..........4
CDE_ ..........2
EFG.ss1.......1
GHI.srs........3 …

I have a Query created in Access whose output is
FileName RowCount Exception RunDate
ABC_20141117.....10...........5........11/17/2014
CDE_11172014......23.........10.......11/17/2014
EFG.ss1................55..........0........11/17/2014
GHI.srs.................15..........5........11/17/2014

Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.
Please advise how I can achieve this. There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.

Required Output:
FileName RowCount Exception RunDate SortOrder
EFG.ss1...............55............0.......11/17/2014...........1
CDE_11172014.....23...........10......11/17/2014......... 2
GHI.srs................15............5.......11/17/2014..........3
ABC_20141117.......10...........5......11/17/2014..........4

 

[jdraw]

Try this as a start (untested)

SELECT
query.Filename
,query.rowcount
,query.exception
,query.runDate
,Master.sortorder
from
Master Inner Join Query 
ON Master.Filename = Left(query.filename,3)

 

[varadha_72]

Thanks for the reply, but I cannot use Left function and also cannot validate string for 4 characters, since each and every filename is different in last few char only, and some of the file names have similar 5-9 char.

 

[jdraw]

Have you considered applying some data standards for naming conventions?
Is this something you are designing or have designed?
This seems to be something that might have been considered in design, or during testing.
If the above are no "doable", then I recommend you provide better sample data.

 

[Galaxiom]

ON query.filename Like "*" & Master.filename & "*"

 

[BigHappyDaddy]

I had a very similiar requirement. If I remember right, the query below worked, but it was very slow.

SELECT
query.Filename
,query.rowcount
,query.exception
,query.runDate
,Master.sortorder
WHERE query.filename LIKE Master.Filename*

The LIKE statment isn't exactly right, but maybe you will get the idea. I ended up not using this method because of the size of my tables (300,000 and 1,500,000 records) made this solution slow.

 

[jdraw]

Good point guys(Galaxiom/BigHappyDaddy)
Varadha,
I think since you know the starting chars that you would use the technique from BigHappyDaddy. However, if nameA can embedded within nameB, then galaxiom's approach should be used.

I don't think the LIKE will make use of any indexing.

Good luck with your project.

 

[namliam]

SELECT
query.Filename
,query.rowcount
,query.exception
,query.runDate
,Master.sortorder
from
Master Inner Join Query 
ON Master.Filename = Left(query.filename,len(master.filename))

And neither Like or this above Left construct will use an index.

The best way forward though, depending on where the source data from your query comes from is to populate a proper key / foreignkey field between the query and the master table on creation of the file entry.
I.e. add an auto number to your master table and make sure to fill the key/foreign key when the file is processed.

 

[varadha_72]

Thanks Access Gurus, hats off to you all for your help. I was able to achieve my result based on Galaxion's suggestion. I will be needing another help from you guys.

I need to put some kind of counter for the instance of filename occurrence. I.e. If you notice in the below example, in row number 2&3 and 5&6, the file names are the same, the only difference is the row_count and exception. Hence All I need is if the file_name is same and the row_count and/or exception is different in both instances then based on time of occurrence the first record should show counter as 1 and second record as 2. Rest of the records should show distinct records.

No..FileName....... RowCount.. Exception.. RunDate.............. SortOrder....Counter
1....EFG.ss1...............55............0.......11/17/2014.19:05...........1.............1
2....CDE_11172014.....23...........10......11/17/2014.19:06......... 2..............1
3....CDE_11172014.....15...........1.......11/17/2014.19:07......... 2...............2
4....GHI.srs................15............5.......11/17/2014.19:07..........3...............1
5....ABC_20141117.......10...........5......11/17/2014.19:08..........4...............1
6....ABC_20141117......5..............2......11/17/2014.19:09..........4..............2

 

[varadha_72]

Can someone PLEASE help me with the above request. I need to get this done.

 

[namliam]

How are you filling this table?

You can research DCount, if you already have this table and want to do a query on it...
Something along the lines of
Dcount("[Filename]", "[TableName]", "[Filename] = """ & [filename] & """ and [Rundate] < #" & format([rundate], "MM/DD/YYYY HH:MM:SS") "#")

Note that using this DCount solution is pretty slow! You will probably be better off filling this at the moment you fill the table but I dont know how you do that, hence the first question