I hope that subject line isn't too bizarrely confusing.
So I have a query that selects data from a table (Design Hours) based on the designers' manager. The manager number is gathered by a form. I am using the Query Designer and this column has:
Field: Managers_ID: ID,
Table: Managers
Criteria: [Forms]![SelectManagerAndDateRange]![ManagerNumber]
The Managers table has "ID" as its Unique Identifier.
The only flaw in this query is that it doesn't include any Design Hours that the manager him/herself has worked. In the Query Design View, can I add a second criterion for additional records to be added? I know how to further filter down the result, but in this case I want to add more. I can look up the PersonID of the Manager in question in the Managers table, but then I have look up all records in which Person = that personID in the Design Hours table.
I'm using Access 2003. Yes, I know. We plan to upgrade to 2010 later this year. Data is saved as an MDB.
Any help will be greatly appreciated.
EDIT: screencap as requested
A general answer that may help you: If the return from both queries have the same number of fields, the simplest way is to use a union statement.
[SQL Query 1]
UNION
[SQL Query 2]
[Order by Statements]
Note that when using Union the name of the fields in the second query does not matter as long as you have the same amount as the first query and they are the same type. When ordering, use the field names from the first query.
The UNION query is intended to be used when you want to retrieve data from different tables but with the same structure.
In your situation, I think the best solution is a JOIN query, like
SELECT M.field1, M.field2, M.field3, H.field1, H.field2 ...whatever fields you need
FROM Managers as M
LEFT OUTER JOIN [Design Hours Table] as H
ON H.Person = M.PersonID
ORDER BY ... whatever order you want
You can use this query as RowSource for a report, telling Access to group the results by all the fields in the Managers table.
But if you are using that for a form, then you can use a Form/Subform combination.
You set your original query as the RowSource of the form, and design it with all the desired fields and labels. Then you insert a SubForm,
and tell Access to use an existent table or query for the subform data, then choose the Design Hours table for that, and select to show records in Design Hours for each record in Managers using the field PersonID.
Now, there is another issue I wanted to comment: You have a PersonID field in the Managers table, which is correct, since a manager IS A person. What is incorrect in your design is the existence of a FirstName and LastName columns in the Managers table (unless it is not really a table, but a query). The reason is that the PersonID defines a set of properties, FirstName and LastName being among them. So, with your design, you could end up with a manager having PersonID = 2013, FirstName = John, LastName = O'Connor and the ID 2013 in the People table referring to FirstName = Peter, LastName = Sellers, which would be an inconsistency.
Also, you don't need an ID in this table, since the field PersonID is best fitted to serve as PK for this table.
You retrieve the Name and SurName from the People table, so the join would be something like
SELECT M.field1, M.field2, M.field3, P.FirstName, P.LastName, H.field1, H.field2 ...whatever fields you need
FROM (Managers as M
INNER JOIN People as P ON P.ID = M.PersonID)
LEFT OUTER JOIN [Design Hours Table] as H
ON H.Person = M.PersonID
ORDER BY ... whatever order you want
Regards,