MS-Access 2003: how to combine query criteria that depend on different tables

Go To StackoverFlow.com

0

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 Relationships and criteria as mentioned in question. Circled criterion is not working as desired.

2012-04-05 22:15
by CarlF
Please show us the SQL text of your query - Olivier Jacot-Descombes 2012-04-05 23:41
...or, just a screenshot of your tables' relationships (from Query Designer window). 3 tables, I guess - Igor Turman 2012-04-06 00:51
@IgorTurman, question edited to add screencap. And thanks - CarlF 2012-04-06 12:47
@CarlF, I have answered your question cause I don't really think that UNION is a good choice for yo - Clon 2013-08-14 09:30


1

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.

2012-04-06 01:30
by Daniel
I was hoping to avoid learning SQL. I appreciate the answer but this is not my full-time job and I don't necessarily want to make it one - CarlF 2012-04-06 12:39
Figured it out. I used the GUI to design a second query that used DLookup to find the hours worked by a manager, then created a third query and manually copied the SQL from both the others in, separated by UNION. This didn't require me to actually understand SQL and it works. In the end Daniel's answer did solve my problem. Thanks - CarlF 2012-04-06 18:13


0

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,

Adding 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,

2013-08-14 09:28
by Clon
Ads