Multi Field Query

[dsthome]

Hi I am trying to write a query that will search 5 fields which may have the same refernce in it and return the information. I am currently trying

Like "*" & "[Enter Search criteria]" & "*"

in all 5 fields in the query, it only appears to give me a result if the search criteria appears in the first field and not the other 4, could you please let me know where I am going wrong

Thanks

[JoeM]

It actually sounds like you have a database design issue. If you have to search 5 different fields for the same item, your table probably isn't normalized, making searches like this harder than they have to be.

In your current situation, if you try to use a parameter query, I think you will need to place that criteria under each of the five fields, under a different criteria row. That means that every time you run it, the person will have to input the value they want 5 times, one for each field!

You could input the value you want to search on a Form, and have the criteria under each of the 5 fields reference that one Form input. However, if it is at all possible, I would suggest normalizing your table. If you post the structure of your table, and maybe some sample data, we can help you with that.

[dsthome]

Hi I think I have attached the file. What I am trying to get at is being able to place an order then pick part quantities and create a despatch note from the reference added, this could be from multi orders.

Hope this is clear.

Thanks

QUOTE=JoeM;164269]It actually sounds like you have a database design issue. If you have to search 5 different fields for the same item, your table probably isn't normalized, making searches like this harder than they have to be.

In your current situation, if you try to use a parameter query, I think you will need to place that criteria under each of the five fields, under a different criteria row. That means that every time you run it, the person will have to input the value they want 5 times, one for each field!

You could input the value you want to search on a Form, and have the criteria under each of the 5 fields reference that one Form input. However, if it is at all possible, I would suggest normalizing your table. If you post the structure of your table, and maybe some sample data, we can help you with that.[/QUOTE]

[JoeM]

Unfortunately, I do not have the ability to download files from my current location (corporate security policy).

If you can post a small sample or screen print, that would be great. Otherwise, hopoefully someone else will be able to take a look at it, or it will need to wait until I am at home tonight and can download your file.

[JoeM]

I tried to download your file, but it appears to be in a more recent version of Access than I have and I am unable to read it.
If you can just describe the structure of the table/query in question, and post a screen print of sample data, that might work.

[June7]

Why does Products table insist on being in the Relationships builder twice? Why not Customers in the Relationships builder?

Your data structure is not normalized. However, change the filter criteria to:
Like "*" & [Enter Search criteria] & "*"

Note that I removed the quote marks from around the input parameter.

Also need to have each criteria on a different row so the operator will be OR not AND.

[dsthome]

Hi I have attached another file with the changes you suggested, but still not getting the information back I was expecting when I create Despatch Note. Help.....

Thanks

Why does Products table insist on being in the Relationships builder twice? Why not Customers in the Relationships builder?

Your data structure is not normalized. However, change the filter criteria to:
Like "*" & [Enter Search criteria] & "*"

Note that I removed the quote marks from around the input parameter.

Also need to have each criteria on a different row so the operator will be OR not AND.

[dsthome]

Update, I think I am going in the right direction now, the problem I have now is to return the qty associated with the search criteria..... any ideas?

Hi I have attached another file with the changes you suggested, but still not getting the information back I was expecting when I create Despatch Note. Help.....

Thanks

Why does Products table insist on being in the Relationships builder twice? Why not Customers in the Relationships builder?

Your data structure is not normalized. However, change the filter criteria to:
Like "*" & [Enter Search criteria] & "*"

Note that I removed the quote marks from around the input parameter.

Also need to have each criteria on a different row so the operator will be OR not AND.

[June7]

Don't understand.

Return what quantity from and to where?

[dsthome]

If the criteria search finds one of the fields that has that criteria in it I need it to return the quantity in the field that is associated with the result. Don't know if this is possible. I am trying to part despatch orders and be able to create a delivery note for the search driteria reference. Not having much Luck

Thanks for your patience and help

Don't understand.

Return what quantity from and to where?

[June7]

Your query has the criteria under the Ref fields. There is no data in these fields. Should all of these fields have data for every record? No records return because all of the Ref fields are Null.

Still don't understand what you mean by 'return the quantity'. If records are retrieved, then the QtyOrdered field is there.

Just what is a 'despatch'? Should this be 'dispatch'?

What does 'part despatch' mean? Create a delivery note where?