Group Totals

[GJT]

I have a contract number which is a five digit code. For phased deliveries i.e. where items in the same order are to be delivered on different dates, the contract has an additional identifier (1-9) to identify the batch.

i.e. 31022 (Base contract)
310221 (1st phase)
310222 (2nd phase)
etc......

I am trying to write some SQL to return the total invoiced quantity count for these contracts ie. for 31022 + 310221 + 310222 etc.

So, for :
Jobnumber InvQty
31022 10
310221 1
310222 1

Hence, the output could be :

Jobnumber InvQty
31022 12 (note : the base contract only is displayed but the count includes all subsequent contracts)

I am not too sure how to write the appropriate code to do this. Any ideas? Can you help?

Thanks

 

[FoFa]

Is this numeric or text data type?
Is the Phase a seperate field or just added to the contract?
I don't understand this example:
31022 10
310221 1
310222 1

Why is there 10 next 31022, do you mean there 10 total entries for 31022 with all it's subphases even if you only showed 3 in your example?

 

[GJT]

As posted earlier.....( it is difficult to tabulate data on this page - when posted to the forum the formatting gets lost!)


It is a numeric data type. NOT a string. The 10 is the no. units invoiced to base contract 31022, 1 unit invoiced so far to phase 1 contract and 1 unit invoiced to phase 2 contract . Hence :

So, for :
Jobnumber InvQty
31022 InvQty =10
310221 InvQty =1
310222 InvQty =1

The phase is NOT a seperate field. i.e. Base Contract =31022
Phase 1 Contract = 310221
Phase 2 Contract = 310222
etc......

I hope this makes sense!

 

[KKilfoil]

In a query, include your contract# and amount fields.

Create a new Calculated Field such as:
BaseContract: Left( str([YourContractField] ),5)

This will return a new (text) field which should match the base contract for all records (including those which are base contracts).

Now you should be able to use standard summary techniques to 'add' them up.

HTH

 

[GJT]

KKilfoil

Thanks for the info - yes simple isn't it..!!! Had brain fog looking at the problem for too long from the same perspective.....

Thanks