Help on query (1 Viewer)

[ScottXe]

I have a table of database containing all the model related information. The model field in the table may consist of one or more model nos (same product but with different brand and model no, such as:

123456
234567, 345678
456789, 567890, 234567

Now I received a list of orders that contains the order nos, models and quantities. Each order no contains one model and one quantity. I am required to pull the model related information from the table according to this list given. Can someone advise how to construct the query to pull the required info from the table. Thanks!

 

[plog]

Yes, but you can't get there from where you are. You need to properly structure your Model table so that each discrete piece of data is in its own field. You shouldn't store multiple model numbers in the same field. You need a new record for each one.

Once your table is like that, this is a trivial query.

SELECT *.ModelTable
FROM ModelTable
INNER JOIN ModelList ON ModelList.ModelNumber = ModelTable.ModelNumber;

 

[ScottXe]

plog, thanks for your code, it works exactly right.

However I cannot change the field of multiple model numbers as it works very well in our simple and efficient search query. Is it possible to select the data in the model table if the model no of list table matching part of model no in model table?

 

[plog]

Is it possible to select the data in the model table if the model no of list table matching part of model no in model table

I don't understand what you are asking.

 

[ScottXe]

For example

Model field of Model Table Model field of Order Table
123456, 234567 123456

As long as the Model field of model table contains 123456, the data in that record cannot selected in the query. Currently these two fields must be 100% matched and then the data on that record can be selected. If they match partially, it is considered match similar to the LIKE operator in query.

 

[MSAccessRookie]

I can't believe that I am saying this (since it violates proper normalization rules, and I am still not sure what you want to do), but did you try to use the Instr() Function to locate the Model Number? Once you search your data for the Model Number, you can determine the best way to use your results.

-- Rookie