Lookup using part of a field

[Isskint]

Hi guys

I am having a hard time figuring this one out.

I have a table - RDC/NDC - that holds details about various warehouses, including a field - [Short_Code] (TEXT) - to hold the warehouse short code or ID.

I have an import table - PickDataImport - that contains details of goods picked, including a field - ToAssignRef (TEXT) - that is made up of 3 elements; Type-Short_Code-Date (EG GREEN-MAN-210215).

I am trying to create a query that will return the warehouse name from RDC/NDC by looking up the depot short code in the ToAssignRef.

The Type element of ToAssignRef can be any length of text.

I am thinking i need to use HAVING or IN (or a combination) but everything i try fails - either blank or errors.

 

[jdraw]

Is Short_Code separated by dashes "-" in field ToAssignRef?
Are there any other fields common to tables RDC/NDC and PickDataImport?

 

[RainLover]

We need more details.

A Pic of the Relationships may help a lot.

 

[Isskint]

@jdraw
Yes, the naming convention used includes the dashes. I did try using instr() to find the location of the first dash and then get the next 3 characters. This works OK with 50% of the ToAssignRef entries (those created by a host system), however there are a large number of manually created entries and then we are reliant on the operators putting in the dashes :banghead:

@RainLover
There is no relationship between the 2 tables. Could i create a relationship on part of a field?

Just a thought (prompted by jdraws post), i could change the Short_Code in RDC/NDC to include the dashes.

 

[jdraw]

How about showing us some sample data -- both the host generated (consistent stuff) and a few of the manually entered"customized" entries.

 

[MickC]

Hi

If there are manual entries and there are fixed rules for data entry, surely you can enforce the correct input by using a mask? That way all entries will conform.

 

[Isskint]

@jdraw
As requested MoveSample.jpg. The first 6 records are host generated entries, whilst the last 5 records are typical of the manually created entries.

@MickC
My database has no control over the input unfortunately. The database is a datastore for the warehouse management system. We extract data every day into the datastore so we can produce performance and analysis reports. Have a look at Host-WMS-DS.jpg

 

[Isskint]

OK, I think I will have to get people to use the same naming convention for manual entries and then I can use the Instr() function to find the first dash and move on from there. It just felt as though there would be an easier/cleaner way to do this

Perhaps I was over complicating this?

Thanks all who took the time to post and help

 

[jdraw]

Based on your Movesample data, WHAT exactly are the values you want from these 11
sample records?
How are the manual records entered?? That is a Form or ??? you could force the format on a data entry form.
Is this a new problem? I just can't imagine that this is a production issue. I would think red flags went off after the first request for reports/stats..

 

[Isskint]

Hi jdraw.

I use all the data in one way or another, however it is the 3 characters between the dashes that identify which warehouse that record relates to. No records are created in my data store (so i have no control over data entry) , everything is imported.

Let me try and explain the set up further.

My company generates an order in our system. Multiple orders are allocated to a route for delivery. These routes are then interfaced to a specialist warehouse management system (WMS) to control all stock movements. The route is the ToAssignRef. After interfacing, extra orders or changes to orders have to be manually created in the WMS. Once picked the orders are sent out to a distribution warehouse from where they are then delivered to the customer. The next day we extract all stock movement records from the WMS into my data store.
Until recently, orders were picked by route so i could import a Route table that identified the warehouse. Now, to improve efficiency in the main warehouse, all stock required for each distribution warehouse is put in 1 pot then split by product type. This is done in the host system, which then creates the new ToAssignRef's in the format GREEN-MAN-210215. As interfaced this is consistent, when created manually I have no control over it.

Like i said in my previous post, perhaps I was over complicating this. I was sure i needed something special in a query. But i realised i just need to use Instr() and isolate the 3 characters. The issue with using the correct format on manual orders is now a question of discipline.

 

[jdraw]

Glad you have it sorted.
Discipline is an interesting animal. You can probably have some validation code to assist or possibly reject for further study those manually entered records that suffer "lack of discipline".
Good luck.