access 2003 Where?

[assets]

I have the following as the data source on a form.

SELECT Assets.AssetID, Assets.AssetDescription, Assets.EmployeeID, Assets.AssetCategoryID, Assets.StatusID, Assets.VendorID, Assets.Make, Assets.Model, Assets.ModelNumber, Assets.SerialNumber, Assets.BarcodeNumber, Assets.DateAcquired, Assets.DateSold, Assets.PurchasePrice, Assets.DepreciationmethodID, Assets.DepreciableLife, Assets.SalvageValue, Assets.CurrentValue, Assets.Comments, Assets.Description, Assets.NextSchedMaint, Employees.DepartmentID, Assets.YesNoID, Assets.[Loaned Out], Assets.DivisionID AS Expr1, Division.DivisionName, Division.DivisionID, Assets.AvailableID, Assets.Updates, Assets.[Printer Driver], Assets.[Printer PEO No], Assets.Audit, Assets.[Port No], Assets.Account, Employees.OfficeLocation, Assets.[Warrenty Expires] FROM Division RIGHT JOIN (Departments RIGHT JOIN (Employees RIGHT JOIN Assets ON Employees.EmployeeID=Assets.EmployeeID) ON Departments.DepartmentID=Employees.DepartmentID) ON Division.DivisionID=Departments.DivisionID ORDER BY Assets.StatusID;

I have a query that has that has two fields logedin from employees and divisionID from division the critera is logedin =1

What I want to do is use the query to filter the data source above to only show records for the divisionID records

So how do I added where divisionID from queryone

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

What is "queryone"?
How many queries are you talking about?
I can't make heads or tails from your description.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

sorry Duane
Queryone is query that has that has two fields (logedin from employees) and (divisionID from division) the criteria is logedin =1 It display the divisionID when logedin =1. Users login from the login form and this set logedin in employees for that user to 1. I trying to filter the records. This has come from your help with another thread, where you suggested using this approach. It work well for the employee form . But the asset form as you will see has three table. I tying to stop users from other division seeing the record from other divisions. That's why with the above code and using the divisionID value from the queryone to filter the records. Hope this explain the problem better.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

Does the form's record source need to be updateable?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Somthing like this ?

SELECT Assets.AssetID, Assets.AssetDescription, Assets.EmployeeID, Assets.AssetCategoryID, Assets.StatusID, Assets.VendorID
, Assets.Make, Assets.Model, Assets.ModelNumber, Assets.SerialNumber, Assets.BarcodeNumber, Assets.DateAcquired, Assets.DateSold
, Assets.PurchasePrice, Assets.DepreciationmethodID, Assets.DepreciableLife, Assets.SalvageValue, Assets.CurrentValue
, Assets.Comments, Assets.Description, Assets.NextSchedMaint, Employees.DepartmentID, Assets.YesNoID, Assets.[Loaned Out]
, Assets.DivisionID AS Expr1, Division.DivisionName, Division.DivisionID, Assets.AvailableID, Assets.Updates, Assets.[Printer Driver]
, Assets.[Printer PEO No], Assets.Audit, Assets.[Port No], Assets.Account, Employees.OfficeLocation, Assets.[Warrenty Expires]
 FROM Division RIGHT JOIN (
Departments RIGHT JOIN (
Employees RIGHT JOIN Assets ON Employees.EmployeeID=Assets.EmployeeID
) ON Departments.DepartmentID=Employees.DepartmentID
) ON Division.DivisionID=Departments.DivisionID
[!]WHERE Employees.logedin = 1[/!]
ORDER BY Assets.StatusID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[assets]

Thanks but that only displays assets for the employee that logged in. I need all assets from that division the person is from, that why I tried tne query that did not work. Can I set a new field in asset table and say if Employees.logedin = 1 then this new field is 1 and use this in the where statement. Would something like this be better. Thank you for your prompt reply it is appreciated.


Never give up never give in.

There are no short cuts to anything worth doing

 

[PHV]

What about replacing this:
ON Employees.EmployeeID=Assets.EmployeeID
with this ?
ON Division.DivisionID=Assets.DivisionID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886