how do I do this? (1 Viewer)

[shrndegruv]

I have a query on two tables with a one to many relationship.

I would like to write a query that limits the results based on fields in both tables, AND that only returns one row, regardless of how many rows are in the "many" table of the 1-many relationship. This is not an inner join or an outer join (both can return many rows if there are more than one row in the many table).

The tricky part is if there are no rows in the many table and there is a row in the one table that meets the requirement.

Do I need an intersect or union? Do I need multiple queries?

Im thinking the first option

Select *
FROM one-table
WHERE paramaters1
UNION
Select *
FROM many-table
WHERE parameters2

The question is will this return a row in the one-table more than once?

thanx
mike

 

[Pat Hartman]

For starters you can't union recordsets that are not alike. If the columns in the 1-side table are different from the columns in the many-side table (and I presume they are) the union will not work. A union query unions LIKE recordsets.

If the recordset does not need to be updatable, you can use a totals query and return the First() value for each column from the many-side table.

 

[namliam]

If you are trully working with a 1-many relationship you MUST use a join, all else will fail.

Try doing an outer join and using a group by statement.

If that fails you may need to use a join on a sub query (a second query that reduces the many part in the relationship to a 1, using a group by offcourse)

Regards & GL

 

[shrndegruv]

Im still having trouble on this--

can either of you (or someone else) please provide a psuedocode example?

 

[Pat Hartman]

If you are unfamiliar with SQL syntax, let the query builder build the query.
1. Create a new query and add the 1-side and many-side tables.
2. Draw the join line if it is not automatically drawn for you.
3. Change the join type to left so that all rows are returned from the 1-side table and any matching rows from the many-side
4. Choose the columns you want from both tables.
5. Apply the selection criteria.
6. Press the sygma button to make this a totals query. Access will add "group by" as the total field. That is OK for the 1-side fields but you'll need to change the many-side to "first" instead.