access 2003 summary report problem

[kamalis14]

I am trying to create a summary report that will be run monthly for Clients. I just cant seem to work it out. Each client can order 3 types of products. Each type consists of product codes describing the product.

So, ClientX can order a 1,2 and/or 3 product that belong to TypeA and product 4,5 which belong to TypeB. Each product order also has an ascending status code ascociated with how far along the product is to being complete/cancelled.

I need the report to have a summary of total products ordered for each type, total for product completed including average of the time it takes, and total cancelled. These are all within the previous month but I also need the report to have a total of outstanding items within the last 120 days.

I am able to query a list of all the product ordered by all the clients and i am able to display the total products ordered for each Type by client, but I am having trouble also displaying the total completed, the TAT, outstanding and cancelled products in the report mainly because I cant seem to get the join on the table with the product status code to display without chopping data.

Im thinking it should be a right outer join for the table but any advice would be appreciated. I am also wondering if handling this with VBA code and some temp tables might be suitable for this vs a complex query with a lot of calculated fields in the report. Any opinions? Thanks for any help.


similar key/fk structure on the three tables

table main table prd table status

ID ID ID
f1 SubID SubID
f2 f3 StatusCode

[MMcCarthy]

I am trying to create a summary report that will be run monthly for Clients. I just cant seem to work it out. Each client can order 3 types of products. Each type consists of product codes describing the product.

So, ClientX can order a 1,2 and/or 3 product that belong to TypeA and product 4,5 which belong to TypeB. Each product order also has an ascending status code ascociated with how far along the product is to being complete/cancelled.

I need the report to have a summary of total products ordered for each type, total for product completed including average of the time it takes, and total cancelled. These are all within the previous month but I also need the report to have a total of outstanding items within the last 120 days.

I am able to query a list of all the product ordered by all the clients and i am able to display the total products ordered for each Type by client, but I am having trouble also displaying the total completed, the TAT, outstanding and cancelled products in the report mainly because I cant seem to get the join on the table with the product status code to display without chopping data.

Im thinking it should be a right outer join for the table but any advice would be appreciated. I am also wondering if handling this with VBA code and some temp tables might be suitable for this vs a complex query with a lot of calculated fields in the report. Any opinions? Thanks for any help.


similar key/fk structure on the three tables

table main table prd table status

ID ID ID
f1 SubID SubID
f2 f3 StatusCode

Post the SQL you are currently using. Tell us what results you are getting and what results you need and we'll see if we can help