Hi Reji,
The design of Table 'B' is not correct for a relational database. You do not want separate
fields for storing similar data. You should be able to achieve the desired result using the data
stored in Table 'A', without the need to rewrite the data to another table.
Try the following SQL statement, which produces a crosstab query. To use this statement, create
a new query in design view. Dismiss the tables dialog without selecting any tables. Then click
on View > SQL View in query design. Paste the following SQL statement into the SQL window,
replacing the default "SELECT;":
TRANSFORM Count([Status]) AS CountOfStatus
SELECT A.CustomerCode
FROM A
GROUP BY A.CustomerCode
PIVOT
IIf(Now()-[DocDate]<31,"0-31 Days",
IIf(Now()-[DocDate]<61,"31-60 Days",
IIf(Now()-[DocDate]<90,"61-90 Days",
"Above 90 Days")));
If you try running the query right now, you will likely get an error message that reads:
"The Microsoft Jet database engine does not recognize '[Status]' as a valid field name or
expression"
Click on View > Design View to switch back to the more familiar QBE grid. Replace "Expr1:" with
"Status:" in the second column (without including the double quotes). In other words, make the
following change:
From
Expr1: IIf(Now()-[DocDate]<31,........
To
Status: IIf(Now()-[DocDate]<31,........
Note: This solution uses a nested IIF function. My preference is to call a custom function
instead of using nested IIF statements, because a custom function is usually much easier to
debug. The solution also assumes that CustomerCode is a foreign key and can include several
records in Table 'A' that have the same CustomerCode (ie. not a primary key).
If you would like to include a row total for each customer, then change the second line above as
follows:
SELECT A.CustomerCode, Sum(([CountOfStatus])) AS [Customer Total]
Post back if you'd like an example of using a custom function instead of the nested IIF's.
To learn more about crosstab queries, check out this link:
http://www.access.qbuilt.com/html/crosstab_queries.html
Tom
___________________________________________
Sir,
I have two tables in my db. Table 'A' contains the fields
CustomerCode,Docdate , Trantype & Docamount. Table 'B'
contains CustomerCode,0_30,31_60,61_90 & Above_90 like
that. If diffrence between currentdate and docdate
of 'A'is below 31, that should be update in B's 0_30, if
diffrence is between 31 to 60 sholud update 31_60 and so
on. How can I ? With query or code builder ? Any one knows
pls give me answer. Because I wish to prepare aging
analysis repot.
Thank you
Reji
