Simple query question - I hope 1

[puppet]

I have two tables. One called Assets with the following fields:

AssetID - Primary Key
EmployeeID
AssetCategoryID
StatusID
Model
Barcode
PreviousUser1
PreviousUser2

One called Employees with the following fields:

EmployeeID - Primary Key
Name
Location

I want a query to return asset information based on entering the partial name of a previous user and it searching for it. For example when I run the query it prompts me for the users name and if I enter Mark it returns all assets that have previously been owned by any Mark in the company.

My limited Access knowledge however can only get it to work by entering in the employeeid number not the name associated with it!

Can anyone help me please?

Thanks

 

[Remou]

Hi
This query may suit (SQL view):

SELECT Employees.Name, Assets.AssetID, Assets.EmployeeID, Assets.AssetCategoryID, Assets.StatusID, Assets.Model, Assets.Barcode
FROM Employees INNER JOIN Assets ON Employees.EmployeeID = Assets.EmployeeID
WHERE (((Employees.Name) Like "*" & [Enter Name] & "*"));

 

[puppet]

Remou - thanks for the quick reply but that only gives me the assets that the username that I input Currently owns... I want to search for an asset that was Previously owned by someone...

Any other ideas?

 

[Remou]

Sorry, not paying attention. Do you need the information from both these fields: PreviousUser1, PreviousUser2? Do both these fields show an EmployeeID? If so, you could try:

PARAMETERS EmpName Text ( 255 );
SELECT Assets.AssetID, Assets.EmployeeID, Assets.AssetCategoryID, Assets.StatusID, Assets.Model, Assets.Barcode, Employees_1.Name, Employees.Name
FROM Employees AS Employees_1 INNER JOIN (Assets INNER JOIN Employees ON Assets.PreviousUser1 = Employees.EmployeeID) ON Employees_1.EmployeeID = Assets.PreviousUser2
WHERE (((Employees_1.Name) Like "*" & [EmpName] & "*")) OR (((Employees.Name) Like "*" & [EmpName] & "*"));

 

[puppet]

This seems to give me no results...

Both PreviousUser1 and PreviousUser2 have an EmployeeID in them if the asset was previously used by someone else. If the asset has always belonged to the one user then PreviousUser1 and PreviousUser2 simply has 0 (zero) in them.

Even two queries - one searching PreviousUser1 and another searching PreviousUser2 would be fine if I could get that to work...

Thanks for your help Remou.

 

[Remou]

If you are not getting results, I have not understood your set up.
However, let's try searching PreviousUser1, to see where I have gone wrong.

SELECT Assets.AssetID, Assets.EmployeeID, Assets.AssetCategoryID, Assets.StatusID, Assets.Model, Assets.Barcode, Employees.Name
FROM Assets INNER JOIN Employees ON Assets.PreviousUser1 = Employees.EmployeeID
WHERE (((Employees.Name) Like "*" & [EmpName] & "*"));

A window should open asking for EmpName. As the query has [tt]Like * & [Empname] & *[/tt], entering "ar" should get you any employees called, eg, Mark or Mr Quark etc. I have assumed that Employees.Name contains something along these lines.
As a by-the-way, calling a field Name is not recommended, because Name is a keyword.

 

[puppet]

That works perfectly! Thanks!

If I could get it to search both fields that would be ideal but not absolutely needed. I will have to use this query very rarely I hope...

Thanks again.