Unique Records

[Guest]

I have 2 tables reservation say 200 records and reservation details say 1200
records. Res id is the key field When I build a query with just the
reservation table no problem. when I add the detail table the query pulls
all 1200 records. which has maybe 25 detail records for each reseervation.
It should be pulling the detail for each reservation. say amt paid not 25
lines showing amt paid.

 

[Tom Ellison]

Dear Sandy:

The type of query you need is called an "aggregate" or "totals" query.

In the Design View, find the "sigma" in the row of icons at the top (it
looks like M but on its side). When you click this, a new row of parameters
will appear across the grid called "Total:".

In this row, select Group By for any column you wish to show from the
"reservation" table. Choose SUM for the amt paid column. If you have any
other columns you want to show from the reservation details, then I don't
know what you want to choose for them. It depends on how you want to
"aggregate" them. You cannot just show them, as you don't want all 25 or so
rows showing. You have to choose some way of aggregating them for this to
make sense.

If there are other columns from the details you want to include, please
explain what you want to have shown. If there are 25 of them, and you want
to show something in the query without showing all 25 rows, then what is it
you want to show in the one row of results for such columns?

Tom Ellison

 

[Guest]

Tom

That help a lot, but I'm confused why the res id is only picking up 80
reservation id's out of 170 some. each Res id is a unique autonum, I'm using
group by, the other fields I have added to the query seem to be fine some
are group by and some are sums

Why wouldn't it pull all the res id's

 

[Tom Ellison]

Dear Sandy:

If you do not use the proper style of JOIN between the tables, you can get
the effect you describe. Is it the case that you have an INNER JOIN it will
show only those rows from the reservation table that also have rows in the
reservation details table. Does this accurately describe which reservation
rows are not being shown?

To change this in the Design View, right click on the line connecting the
two tables and select Properties. The rest is pretty much explained in what
comes up. Make the proper change and run the query again. Is that the fix
you needed?

Tom Ellison