Customise Search Query

[AndreasPanayiotou]

Hello, i am new to the forum as you can guess as i sincirely have some issues with a query.
Ok here is the details, one of the many tables that i have is named Inventory.

SELECT Inventory.CategoryID, Inventory.BrandID, Inventory.ID, Inventory.FirstName, Inventory.LastName
FROM Inventory
WHERE (((Inventory.CategoryID) Like "*" & [Forms]![Inventory]![Category] & "*") AND ((Inventory.BrandID) Like "*" & [Forms]![Inventory]![Brand] & "*") AND ((Inventory.FirstName) Like "*" & [Forms]![Inventory]![Namee] & "*") AND ((Inventory.LastName) Like "*" & [Forms]![Inventory]![Surnamee] & "*"));



I call this as a customise query because end-user have four fields to fill but none of that is neccesary.
for example the fields are: Name,Surname,Category ,Brand
User can fill what ever he wants and then can click the Search button for the query to work.
My problem here is that i store info about users and about the hardwares that are related to them, but there hardware that they do not belong to any user and when the user search lets say for a Category name Keyboard and a Brand named Trust the query do not show any result.
As query will show results only in hardwares that are related to user.
I know what is the problem here, is because i have "AND" in all fields so in order the query to be corrert all fields must be true.
So if a user is not related to a hardware the query wont work.
My question here is what i can do for it ?

[orange]

If you're only looking for Inventory info related to a specific user, then how about something like

SELECT
Inventory.*
FROM
Inventory
WHERE
((Inventory.FirstName) Like "*" & [Forms]![Inventory]![Namee] &  "*") AND 
((Inventory.LastName) Like "*" &  [Forms]![Inventory]![Surnamee] & "*"));

If the user is not related to hardware, nothing will be returned from the query.

[AndreasPanayiotou]

Thanks for your reply, maybe i dident explain my self correctly.
The search must search for user who are related as also for user who are not related.
For example: as i said have four textbox/fields. , Name,Surname,Category ,Brand
if i type the name the query will show the name that i had search,the surname as also the different categorys or brands.
Now, if lets say i added a hardware the there is now user on it, that means i wont add any name or surname but i will add a category lets say keyboard and the brand would be microsoft.
The search query wont find this result even if it is true becaue i do not have any name or surname on it.
As you can read my SQL i have three "AND", that means four things must be true.

[orange]

Right I understand your ANDs.

Your question is still not clear to me.
What exactly do you want? Can you give an example with some data to show the issue?

If you have 1 table Inventory, where are you getting these "related things"? Where does becaue i do not have any name or surname on it. fit?

I think you have a design problem. In very general terms you have

People
Things/Items
Category
Brand

Seems you can Have a Thing that has a Brand and is assigned a Category, BUT may not be associated with a Person.

If that is the case, then you have a data base structure issue.
Are you familiar with Normalization?

You may get some ideas from models here
http://www.databaseanswers.org/data_models/index.htm

see http://www.databaseanswers.org/data_...sets/index.htm for some ideas

[AndreasPanayiotou]

i did normalize my table, i have a table named brand, as i have multiple items with the same name therefore i made a table of it ( 4 Normal Form) the brand table has the BrandId and Brandtype and BrandID is foreign key in the Inventory Table. Also i have a Category table that there is a categoryID and categoryType and foreign key on Inventory. The name and surname of the user is stored in the Inventory Table.

[orange]

Can you post a jpg of your tables and relationships?

Can you give an example with some data to show the issue?

I have acc2003 so can not read/use an accdb file. Do you want to post a copy of your db with no personal info?

[AndreasPanayiotou]

Dear orange, check your email please. Thank you.

[orange]

Try putting you file (mdb format) on http://www.4shared.com/

[AndreasPanayiotou]

i cant save it as mdb as i have some macro and codes that cannot be in mdb

[orange]

Ok post it to this forum ans seek help from someone with 2007 or 2010.