Conditional Sum with Two Tables?

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Hi all,

My brain hurts today (as it does most :-) ). If someone would help me here
I would really appreciate it.

I have two tables - tblBox and tblBatches. In tblBox there are fields
denoting the range of batch numbers in the box. They are named
[StartingBatch] and [EndingBatch]. In tblBatches we have the data for each
batch. Specifically, it contains [BatchNumber] and [RecordCount].
[RecordCount] is the number of records in each batch.

Now, what I want -- in English is: The total number of records in each box.
AKA: Sum of [RecordCount] for each box in tblBox where [BatchNumber] is
BETWEEN [StartingBatch] AND [EndingBatch].

Any help is GREATLY appreciated. Thanks

Sean
 
Place both tables in the query and don't create a join line. Place the
[BatchNumber] field in the grid and set its criteria to:
Between [tblBox]![StartingBatch] AND [tblBox]![EndingBatch]
Add the [RecordCount] to the grid, make it a totals query and sum the
RecordCount field.
 
Hi,

Thanks. It worked perfectly. I was making much harder than that. Once
again the bluntly obvious rules the day.

Sean


Duane Hookom said:
Place both tables in the query and don't create a join line. Place the
[BatchNumber] field in the grid and set its criteria to:
Between [tblBox]![StartingBatch] AND [tblBox]![EndingBatch]
Add the [RecordCount] to the grid, make it a totals query and sum the
RecordCount field.

--
Duane Hookom
MS Access MVP


Sean said:
Hi all,

My brain hurts today (as it does most :-) ). If someone would help me here
I would really appreciate it.

I have two tables - tblBox and tblBatches. In tblBox there are fields
denoting the range of batch numbers in the box. They are named
[StartingBatch] and [EndingBatch]. In tblBatches we have the data for each
batch. Specifically, it contains [BatchNumber] and [RecordCount].
[RecordCount] is the number of records in each batch.

Now, what I want -- in English is: The total number of records in each box.
AKA: Sum of [RecordCount] for each box in tblBox where [BatchNumber] is
BETWEEN [StartingBatch] AND [EndingBatch].

Any help is GREATLY appreciated. Thanks

Sean
Click to expand...
Click to expand...
 
Back
Top