Q,

[infotech2]

I have a cross-tab query that contains the Fiscal Quarters (1, 2, 3, 4) and the total sales for that quarter based on sales account code. I need a row with codes combined with the name "Sales" and a total for each quarter. Some codes are in order (1,2,3,4) others are not (6,9,10, 15). How can I combine multiple records into one?


AcctCode AcctCodeName Total Date
1 Sales-Parts $4 1/1/2006
2 Sales-Service $5 4/2/2006
3 Sales-Internal $5 7/1/2006
4 Sales-Other $5 12/23/2006

I'm using the following code to mark each detail with a quarter number but I'm lost on combining each record into one.

if Month ({AR_InvoiceHistoryHeader.TransactionDate}) <= 3
then "1Q"
else
if Month ({AR_InvoiceHistoryHeader.TransactionDate}) <= 6
then "2Q"
else
if Month ({AR_InvoiceHistoryHeader.TransactionDate}) <= 9
then "3Q"
else
if Month ({AR_InvoiceHistoryHeader.TransactionDate}) <= 12
then "4Q"

Thanks for any help you can provide.

 

[lbass]

Your post is unclear. Why do you need to combine multiple records into one? What does the irregular sequencing of code have to do with anything. You haven't said the code relates to anything in particular. Please try again.

-LB

 

[infotech2]

Sorry for being unclear. I really didn't mean to have the subject Q, but I couldn't modify it after I hit submit.

We have sales account codes that invoices get coded to so that our finances can be tracked by those codes. For instance, code 1 would be for the sales of airtime hardware. Code 2 would be for sales of airtime. I need to get the total of both of those codes in a summary line called "Airtime Sales".

Thanks for taking the time to reply.

 

[lbass]

You need to create groups. You could use a formula that extracts the beginning of the Account Code Name, e.g., if the Name always has the word you want to group by first, followed by a "-":

left({table.accountcodename},instr({table.accountcodename},"-")-1)

Or you could create groups by clustering by code numbers, e.g.,

if {table.acctcode} in 1 to 4 then "Sales" else
if {table.acctcode} in [6,9,10,15] then "Airtime} else
//etc.

Insert a group on the formula you decide to use, and then you can insert summaries on the fields at the group level.

-LB

 

[Madawc]

Or you could use DatePart("q", {your.date}). The result is 1, 2, 3 or 4.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[infotech2]

lbass,

Thanks for the reply. For some reason when I check the code, I get an error stating "A string is required here" and the account codes get highlighted. Our account codes are in number format like this: 1111-01 or 1111-02.

Any ideas?

Madawc,

That is definatlly less code but I like the ability to have a Q in the number.

Thansk!

 

[infotech2]

The error was popping because quotes are needed around each number.

Thanks for the solution, it works great!