One-to-many join query limited to one row per row in the One side

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to figure out two related things:

1. If I have a query that is a join (one-to-many) of two tables which I use
to produce a report, but then I decide I want to use the query also as the
basis for a report where I just want to return one row for each record in the
main (one) table, is that possible? If it is, what 'criteria' needs to be
set to get that result?

I hope that makes sense...
CW
 
If you want to get one record out of a one to many relationship, you
have to do something with the "many" side to make it fit in one result
record. Create a summary query, and sum, first, last, Average, etc. the
fields in the "many" table that you need to see.

You could build a crosstab query as well, which is also a type of
summary query.
 
If I understand you correctly you want all the records from the many-side but
want to DISPLAY the one-side only once.
You can do this in the query. You do it in the report by changing the
one-side text boxes property Hide Duplicates to Yes.
 
Thanks for the replies,

You're close Karl, and maybe I can use what you told me to get to the goal
if it's possible. What I have is a report based SOLELY on the one side of
the join. None of the fields of the many side are actually in the report. I
use the many side fields in OTHER reports based on the query, but I'm trying
to figure out if I can get a report based solely on the one side, with one
row per row in the one side table, without having to actually change the
underlying query (read: I don't mind using vba to manipulate the query or
change properties or whatever, I just want the original query to remain as is.

Anyway, you've both given me things to consider and I do appreciate it.
CW
 
IF you want to display just the one side in the report. Set up a group and
include only the one-side fields (controls) in the group. Set the detail
section to Not visible. Although the detail section will process, it won't
print or show up.

I use this technique and some code in the on-open event of the report to
allow the same report layout to generate a summary report or to generate a
report with details.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Excellent John, thanks!

That sounds like it not only gets me where I was going, but also adds
another nice 'twist' to it as well!

CW
 
Hi Karl,

It just strikes me as more efficient to use one query to produce two reports
than to use two queries to produce the same two reports (all other things
being equal).

Besides, when I know a query has the data I need then it's irritating not to
know how to extract it :)

CW
 
Back
Top