One to many summary problems

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

Or in SQL view change the words INNER JOIN to LEFT JOIN.
--
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
"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.

 

[N. Byron Griffin]

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.

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.

 

[N. Byron Griffin]

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.

Or in SQL view change the words INNER JOIN to LEFT JOIN.
--
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
"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.

In your query, try grouping by batch.