Consolidating records

  • Thread starter Thread starter Amanda
  • Start date Start date
A

Amanda

Hi,

I hope someone can help me achieve a report that i'm
currently struggling with...

I am trying to consolidate data currently in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
123/101/002LT Smith & Co 15/11/03 £1000
123/101/002LC Smith & Co 15/11/03 £3000

to become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
123/101/002 Smith & Co 15/11/03 £4000

Is this possible? If yes, please can you tell me how can
achieve it? I have a number of records (with different
reference numbers) that need to be consolidated in this
way.

I thought about using the 'LEFT' function, but I'm not
sure how to use it in this context.

Thanks in advance,
Amanda
 
Amanda:

In your report's underlying query, assuming that the Reference number is
always in the same format and length, then you would change the query to a
Totals query and using left function create an aliased calculated field for
the Reference number as in:

RefNo: Left([Ref],11)

Group by on the RefNo, Company and Date and Sum on the Amount
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Hi,

I hope someone can help me achieve a report that i'm
currently struggling with...

I am trying to consolidate data currently in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
123/101/002LT Smith & Co 15/11/03 £1000
123/101/002LC Smith & Co 15/11/03 £3000

to become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
123/101/002 Smith & Co 15/11/03 £4000

Is this possible? If yes, please can you tell me how can
achieve it? I have a number of records (with different
reference numbers) that need to be consolidated in this
way.

I thought about using the 'LEFT' function, but I'm not
sure how to use it in this context.

Thanks in advance,
Amanda
 
Steve,

Thank you for your help. The reference numbers are not of
equal length so will I need to pad out the shorter
reference numbers with preceding zeros? If so, please
could you point me in the direction to do this?

Many thanks,
Amanda
 
Amanda:

If they are not of equal length, then you have to use some algorthym to
parse it up, possibly looping through the string to the last slash?

If the significant values to the left e.g. 456/100/001 are always the same
length then you don't have to work about padding.


--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Steve,

Thank you for your help. The reference numbers are not of
equal length so will I need to pad out the shorter
reference numbers with preceding zeros? If so, please
could you point me in the direction to do this?

Many thanks,
Amanda
 
Unfortunately, the significant values of the reference
numbers are not always the same. For example, as the data
currently stands, a reference number can be listed in one
of the following formats:

######??#
##/###/###??
###/###/###??
####/###/###??

where '#' is a number, and '?' is a character.

This is turning out to be more complex than I first
imagined.


Any suggestions? Thank you for your help.
-----Original Message-----
Amanda:

If they are not of equal length, then you have to use some algorthym to
parse it up, possibly looping through the string to the last slash?

If the significant values to the left e.g. 456/100/001 are always the same
length then you don't have to work about padding.


--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Steve,

Thank you for your help. The reference numbers are not of
equal length so will I need to pad out the shorter
reference numbers with preceding zeros? If so, please
could you point me in the direction to do this?

Many thanks,
Amanda
-----Original Message-----
Amanda:

In your report's underlying query, assuming that the Reference number is
always in the same format and length, then you would change the query to a
Totals query and using left function create an aliased calculated field for
the Reference number as in:

RefNo: Left([Ref],11)

Group by on the RefNo, Company and Date and Sum on the Amount
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Hi,

I hope someone can help me achieve a report that i'm
currently struggling with...

I am trying to consolidate data currently in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
123/101/002LT Smith & Co 15/11/03 £1000
123/101/002LC Smith & Co 15/11/03 £3000

to become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
123/101/002 Smith & Co 15/11/03 £4000

Is this possible? If yes, please can you tell me how can
achieve it? I have a number of records (with different
reference numbers) that need to be consolidated in this
way.

I thought about using the 'LEFT' function, but I'm not
sure how to use it in this context.

Thanks in advance,
Amanda


.
Click to expand...


.
Click to expand...
 
Amanda:

Unfortunately no, I have no additional thoughts. The challenge is to get
your organization to move to a standard reference number format if that will
be your key. An alternative in the short term might be to use a Customer
ID and a Date in your group by rather than the reference number since there
is no consistancy to the reference number.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Unfortunately, the significant values of the reference
numbers are not always the same. For example, as the data
currently stands, a reference number can be listed in one
of the following formats:

######??#
##/###/###??
###/###/###??
####/###/###??

where '#' is a number, and '?' is a character.

This is turning out to be more complex than I first
imagined.


Any suggestions? Thank you for your help.
-----Original Message-----
Amanda:

If they are not of equal length, then you have to use some algorthym to
parse it up, possibly looping through the string to the last slash?

If the significant values to the left e.g. 456/100/001 are always the same
length then you don't have to work about padding.


--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Steve,

Thank you for your help. The reference numbers are not of
equal length so will I need to pad out the shorter
reference numbers with preceding zeros? If so, please
could you point me in the direction to do this?

Many thanks,
Amanda
-----Original Message-----
Amanda:

In your report's underlying query, assuming that the Reference number is
always in the same format and length, then you would change the query to a
Totals query and using left function create an aliased calculated field for
the Reference number as in:

RefNo: Left([Ref],11)

Group by on the RefNo, Company and Date and Sum on the Amount
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Hi,

I hope someone can help me achieve a report that i'm
currently struggling with...

I am trying to consolidate data currently in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
123/101/002LT Smith & Co 15/11/03 £1000
123/101/002LC Smith & Co 15/11/03 £3000

to become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
123/101/002 Smith & Co 15/11/03 £4000

Is this possible? If yes, please can you tell me how can
achieve it? I have a number of records (with different
reference numbers) that need to be consolidated in this
way.

I thought about using the 'LEFT' function, but I'm not
sure how to use it in this context.

Thanks in advance,
Amanda


.
Click to expand...


.
Click to expand...
 
Back
Top