Help needed desperately with converting a list of credit notes to columns

[BoyDay]

I have some data in the same format as the Raw Data table in the attached file, and I need to change it into the same format as the Desired Format table.

So it's a list of account numbers and credit note details; and I need to have the credit notes for each customer on one line per account number with six sets of columns for credit notes per quarter. There are instances where there are more than six credit notes per customer in a quarter, but in these cases I just want to see the first six (by date credited).

It's really frustrating as it feels like it should be fairly straightforward but I can't do it and I have to have the data ready for Friday

 

[plog]

First and foremost, why? You have your data properly normalized and you want to unnormalize it. Are you technologically regressing back to an Excel based system? Whats the ultimate purpose of this?

Second, it is straightforward, tedious, but straightforward. Below is the start of the SQL that will produce what you want:

SELECT [Raw Data].[Account Number], Max(IIf([Credit Note No]=1,[Credit Note No])) AS CreditNoteNo1, Max(IIf([Credit Note No]=1,[Credited Date])) AS CreditNoteDate1, Max(IIf([Credit Note No]=1,[Credit Amount])) AS CreditAmount1, Max(IIf([Credit Note No]=1,[Quarter Credited])) AS CreditQuarter1
FROM [Raw Data]
GROUP BY [Raw Data].[Account Number];

Paste that into a query and then run it to see what you get. It will show the account number then the 4 fields for Credit Note 1 (Credit Note, Credit Date, Credit Amount, Credit Quarter). Open the query to design view and then copy those 4 fields for however many Credit notes you will need. Then for each new set of 4 fields you will need to change the below portion of each to the current number you are on:

IIf([Credit Note No]=X

Change X above to the number of credit note.

 

[BoyDay]

Thanks for that, it helped a lot, although my credit notes aren't all numbered 1-6; they're all unique and random numbers which this then didn't work with. I can put the raw data into Excel first to put in a 1-6 column to allow me to do this, but is there a way to do it in Access with unique credit note numbers? I'm setting this up as part of a larger file which takes a few different spreadsheets to create the export format I need for an import template.

It's to take data from our database and provide it in an import template we've been given by our agency, so unfortunately it is a bit of a weird thing.

 

[plog]

You might be able to do something similar using a "cross tab query" (google that), although I'm not sure if it will work since you need 4 fields per Credit Note Number.

 

[BoyDay]

Yeah I tried a crosstab query but don't think it'll work. Your suggestion will work if I insert another field where I count the credit notes per account per quarter. If I was going to do the count in Excel I would sort by account number and credit note date, and then write an IF statement along the lines of IF (acct no)&(quarter) are not equal to the row above then 1, else IF = only the row above then 2, =2 rows above then 3, etc until I reached 6 and then have null or something outside of 1-6 display for the 7th record.

Any idea how I would do that in Access?

 

[plog]

When you talk about manipulating data based on rows' relationships to other rows your going to have to use VBA.

Maybe the best way to do this is to append all your unique Account Numbers to your Desired Format Table.

INSERT INTO [Desired Format] ( [Account Number] )
SELECT [Raw Data].[Account Number]
FROM [Raw Data]
GROUP BY [Raw Data].[Account Number];

Then link the Desired Format table back to Raw Data table and populate it 4 fields at a time:

UPDATE [Raw Data] INNER JOIN [Desired Format] ON [Raw Data].[Account Number] = [Desired Format].[Account Number] SET [Desired Format].[Credit Note No] = [Raw Data].[Credit Note No], [Desired Format].[Credited Date] = [Raw Data].[Credited Date], [Desired Format].[Credit Amount] = [Raw Data].[Credit Amount], [Desired Format].[Quarter Credited] = [Raw Data].[Quarter Credited]
WHERE ((([Desired Format].[Credit Note No])=1));

Working your way through all the Credit Note Numbers and appropriate fields in Desired Format.