Defining query specifics

[roger751]

I've got a db on the go and am stuck on one thing. Its an inventory db with PC hardware, dataports, employees and departments. Im able to make querys with all departments and dataport but how do I create a query that prompts me for which department I want listed and then shows the results by department name, with employee names and data port displaying also. How, if I wanted to show all, do I insert an option to select all departments and then point it to a different qeury to display all those results.

Using Access 2K3
Thanks,

 

[Pat Hartman]

shows the results by department name, with employee names and data port displaying also

- Are you asking how to join these tables?

Your where clause should look something like:
Where DeptName = Form!YourForm!DeptName OR IsNull(Form!YourForm!DeptName)

The form will need to be open when you run the query. The criteria selects all rows where the DeptNam matches the one chosen on the form or selects all departments when the form field is null.

 

[roger751]

Ok, now Im a bit confused. This is what I put:

Where DepartmentName = [Forms]![Department Form]![DepartmentName] Or IsNull([Forms]![Department Form]![DepartmentName])

and that didnot work.

Here is some more info

I have a total of 12 departments. 2 are called "ARCH" & "Administration"
I have a form called "Department Form"
Can you retype the Where expression I should use please?


Also, is there a way I can create a query that prompts me to select a department from a drop down list of current departments, to display whatever field I may need? IE data port, PC model etc. I already have the dept form created.

 

[Pat Hartman]

and that didnot work

- not a very helpful statement.

Did the query run and produce too many rows?
Did it produce too few rows?
Did it generate an error and not run at all?

 

[roger751]

Sorry opps

Came up with

Enter Parameter Value
Forms!Department Form!DepartmentName
With an empty box to enter info

Regardless or what I type, after clicking ok, I get 2 fields of Data Port & Employee but with no records displaying

Hope this helps

 

[Pat Hartman]

You have a typo. Make sure that the form field name is typed correctly.

 

[roger751]

Im completely stumped. I see no typo what so ever. I've changed the expression to
[Specific Department Query]![Department]
and after that, I get a complete list of all departments and data ports but still unable to figure out how to get a drop down list to prompt me what department records I want to display only, as opposed to them all.

Now, I have a department called ARCH. How can I just choose fields under that department name to appear?

 

[roger751]

I figured out another method. What I did was create a query for each department. Because I created the departments table as a lookup, the primary key was pointing to tbl DepartmentsID field and not the DepartmentName field. So what I did for the expression was made the criteria a number that related to the DepartmentName and did that for each department.

Thanks for all your help Pat