spliting 1 long table into many tables based on Months.

[PaulJL]

I have a table with two columns, Customer code and Date of invoice sent. (approximately 3/4 million Rows), I need to be able to see the pattern of when the customer code was invoiced. ie every month, every quarter, every half year and so on.

I have used the distinct command to create a unique list of customer codes, now i need to create a table which shows the customer code along with a True/false entry for each month (with each month in a different column). (approximately 2 and a half years). I have done something similar in excel by downloading seperate months and storing them as worksheets and then using a vlookup to reference these worksheets but only for small sections of the file. Is what i am asking possible in Access without too much difficulty.

 

[Russell Hauf]

Is what i am asking possible in Access without too much difficulty.

I think so. You'll have to create 2 queries, but it won't be that difficult.

Ok, first query:

Select your CustomerCode and your Date field, then add a field like this (just type it in the top line):

YearMon: Year([fldDate]) & Format(Month([fldDate]),"00"

Then click on the "Totals" button on the toolbar (the Sigma) -- or go to View-Totals.

Group by the CustomerCode and the "YearMon" fields, and COUNT the Date field.

Run the query to see if it works and then save it (I called mine qry020403a - I'm telling you this b/c I'm going to paste my SQL below).

Now, go to the query tab and create a new query using the Crosstab wizard. For the row headings, select the CustomerCode. For the column headings, select YearMon, and for the calculated column, select the Count field (using Acc2000, I unchecked the "Yes, Include Sums" checkbox).

That should do it. You'll get blanks for any month that does not have a date for that particular customer. Here's my 2 queries - my fields were named fldCustCode and fldDate, and I didn't change any of the field names that Access automatically assigns (such as "CountOffldDate"). My table name was tbl020403.

Query1 (again, I saved this query as qry020403a):

SELECT tbl020403.fldCustCode, Year([fldDate]) & Format(Month([fldDate]),"00") AS YearMon, Count(tbl020403.fldDate) AS CountOffldDate
FROM tbl020403
GROUP BY tbl020403.fldCustCode, Year([fldDate]) & Format(Month([fldDate]),"00");

Query2 (crosstab):

TRANSFORM Sum(qry020403a.CountOffldDate) AS SumOfCountOffldDate
SELECT qry020403a.fldCustCode
FROM qry020403a
GROUP BY qry020403a.fldCustCode
PIVOT qry020403a.YearMon;

HTH,

Russell

 

[Bariloche]

Paul, Russell,

Unless I'm missing something, I think you can go right to the crosstab query (leastwise it worked for me ). Here's the SQL for it:

TRANSFORM Count(tblCust.InvDate) AS CountOfInvDate
SELECT tblCust.CustID
FROM tblCust
GROUP BY tblCust.CustID
PIVOT DateValue(Month([InvDate]) & "/1/" & Year([InvDate]));

The "datevalue" bit just changes the "month," represented by the first of the month, back into a format that is recognized as a date.


hth

 

[PaulJL]

Both solutions worked!! thanks very much for the input.