One to many summary problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ryan - 09 Apr 2007 21:44 GMT

Im not sure exactly how to explain this, so Ill be as detailed as possible.
I have my relationship set up like this: "Batches" 1-------many
"Discrepancies". The "Discrepancies" table has a field named [Amount] that I
need to add to several other fields in the "Batches" table. The problem is
that I want to show all records in the "Batches" table, but when I make the
query it only shows records that have a related "Discrepancy" in. Is there a
way to show all of the records in the "Batches" table if there is or is not a
related recordset in the "Discrepancies" table?? The related field is
[BatchID] if that helps. Thank you in advance for any help.

Reply to this Message

Jerry Whittle - 09 Apr 2007 21:50 GMT

Yep. In the query design view click on the thin line between the two tables
untill a dialog box opens. Select the second option.

Or in SQL view change the words INNER JOIN to LEFT JOIN.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Im not sure exactly how to explain this, so Ill be as detailed as possible.
> I have my relationship set up like this: "Batches" 1-------many
[quoted text clipped - 5 lines]
> related recordset in the "Discrepancies" table?? The related field is
> [BatchID] if that helps. Thank you in advance for any help.

Reply to this Message

Ryan - 09 Apr 2007 22:00 GMT

There can be multiple "Discrepancies" [amounts]. What would be the best way
to get the total for the [amounts] field on one recordset for the "Batches"
so that I dont have multiple counts of the same batch? Would I just run a
summary report on the Left Joined query? Thank you very much for your helps
so far.

> Yep. In the query design view click on the thin line between the two tables
> untill a dialog box opens. Select the second option.
[quoted text clipped - 10 lines]
> > related recordset in the "Discrepancies" table?? The related field is
> > [BatchID] if that helps. Thank you in advance for any help.

Reply to this Message

N. Byron Griffin - 09 Apr 2007 23:10 GMT

> There can be multiple "Discrepancies" [amounts]. What would be the best way
> to get the total for the [amounts] field on one recordset for the "Batches"
[quoted text clipped - 19 lines]
>>> [BatchID] if that helps. Thank you in advance for any help.
>>>

In your query, try grouping by batch.

Reply to this Message

N. Byron Griffin - 09 Apr 2007 23:09 GMT

> Im not sure exactly how to explain this, so Ill be as detailed as possible.
> I have my relationship set up like this: "Batches" 1-------many
[quoted text clipped - 6 lines]
> [BatchID] if that helps. Thank you in advance for any help.
>

Use a Left Join. It will cause all the records in the left table to
show up and leave blanks where the right table has no information.

Reply to this Message